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.
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 click on the element you wish to modify or change and you will see the Chart Tools tab appear at the right hand side of the ribbon. There are three additional tabs under Chart Tools; Design, Layout and Format.
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 to see the options on your ribbon. You can then make changes or modifications to a single data point without affecting the remaining data points.
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 there 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!
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 go to the Insert tab and under Charts options you will see the six most common types of charts available and an Other Charts icon. Click on any chart type and you will see the sub-types available. We will select the dialog box launcher to display the Insert Chart dialog box so we can see all the charts at once.
You will notice on the left of the dialog the various types of standard charts available to you. On the right hand side 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. For our purpose, we will select Column on the left of the dialog box and then click on the first chart sub-type and click the OK button.
A chart should appear as an object on your screen adjacent or over your data. If you wish to move the chart, just hold your left mouse button down on it and drag in the desired direction. If you have a look at the ribbon, you will also notice a new heading Chart Tools on the right of the ribbon and three new tabs, Design, Layout and Format. These tabs allow us to manipulate the settings of our created chart.
The data range is already selected but if we wish to change the range that our chart feeds from, we would select the Select Data option under Data options on the Design tab. To the right of the Chart Data Range is a collapse tool. 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, the click OK to accept the new range.
If we wanted to adjust the orientation so that the information is charted by rows (the default being columns), we would select Switch Row/Column under Data options on the Design tab.
If you wanted to view your chart on a separate worksheet of it's own, select the Move Chart icon under Location options. The Move Chart dialog box gives you the option to name your worksheet tab if you like. Lets select New Sheet then type in Pet Shop Sales. Click on OK and you will notice that your chart has now been produced on its own chart sheet with a chart sheet tab titled Pet Shop Sales.
The design tab is also where the various Chart Styles are housed. Click on the various designs and you will see you chart change immediately. To the right of the chart styles is a scroll bar, select the downward pointing arrow to see the entire range.
Lets now click on the Layout tab. The Layout tab is where you can add titles and manipulate the general look of the chart. Click on Chart Title>Above Chart under Labels options and type Pet Shop Sales. Now click on Axis Titles>Primary Vertical Axis Title>Vertical Title and type in 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 other options while you are viewing this ribbon to see what else is available.
The format tab contains further formatting options, such as Shape Styles, Word Art Styles, Plot area options, border colours and styles etc.
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.
Select the chart.
Go to the Design tab
Ensure the User-defined option button is checked.
Select Save As Template
Type a Name for your chart then click Save.
Next time you create a chart and you wish to use your template chart, simply select it from the Templates folder of the Insert Chart dialog.
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 Change Chart Type.
Select the chart you want to set as the default chart.
Click the Set as Default Chart
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 created our chart does not mean we cannot make any changes to it. 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 clicking on the chart element we want to change and selecting from the ribbon.
After you have created a chart you may need to add a new series to your chart. This can be done in two different ways. We will show you the long way (most common) first then an easy shortcut.
Double click your chart and choose Select Data from Data options on the Design tab.
Click the collapse tool to the right of Chart Data Range
Reselect your data
Click OK
There is a 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.
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.
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.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.