Creating a chart in Excel is a simple process once you know how. With this post learn the steps you need to take in order to create a chart and make some custom formats to the Excel chart which will ensure you are presenting an effective chart every time…
In the first post of this charting series we looked at the various chart types on offer in Excel 2010 and some of their main reasons to use them. This post leads on from that and now looks at the steps involved in creating a chart in Excel 2010 along with the some of the initial formatting options that will help you produce an effective looking chart.
This post will cover:
- Inserting a chart
- Components of a chart
- Positioning and sizing a chart
- Adding a title to a chart
- Formatting the legend in a chart
To begin with we need some data to chart. If you have your own data to hand then feel free to use that or you can download the following Excel file which will help you work through examples.
To create a chart you first need to highlight your data and then insert a chart. Excel will build the chart type of your choice using the data that you have highlighted.
This method is generally the quickest way to produce a chart, as with many features in Excel there are other methods. To create the chart with this method the steps are:
1) Select your data. To do this select the upper left cell of your data, for this example cell A5. Then holding down the left mouse button drag the cursor down and across to the last cell of data cell C16, you can then release the left mouse button. Your data should take on a highlighted appearance like below:
2) With the data highlighted select the “Insert” tab (1) across the Excel ribbon which is found at the top the worksheet. For this example we will create a standard column chart by selecting the “Column” icon (2) and the standard column chart (3):
3) Now you have created a basic chart which will show up on the worksheet and should look very similar to this:
When your chart is created it is created as an object within Excel.
A chart object has a variety of components that go into its creation and it is worth being aware of each one as they can all be formatted individually. This means for the user is that you can select any of the components on their own, by left-clicking the mouse button while the cursor is over that component, then format it individually.
By formatting that means you can move components around the chart area or change text styles and many more different formatting options you would associate with Excel.
When you have generated a chart you will often need to reposition it on your worksheet and perhaps resize the chart so it fits in better with your report. A chart is treated as an object within Excel and we can move objects easily using the mouse.
To reposition a chart:
1) Select the chart – left click the mouse with the cursor anywhere on the chart, you will notice that the border thickness of the chart increases when it is selected, this is just Excels way of showing you that the object has been selected.
2) Making sure the cursor is in the chart area (see above for where that is on a chart) hold down the left mouse button. While the mouse button is held down you control the chart position so can move it around the worksheet wherever you need to, as soon as you release the mouse button the chart will take the new position.
To resize the chart:
1) Position the mouse cursor on any corner of the chart, the cursor will change to a diagonal style arrow which represents it is possible to resize the chart object.
2) Hold the left mouse button down and while you have the mouse button held down you can drag the curser in any direction to expand or shrink the chart. As with repositioning of the chart as soon as you release the left mouse button your chart will take its new size.
Now you can move and resize the chart so it can be anywhere, or any size, on the worksheet. For this example I have moved and resized the chart so it fits neatly alongside the data:
Adding a title to a chart should always be considered. If it is totally obvious what the chart shows then you might consider omitting one as they do take up space but if in doubt always include one.
To add a chart title:
1) Select the chart by left clicking the mouse anywhere on the chart.
2) On the Excel ribbon (at the top of the screen) you will now see additional options called “Chart Tools” (1). The “Chart Tools” option will only appear when you have a chart object selected, it remains hidden at all other times. Within the “Chart Tools” option select the tab called “Layout” (2) then select the icon called “Chart Title”(3) and for this example we will add in a title “Above Chart”(4):
3) Your chart should now look like the below image with the words “Chart Title” added as a title:
The chart title becomes an additional component on the Chart and as a result it can be selected and formatted on its own.
To amend the chart title left click the mouse button within the chart title component (which will highlight it by placing a box around it), we can then simply highlight the text or left click the mouse button for a second time within the box and delete the standard “Chart Title” text and replace it with our own:
The legend can be a very important component on a chart as it acts as a key to what the data values represent. In our example we might know that the blue column is 2012 data and the red column is 2013 data but remember that your end-user might not.
When you have initially created a chart in this example Excel has no titles for the data values, it has just referred to them as “Series 1” and “Series 2”. A data series is a range of values that are plotted on the chart and in our example we have two different data series as we have data values for 2012 and 2013.
To change the legend names we need to drill into the data that feeds the chart, this is done by right-clicking on the chart and choosing the “Select Data” option from the menu:
When you have clicked on the “Select Data” option a new menu box will open up where we can edit the data that is feeding the chart:
In the far left box you will see the current labels, “Series 1” and “Series 2”. Just above them is the Edit button which we can click on to edit that data series.
To edit Series 1 click once on the name which highlights it then left click on the Edit Button:
Another menu box will now open that shows you the following:
This menu box shows us the Series Name, which you can see is currently empty, along with the range of data that is currently associated with that series, in this example cells B5 to B16.
There are two options available to you to rename the data series, the first is we can simply type in a name in the empty “Series name” box, so in this case we know that the range B5 to B16 refers to 2012 sales so we can type in 2012.
Alternatively the preferred method is to link the name to a cell on the worksheet, which means if the name ever changes on the worksheet the legend will update automatically.
To do this click on the icon next to the Series name box:
Then click on the cell you want to link, in this case it is B4 as that contains the title:
Click on the icon to the right hand side:
And then click OK:
Now instead of “Series 1” the label has updated to “2012”. Repeat the process for Series 2 using cell C5 as the title so you end up with the following view:
The last step is to click on “OK” and the legend will update with the new labels and the chart should look like so:
As you can see from the updated chart the legend clearly shows that the blue columns in the chart refer to 2012 and the red ones refer to 2013, which is much clearer for the end user.
- In this post we learned how to insert a chart by highlighting our data and then selecting the insert chart option from the Excel 2010 ribbon.
- An Excel chart contains multiple components, such as a legend or a plot area; each of those components can be selected and formatted individually.
- Repositioning the chart can be done by selecting the chart area and holding down the left mouse button while you move the cursor around the worksheet. You can also resize the chart by positioning the mouse cursor over a corner of the chart and holding down the left mouse button to resize.
- Adding a title to the chart, while not always necessary, can be done using the layout tab found within the chart options menu on the Excel ribbon. You must have the chart selected to be able to see the chart options menu on the Excel ribbon.
- Formatting the legend can be done within the “Select Data” option found by right-clicking on the chart. A legend will help end-users clearly understand what the values in a chart refer to.
There are many more options and features in charts to learn but with the ones explained in this post you have everything you need to get started on creating an effective looking chart within Excel 2010.
The next post in the series will look at formatting some of the other components in a chart such as the horizontal and vertical axis, along with changing the look or type of chart presented so make sure you keep an eye out for that one. Remember you can follow to site on Twitter or via an RSS feed so you always become aware of when a new guide has been added.