Software

Five tips for enhancing Excel charts

With a few simple tweaks, you can make your Excel charts easier to interpret and convey data in a more compelling and meaningful way.

Excel's charting features are powerful, flexible, and easy to implement. You select some values, click a button... and you have a chart. But don't stop there. There are many ways to enhance a chart, not for the sake of enhancing, but to emphasis the chart's point or purpose or to improve readability. The following tips are incredibly easy to implement, so you can share them with your users. They'll have no trouble applying these simple techniques.

Note: This article is also available as a PDF download.

Figure A shows the data source for all the example graphs, but these techniques work with almost all chart types. To create the example column graph, select A2:E6 and then click the Insert tab. From the Column drop-down in the Charts group, choose Clustered Column (the first option).

Figure A

Charting this data is easy, but Excel offers many ways to enhance the initial results.

1: Shorten long Y axis labels

Large numbers on the Y axis require a lot of space. For example, Figure B shows long Y axis values. It certainly isn't horrible, but Figure C is better. In this case, a custom format shortens the labels. To create this format, do the following:

  1. Right-click the Y axis (try right-clicking one of the labels) and choose Format Axis from the resulting context menu.
  2. Choose Number in the left pane. In Excel 2003, click the Number tab.
  3. Choose Custom from the Category list.
  4. Enter the custom format code $0,, \m, as shown in Figure D. In Excel 2010, click Add.
  5. Click Close (OK in Excel 2003) to see the custom format at work (Figure C).

Figure B

These Y axis labels are too long.

Figure C

A custom format shortens the labels.

Figure D

Enter this custom format to shorten the Y axis labels.

This particular combination will display all digits to the left of the second thousands separator. The \m component displays a literal lowercase m. In this case, I chose the 0 placeholder instead of # because 0 displays a 0 value at the 0 gridline. When you update the source values, check the axis values before printing and distributing, just to make sure the custom format accommodates the new values.

In the end,  the reader's perception might be all you're changing, but consider reducing lengthy Y axis values for both space considerations and readability when possible.

2: Position X axis labels

A negative value can seemingly displace the X axis labels. Figure B (above) is a good example. By default, Excel displays the X axis labels just below the $0 m gridline, not below the plot area. This might be exactly what you want, but you have control -- not Excel. To adjust the X axis position, do the following:

  1. Right-click the X axis and choose Format Axis. (Try clicking one of the labels.)
  2. Choose Axis Options (the default) from the left pane. In Excel 2003, click the Patterns tab.
  3. Choose Low in the Tick Mark Labels section, as shown in Figure E.
  4. Click Close (OK in Excel 2003). The results are shown in Figure F.

Figure E

There are several positions for the X axis labels.

Figure F

Displaying the X axis labels below the plot area makes them easy to find.

The axis position is a setting and not a bug. By changing the setting, you can quickly reposition the labels.

3: Add a dynamic title

You usually add a title manually after creating the chart. Consequently, the title's a static value you have to remember to update when the need arises. On the other hand, if you can link the element to a sheet cell, the chart's title will update when the source data changes, saving you a bit of work. The trick is to reference the cell in the title element's Formula bar, as follows:

  1. Add a title element by selecting the chart, clicking the context Layout tab, and then clicking the Chart Title drop-down in the Labels group and choosing an option.
  2. Select the title element.
  3. In the Formula bar, enter the cell reference and press [Enter]. Or enter = and then click the sheet's tab, select the cell, and press [Enter]. Figure G shows the result.

Figure G

Reference a cell to create a dynamic title.

Excel immediately updates the title's text to reflect the cell's contents. If you change the contents of the linked cell (SalesCommission!A1 with Merge & Center applied to cells A1:E1), the chart title will also reflect the change.

4: Quickly add and distinguish a new series

The values you want to chart aren't always in a contiguous range. For example, you might want to add the commission values to the example graph, but a row of ungraphed values is in the way (row 7). Fortunately, there's an easy way to add a new series to an existing chart: Simply cut and paste the values into the chart. Excel knows exactly what to do with the new data. To add the commission values to the example graph, do the following:

  1. Select B8:E8 and press [Ctrl]+C.
  2. Select the graph and press [Ctrl]+V. Excel pastes the new series into the existing graph.

I told you it was easy! This might take care of your needs. But you might also want the series to stand out in some way. If so, simply right-click the new series and choose Format Data Series. Figure H shows the new series sporting clipart. (Choose Fill, Picture Or Texture Fill, Clipart. Then, select the file, select Stack, and click Close.) As you can see in the figure, the different style draws your eye right in.

Figure H

You can cut and paste a new series into an existing graph.

5: Change series titles

Similar to using the Formula bar to create a dynamic title, you can also use the Formula bar to usurp a link. For example, the new series includes a reference to the legend values, but the text isn't very descriptive. If you replace this reference with a string, you can usurp the legend's default value with a static value:

Select the new commission data series.

Highlight the first argument. As you can see in Figure I, the first argument (the legend label) is missing. Enter "Commission" and press [Enter]. Be sure to include the quotation marks, as you must delimit the value as a string. Figure J shows the results.

Figure I

The first argument, the legend label, is missing.

Figure J

Change a legend label to a static value.

You can change the legend for any series, not just one you've added. You might have to adjust the size of the legend object to accommodate the new text.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
stapleb
stapleb

Great ideas again Susan. I did not know about the copy and paste, so will be trying that out to see how it works. My biggest bugbear with 2007/2010 is you cannot double click a column to format it, something available in all earlier Excel versions. I won't even go into what I think of colour schemes - oh I might - am I so colour blind that I don't know what colours to place together or that may actually be used as "Company Colours". That's it, I'm over my grizzle. Just a little tip, if you click once on a data series, Ctrl + 1 (top of keyboard NOT numeric keypad) will place you into the Format Data Series dialogue box.

kirsty
kirsty

Another tip for enhancing the readability and understanding of your excel data - don't use excel charts! Use another tool on top of Excel - such as a SaaS analytics tool (we use

ShortcutLibrary
ShortcutLibrary

Might also want to add the keyboard shortcut - F11 can be used to create the chart

dhays
dhays

It appears that Figure J is what she calls Figure D. Figure D is for the text that follows it. I have not even heard of copy and paste into a chart. It sound like an easy way to add to one.

jody.burton
jody.burton

Below "Figure D," I can see the target text that is supposed to display an image, along with an unrelated image that appears to cover some text. On the article itself, I have never tried the copy-paste method of adding a series. Never knew it was that easy.

fieldrd
fieldrd

sorry, kirsty...BIME as a 6th tip is a bummer at $60/mo

ssharkins
ssharkins

Thanks for the heads up -- we'll get the figures fixed asap.

Editor's Picks