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).
Charting this data is easy, but Excel offers many ways to enhance the initial results.
1: Shorten long Y axis labelsLarge 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:
- Right-click the Y axis (try right-clicking one of the labels) and choose Format Axis from the resulting context menu.
- Choose Number in the left pane. In Excel 2003, click the Number tab.
- Choose Custom from the Category list.
- Enter the custom format code $0,, \m, as shown in Figure D. In Excel 2010, click Add.
- Click Close (OK in Excel 2003) to see the custom format at work (Figure C).
These Y axis labels are too long.
A custom format shortens the labels.
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:
- Right-click the X axis and choose Format Axis. (Try clicking one of the labels.)
- Choose Axis Options (the default) from the left pane. In Excel 2003, click the Patterns tab.
- Choose Low in the Tick Mark Labels section, as shown in Figure E.
- Click Close (OK in Excel 2003). The results are shown in Figure F.
There are several positions for the X axis labels.
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:
- 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.
- Select the title element.
- 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.
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:
- Select B8:E8 and press [Ctrl]+C.
- Select the graph and press [Ctrl]+V. Excel pastes the new series into the existing graph.
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.
The first argument, the legend label, is missing.
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.