 |
|
|
Charts in Excel can range from the very simple to the very complex and can be used for a multitude of reasons. They can be used to keep track of spending, stock performance, statistics, employee details and much more. Most charts are used to show a comparison of past data in a highly visual style. A well set up chart should be able to inform the user at a glance exactly what the picture is that it is painting. We have all heard the saying “A picture paints a thousand words” and this should always hold true with charts in Excel. If an informed user looks at a chart and cannot tell what it is that the chart is representing then it would be fair to say that the chart has not be set up very well.
Over the years we have seen many charts of all different shapes, sizes and colours. Some look fantastic, yet give virtually no information. Others look boring and yet inform the user almost immediately exactly what it is they are representing. In fact we could even go so far as to say that the bland boring looking charts are often the most informative. This is usually because the person who has set up the chart has focused on the content and purpose of the chart as opposed to whether it looks good or not.
Excel is arguably the best of the Office Applications when it comes to creating and/or using charts. It allows us to create almost any kind of chart needed with minimal effort. As can be said with a lot of Excels features and capabilities, we often find ourselves using only a very small aspect of them, charts are no different in this respect. There are 14 different types of charts each with between 2 and 7 sub-types. On top of these there are also many “Custom Types”, although the custom types are mainly just templates of the other chart types.
When you start to work with charts you may hear or read a lot of references to words and terms that you have no idea of. Below we have listed the common terms and words often used by Excel in relation to Charts. We have used the text from the Excel help to explain the lingo used in reference to charts.
The areas surrounding many 3-D chart types that give dimension and boundaries to the chart. Two walls and one floor are displayed within the plot area.
A line that borders one side of the plot area, providing a frame of reference for measurement or comparison in a chart. For most charts, data values are plotted along the value (y) axis, which is usually vertical, and categories are plotted along the category (x) axis, which is usually horizontal.
The entire chart and all its elements.
A sheet in a workbook that contains only a chart. Chart sheets are linked to worksheet data and are updated when the worksheet data changes.
The Chart Wizard will guide you through the steps for creating an embedded chart on a worksheet or modifying an existing chart.
A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell. Data labels can be applied to a single data marker, an entire data series, or all data markers in a chart. Depending on the chart type, data labels can show values, names of data series or categories, percentages, or a combination of these.
A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.
A group of related data points plotted in a chart that originate from rows or columns on a single worksheet. Each data series in a chart has a unique color or pattern. You can plot one or more data series in a chart. Pie charts have only one data series.
In line and area charts, lines that extend from a data point to the category (x) axis. Most useful in area charts to clarify where one data marker ends and the next begins.
A chart object placed on a worksheet and saved with that worksheet when the workbook is saved. Embedded charts are linked to worksheet data and are updated when the worksheet data changes.
Graphic bars that express potential error (or degree of uncertainty) relative to each data marker in a series. You can add y error bars to data series in 2-D area, bar, column, line, xy (scatter), and bubble charts. XY and bubble charts can also display x error bars. Error bars can be selected and formatted as a group.
Lines you can add to a chart that make it easier to view and evaluate data. Gridlines extend from the tick marks on an axis across the plot area.
In 2-D line charts, lines that extend from the highest to the lowest value in each category.
A box that identifies the patterns or colors assigned to the data series or categories in a chart.
Boxes in legends that show the patterns and colors assigned to the data series (or categories) in a chart. Legend keys appear to the left of legend entries. Formatting a legend key also formats the data marker associated with it.
A sequence of averages computed from the parts of a data series. In a chart, a moving average smoothes the fluctuations in data, thus showing the pattern or trend more clearly.
In a 2-D chart, the area bounded by the axes and including all data series. In a 3-D chart, the area bounded by the axes and including data series, category names, tick-mark labels, and axis titles.
Tick marks are small lines of measurement that intersect an axis, similar to divisions on a ruler. Tick-mark labels identify the categories, values, or series in the chart. Tick-mark labels come from the cells on the worksheet used to create the chart.
Descriptive text that is automatically aligned to an axis or centred at the top of a chart.
A graphical representation of the trend, or direction, of data in a series. Trendlines are used for the study of problems of prediction, also called regression analysis. You can add trendlines to data series in unstacked 2-D area, bar, column, line, stock, xy (scatter), and bubble charts.
Optional text for a trendline, including either the regression equation or the R-squared value, or both. A trendline label can be formatted and moved; it cannot be sized.
In line charts with two or more data series, bars that indicate at a glance the difference between the data points in the first and last series. Microsoft Excel automatically uses white bars to show that the point in the last series is greater than the point in the first series, and black bars to show that the point in the last series is smaller than the point in the first series.
The basic chart in Excel is made up of many different elements, all of which can be changed at any time. While it is handy to know all the names and terms used for an Excel chart this is not usually the case. If you have a chart in a Workbook and you are unsure of the name of a particular element simply wave the mouse pointer over it and Excel will display the name of the element in tip text.
You can also use the mouse pointer to make any changes you want to a particular element. To do this simply double click the element you wish to modify or change. If the element you wish to modify or change is a single data point (eg one column of many) then you can first left click on any data point (this will select all data series eg; all columns), wait a second or two then left click again (this will select the single data point eg; one column) and then double click. You can then make changes or modifications to a single data point without affecting the remaining data points.
If you prefer to use the Chart toolbar to modify or change an Embedded chart you can do so by simply selecting the chart. The normal Worksheet Menu Bar will disappear and be replaced with the Chart Toolbar. The Chart Toolbar will always be visible if your chart is on a Chart sheet and not Embedded on a Worksheet
Before we even consider creating a chart we need to give thought to what it is we are trying to show. Put another way we need to decide on the picture we are going to paint. Let’s go through the different types of charts and see a brief description of when you would use them. The descriptions for each chart type are certainly not cast in stone and are far from any hard and fast rules.
A column chart might be used to show changing data values over time or by category eg; departments, regions etc. Categories should be shown horizontally with values shown vertically.
A bar chart could be used to illustrate a comparison of different categories or items by their associated values. Categories should be shown vertically and values shown horizontally. A bar chart is not usually used to place an emphasis on time.
A line chart is best suited to demonstrate the trend of a set (or sets) that have related data.
Pie charts are used to show the proportion of particular items in comparison to the total sum of all items. Each slice of a pie chart is often shown as a percentage of the total. It can only show one data series.
An XY (scatter) chart can show relationships between data that is set up in more than one row or column. In other words it can be used to show relationships among two or more sets of values.
The data for an XY (scatter) should have it’s X values set up in the top row or left column and it’s Y values set up in corresponding rows or columns.
An area chart can highlight the extent of a change over a set time period of one or more set of values. It can also show its values as a proportion of the total in a similar way as a pie chart.
A doughnut is very similar to a pie chart. The difference is it can contain more than one data series. A pie chart can only contain one.
A radar chart can compare the total of values from a multitude of related data series. Each category has its own value axis stemming from a centre point. Lines are then used as connectors for all values in the same series.
A surface chart can be used to find the best combination of two sets of data. Colours and/or patterns are often used to show areas that are from the same range of values.
A bubble chart is a type of XY (scatter) chart and pie chart. The size of the data marker (bubble) is used to show a proportion, while its position can show another factor. As with an XY (scatter) chart the data for a bubble chart should have it’s X values set up in the top row or left column and it’s Y values set up in corresponding rows or columns.
Stock charts are usually used to show stock prices, although they can be used for scientific data as well. Stock charts can show high-low closing and high-low closing and volume. A stock chart is highly dependent on the layout of it’s data. It is best to consult Excels help file before setting up stock data you wish to show in a stock chart.
These types of charts are exactly the same as bar and column charts in their content. They can add a dramatic effect to bar and column charts.
We would like to state again that the above is only a guide and not a set of hard and fast rules. The most important thing to keep in mind is the best chart for the job is the one that best shows what you want to say. It can be very easy to fall into the trap of basing your ‘Chart type’ decision on the cosmetics of various chart types. Try not to let this influence your decision or at least make it a low priority in the decision process. You can apply an endless amount of formatting to your chart at any time. You can also change the chart type at any time by right clicking on the outer chart area and selecting Chart Type. In short what we are saying is never lose sight of the reason for creating a chart - they are supposed to supply us with visual information not visual pleasure!
One way to create a chart is through what is known as the Chart Wizard. This Wizard will actually step you through the process of creating a chart, requesting prompts for the information you need to create your chart as you go.
The Chart Wizard can be accessed through its icon on the Formatting toolbar, or by going through the Insert menu and selecting Chart.
Although not necessary, the creation of the chart is easier for the Wizard if you FIRST select the range of cells in the worksheet that contains the data you wish to chart. This also allows you to preview the various chart types available to you from within the Wizard using the actual data that will be plotted.
We will create a basic chart for a pet shop showing their sales for the first four months of the year. The data will be as follows:
In cell A1 type the heading Animals and on rows 2 – 5 you have the animals, dogs, cats, rabbits, birds.
In cell B1 you have the heading January, in B2 type 20, B3 type 15, B4 type 17, B5 type 21.
In cell C1 type the heading February. In C2 type 25, C3 type 12, C4 type 22, C5 type 19.
In cell D1 type the heading March. In D2 type 9, D3 type 8, D4 type 10, D5 type 8.
Lastly, in cell E1 type the heading April. In E2 type 12, E3 type 10, E4 type 13, E5 type 14.
The first thing we will do is highlight the range that we wish to chart. To do this, select the range A1:E5 with your mouse.
Now we need to activate the Chart Wizard. We can do this by either clicking on the Chart Wizard icon on the Standard Toolbar, or by going to Insert>Chart.
Step 1 of 4 of the Chart Wizard – Chart Type should now be in front of you. This screen requires you to nominate the type of chart that you wish to use. You will notice on the left under Chart Type the various types of standard charts available to you. On the right under Chart Sub-type are various sub-types or variations of the type of chart highlighted on the left that are available to you.
Click on a few chart types and have a look at them and their sub-types. If you click on the button Press and Hold to View Sample a sample of your selected chart with your selected data in it will be previewed to you. For the purpose of this exercise we will click on Column in Chart Type. Now click the Next button to move on through the Wizard.
Step 2 of 4 of the Chart Wizard – Chart Source Data should now be in front of you. This is where you can manipulate the data settings. The data range is already selected and therefore appears in the Data range box, but we wish to adjust the orientation so that the information is charted by rows. Click on Rows in Series In. If you had not already selected your chart range, you could do this now by clicking on the Collapse Dialog button at the right end of the Data range box. This will temporarily collapse the dialog box so that you can enter the range by selecting cells in the Worksheet. When you finish, you can click the button again to display the entire dialog box.
We don’t need to make any more changes to our chart in this step so we will select Next to continue to move through the Wizard.
Step 3 of 4 of the Chart Wizard – Chart Options should now be in front of you. This screen will allow you to add titles and manipulate the general look of the chart. Click in Chart Title and type Pet Shop Sales, then click in Value (Y) Axis and type Dollars. Note that if you wish to have a line break, once your chart is finished, click on the text within the chart and push Enter. Have a look at the various tabs while you are in this screen to see what else is available. Now click on Next to move to the fourth and final screen of the Chart Wizard.
Step 4 of 4 of the Chart Wizard – Chart Location should now be in front of you. This box allows you to nominate the location of your chart. You can place your chart into a worksheet (embedded) or you can place it in a separate sheet of its own (chart sheet). We are going to click on As new sheet and type in Pet Shop Sales. There is nothing else to do in this screen so we will now select Finish to complete the chart.
You will notice that your chart has now been produced on its own chart sheet with a chart sheet tab titled Pet Shop Sales.
Charts work in the same way that formulas do. You will notice that if you make any changes to your source data, the chart will immediately update to reflect this, just as formulas in a worksheet do.
Make a mental note of your chart. Now go back to your source data and make the following change. In column B, change the values for January (rows 2 – 5) to 4, 5, 6, 7. Now click back on the Pet Shop Sales worksheet tab and notice how your chart has changed to reflect the changes in your data.
Whether you place your chart on a standard worksheet (embedded) or on a chart sheet is purely optional.
If you will be doing frequent printing of your chart(s) then you may want to lean towards a chart sheet. This way Excel will default to the most likely settings for a chart whenever you select Print. However having said this you can get the same effect by simply selecting your chart before selecting Print, this way Excel will know that it is a chart you want to print and default to the appropriate settings.
At the end of the day though it really makes no dramatic difference so use the option that you are most comfortable with.
If you have spent a lot of time setting up a chart and getting it to look exactly as you want, you may want to be able to use the chart again elsewhere. If you have the need to frequently use the same type of chart you may want to consider creating a template of the chart.
Excel makes this very easy for us to do. Just imagine you have spent many hours getting your chart to the point where it demonstrates very well what you want it to. The last thing you want is to have to do the same all over again for a similar chart. With Excel, we don’t need to as we can make this chart a User-defined custom chart type.
Right click on your chart area and select Chart Type.
Click the Custom Types page tab.
Ensure the User-defined option button is checked.
Click Add.
Type a Name for your chart and a Description then click OK.
Next time you create a chart and you wish to use your template chart, simply select it from the Chart Type page of the Chart Type dialog box in the Select From frame.
Another method that is often over-looked is simply copying the chart that we have created and changing it’s data range. This can be done very easily! If your chart is on a chart sheet select the name tab, then holding down the Ctrl key drag a copy of the chart sheet to the left or right. If the chart is an embedded chart then select the chart with your mouse, then right click and select Copy, now paste to another location
Excel will also allow us to set the type of chart that Excel will default to each and every time we create one. This is done as follows.
Right click on a chart area of any chart and select Chart Type.
Select the chart you want to set as the default chart.
Click the Set as default chart type.
From now on every time you create a chart, Excel will default to the chart type you have chosen. Note this will affect ALL workbooks not only the active workbook at the time. The default chart type for Excel, unless changed, is a column chart.
Just because we have gone through the Chart Wizard and created our chart does not mean we cannot make any changes. In fact we find it far easier to create what is known as a quick chart and make all our changes from there. Creating a quick chart is very simple, just select anywhere within your data range and push F11. Doing this will force Excel to create a chart sheet with the default chart type on it. If you would rather an embedded chart simply right click on the outer chart area and select Location. This will display the Chart Location dialog box.
Once we have our default chart we can make any and all changes we need with relative ease by right clicking on the chart and selecting the appropriate menu item or double clicking on the chart element we want to change. In my opinion this method is the best way as you can view any changes you make as you make them. When you use the Chart Wizard you cannot really tell what the end chart looks like until you have set all the many aspects and characteristics. You can see them in the Preview Window of the Chart Wizard, but it is often not a true representation of your finished chart.
After you have created a chart you may need to add a new series to your chart. This can be done in many different ways. I will show you the long way (most common) first then some easy shortcuts.
Right click on your outer chart area and select Source Data
Click the Series tab on the Source Data dialog box.
Click Add. Type a name in the Name: box or select in the Name: box and then select the cell that contains the name you want to use.
Click in the Values: box then select the range of cells that contain your data.
Click OK
Here are three much easier ways to add a new series to an existing chart.
Select and copy the range of cells you wish to add including the heading, then right click on the outer Chart area and select Paste.
Select the range of cells you wish to add including the heading, then Left click on the selection border and drag it to your chart and release.
Select the range of cells you wish to add including its heading then left Right click on the selection border and drag it to your chart and release. *
* The last method is particularly useful, as once we release the mouse button Excel will display the Paste Special dialog box for charts.
As you are no doubt aware, when you make an alteration to any cell(s) in a charts' source data it is automatically reflected in the chart that is using the range as its reference. It is also possible to do the reverse, that is make the change on the chart and have it reflected in the appropriate cell(s) in the data range.
Let’s say we have more than one chart set up in a workbook and they are all reading their data from many different areas and worksheets. Rather than try and locate the cell(s) that you want to update, try this. We will assume that the chart is a Column chart.
Single left click on the column that needs changing.
After a short pause (1-2 seconds) left click again.
Move your mouse pointer up to the top of the Column until it changes to an up-down arrow.
Left click and simply drag the Column up or down. Excel will display the values as tip text as you are dragging.
You will find that the cell or cells that the column represents will change along with the column on the chart. We should note here that this method will not work for a chart that is receiving its data from another workbook and that workbook is closed.
While nearly all aspects of charts can be found either directly on the Chart Menu Bar, right clicking on the charts outer area or double clicking, there are some further options for charts. To see these you must go to Tools>Options-Charts. It is best to either do this on the Chart Menu Bar or select a chart then go there via the Worksheet Menu Bar.
These options controls how empty cells are plotted on charts. There are three options.
Not plotted (leave gaps): Leaves gaps in the line for empty worksheet cells in a data series, making the line segmented.
Zero: Treats blank cells as zeros, so that the line drops to zero for zero-value data points.
Interpolated: Interpolates data points that represent blank cells, and fills in the gaps with connecting lines.
This option, if checked will mean that any hidden rows or columns in a data range will not be plotted. This can be a handy feature when you are working with filtered data. You can also use this so that you pre-set the data series to include many blank cells and then simply unhide the rows or columns as you need to add new data.
This option only applies to charts that are on chart sheets. When this box is checked (default) a chart on a Chart sheet will automatically size the chart to fit the widow. If you uncheck this box you can drag the chart to size as you would an embedded chart.
This option when checked means Excel will display the name of a particular chart element each time you hover your mouse pointer over it.
This option when checked means Excel will display the value of a particular data marker each time you hover your mouse pointer over it.
So in summary, charts can be a very effective way to demonstrate and/or highlight a particular point. The purpose of a chart is to stress a point and not have attention drawn away from the point they should show by the over-use of formatting. In short, decide on the chart type that would best demonstrate your point, create it, then apply a sensible amount of formatting. Remember, the trick is being able to paint a true informative picture.