Ozgrid, Experts in Microsoft Excel Spreadsheets

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Introduction To Excel Charts

 

Excel Training Level 2 Lesson 20

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 2 TRAINING INDEX

FREE EXCEL HELP

INTRODUCTION TO CHARTS

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.

Chart Lingo

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.

3-D walls and floor

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.

Axis

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.

Chart Area

The entire chart and all its elements.

Chart Sheet

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.

Chart Wizard

The Chart Wizard will guide you through the steps for creating an embedded chart on a worksheet or modifying an existing chart.

Data label

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.

Data marker

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.

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.

Drop lines

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.

Embedded chart

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.

Error bars

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.

Gridlines

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.

High-low lines

In 2-D line charts, lines that extend from the highest to the lowest value in each category.

Legend

A box that identifies the patterns or colors assigned to the data series or categories in a chart.

Legend keys

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.

Moving average

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.

Plot area

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 and tick-mark labels

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.

Titles

Descriptive text that is automatically aligned to an axis or centred at the top of a chart.

Trendline

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.

Trendline label

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.

Up-down bars

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.

Chart Elements

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

What Chart Type to Use

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.

Column

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.

Bar

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.

Line

A line chart is best suited to demonstrate the trend of a set (or sets) that have related data.

Pie

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.

XY (Scatter)

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.

Area

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.

Doughnut

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.

Radar

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.

Surface

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.

Bubble

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

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.

Cone, Cylinder and Pyramid

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!

Chart Wizard

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:

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.

Embedded versus Chart Sheet

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.

Chart Templates

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.

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.

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.

Making Changes to Charts

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.

Adding a New Series to a 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.

Here are three much easier ways to add a new series to an existing chart.

* 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.

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.

Chart Options

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.

Plot empty cells as

These options controls how empty cells are plotted on charts. There are three options.

  1. Not plotted (leave gaps): Leaves gaps in the line for empty worksheet cells in a data series, making the line segmented.

  2. Zero: Treats blank cells as zeros, so that the line drops to zero for zero-value data points.

  3. Interpolated: Interpolates data points that represent blank cells, and fills in the gaps with connecting lines.

Plot visible cells only

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.

Chart sizes with window frame

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.

Show Names

This option when checked means Excel will display the name of a particular chart element each time you hover your mouse pointer over it.

Show Values

This option when checked means Excel will display the value of a particular data marker each time you hover your mouse pointer over it.

Summary

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.

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 2 TRAINING INDEX