Learning to create charts or graphs is an important skill to learn for Excel users.  Charts can show relationships between numbers visually which may not be obvious by looking at a screen full of numbers. A common obstacle when creating charts is addressed. The file from the Power Comparison post is used as an example.

YouTube Video on Creating a Chart

Here is a video which shows you how to create a simple graph in Excel:

Create simple chart

Create Chart

PUT Headings Next to Data

It is helpful to have your data and headings next to each other as it is easier to select the information for the chart.  Normally, the data and the headings are next to each other but this is not always the case.

In the Power Comparison file, I copied the company names in the first row to row 11, just above my data.  If different plans were looked at for the same power company then an option would be to copy the name of the plan into row 11 instead.  In this case, I would try to include the name of the power company with the description of the plan.

Check Heading for X-Axis Data

There is one more thing to check before selecting the data.  If the data for your x-axis, i.e. horizontal axis, is numeric, then it is likely that it can be mistaken for data rather than the x-axis labels.  The best way to get around this problem is to remove the heading for this data so that Excel registers that the numbers in that column (or row) are not points to be included in the chart itself.

Applying this to our Power Comparison file, the column containing the “Power Used” data has numbers, i.e. 400, 600, 800, etc.  So I removed the heading “Power Used” in cell A11 as I didn’t want to get Excel confused.  Otherwise, Excel would have treated the first column as a power company called “Power Used” and the numbers 400, 600, … as monthly costs, like the other numbers in the same rows.

It has been my experience that this is the single biggest reason why charts don’t turn out the way that users expect them to.  It is not immediately obvious as to how to fix up the resulting graph.  So charts get put into the “too hard” basket for many people.

Because this is such a common problem I will shortly show you how to fix up such a chart.

Select the Chart Information

Now select the data to be put into the chart, including the headings.

In our example, I selected all the cells from A11 to F18 by clicking in A11 and dragging to F18.  (There are other ways of selecting data but we will save that for another day.)

Insert Chart

The final step of the process is to go to the Insert tab of the ribbon and select a chart from the different options in the Charts group.  When you click on a particular chart type, a drop-down box shows all the variations of that particular chart type.  My recommendation is to keep it simple so that the chart is easy to read.

With the Power Comparison example, I selected a simple 2D column chart – “Clustered Column”.

A new chart displays on your current sheet.

 Fixing up a Chart with Incorrect X-Axis Data

If the heading “Power Used” was not removed before creating the chart then the following chart is produced.

This is not what we wanted.  It is a bit intimidating seeing a chart like this and not knowing how to fix it up.  I think this is a major reason why some new users give up on charts after their first attempt or two.

One long term option for resolving this problem is for Microsoft to offer a wizard or dialog box where the individual ranges in a chart can be specified.

Another option is to delete the chart and to start again but use the steps outlined above to avoid the trap.

Now let’s look at how this chart can be fixed up without deleting the chart and starting again.

Go to the Design tab and click Select Data in the Data group.

The Select Data Source popup displays.

Click on the unwanted data series in the Legend Entries (Series) section.  In our case, this is labelled Power Used.  Then click Remove.

The series is removed and the chart updates immediately with the change.

Now the next step is to fix up the horizontal axis labels.  On the right hand side in the Horizontal (Category) Axis Labels section, click Edit.

The Axis Labels popup displays.

Now, using your mouse, select the data range for the x-axis (horizontal) labels.  The selected range is automatically entered into the Axis label range field.

Click OK.

The Select Data Source popup displays again.  The values for the horizontal axis labels now correctly display in the list on the right hand side, i.e. 400, 600, …

Click OK to close the popup.

The updated chart displays.

So this chart looks the same to the previous chart, except for a slightly different choice of colours for each power company / power plan.

Tidying Up the Chart

The basic chart is now created.  All that remains is to tidy up a few things.  Once you get used to creating charts, you will start to realize that the tidying up is what takes the time.  (Ideally some of these things should be included in the Chart wizard that I am hoping Microsoft will build!)

Let’s briefly take a look at some of your options.

Design Tab

Type group – Click Change Chart Type if you would like to change the type of chart, e.g. change from a column chart to a line chart.

Data group – Click Switch Row/Column if you would like to swap your rows and columns around.  Usually one way is more suited to your data than another. If it doesn’t look right as it is, then try switching the rows and columns.  In our situation, the power companies could be along the x-axis and the different amounts of power used could be the different bars within each group.  However, as we are interested in comparing power prices between plans/companies for the same amount of power used, I think it is best to leave our chart the way it is, as the prices to be compared are right next to each other.

Chart Layouts group – On the Layout tab you can make choices on things like the position of the title or the legend, or whether to have gridlines or a data table. However, in this section you can give yourself a headstart by selecting one of the default layouts.  There are 11 default layouts for the Clustered Column chart.  There are three displayed on the screen.  To see the other ones, click More in the bottom right hand corner of this group – see screenshot below.

Chart Styles group – You can change the colour scheme of the chart by clicking on the desired one.  My colour sense is not very good, as my wife can testify, so I prefer not to play around with colours too much.  However, if I was printing a chart using a black printer then I would consider using the first style showing different shades of grey.

Location group – Click Move Chart to move the chart onto another worksheet or onto a chart sheet.  A chart sheet just has one chart on it.  It does not have all the rows and columns that a worksheet has.  My preference is usually to move the chart onto a chart sheet.

Layout Tab

In the Labels group there are several key things that can be changed:

• Chart Title – options on where to place the chart title.
• Axis Titles – options on where to place the axis titles for both horizontal and vertical axes.  In the case of the vertical axis there are also options on the orientation of the label. Hint: Use the formatting features in the Font group on the Home tab to increase the size of the titles and labels, make them bold, etc.
• Legend – This refers to the table showing what the different colours represent.  In our case, the colours refer to different power companies/power plans.  The legend can be placed in different locations on the chart. My preferences are on the right or along the bottom.
• Data Labels – the value for each data point can be placed on the chart.  There are different options on where to place the data label.  If you want to change the number of decimal points displayed, then change the number of decimal points in the source data and the chart will update automatically.
• Data Table – you can choose to display a table on the chart showing all of the data values.  You can even combine it with the legend.

Axes group – Click Gridlines to change the display of major and minor gridlines on the horizontal and vertical axes.

Format tab

For a basic chart I don’t change anything using the Format tab.

Summary

Creating charts in Excel can be quite easy.  However, it is common for the x-axis label range to be considered as another data series to be plotted, thus messing up the chart.  The procedure for fixing up such a chart was shown.  Useful features on the Design tab and Layout tab for tidying up or improving the chart were also looked at.

Next time we will look at printing in Excel.