A complaint about many charts in general, and Excel charts specifically, is that they look awful and are hard to understand. However, you have the power to make your charts clear and clean and easy to read. The way to improve your chart is to remove clutter and reduce the amount of ink used to print the chart. To do this you must keep asking: does the chart need this feature, and if so, does it need to stick out so strongly?
By “Simpler Chart Formatting”, I don’t necessarily mean easier ways to format the chart. Instead, I mean ways to make the formatting of a chart simpler, less cluttered, and easier to understand. The techniques are also pretty easy. At first, the hard part is just remembering to carry them out.
As we all know, the Excel defaults are pretty ugly. They are somewhat better in Excel 2007, but I still never use the defaults. Aside from this set of images, I will stick to “Classic” Excel, that is, Excel 2003 and earlier, but the general concepts hold true for any version of Excel, and any graphics package.
Default chart formats in Classic Excel (2003 and earlier).
Default chart formatting in Excel 2007.
The default charts in Excel 2003 and earlier feature a dull gray background (British and Canadian versions use dull grey instead). The first step to cleaning up your chart is to change this ugly background to white.
Removing the muddy background is a major improvement.
How important is the border around the plot area, around the legend, or around the entire chart? The legend never needs a border. Other borders can often be removed entirely, or at least lightened.
Removing the outer border and the legend border cleans up the charts.
Removing the plot area border and gridlines makes the charts simpler.
Sometimes I use a light gray chart area border on embedded charts, so the chart border matches the gridlines between cells in the worksheet.
Sometimes a light border is okay.
If your chart uses gridlines, they should be the lightest features on your chart. The custom palette I use for my own work has a special gridline gray several shades lighter than the 25% gray on the default palette. But often gridlines are not even needed.
Above: Dark gridlines (left) and light gridlines (right).
Below: No gridlines and no plot area border.
De-emphasize the axis lines by using a light or medium gray instead of black. You could even use medium gray for the axis tick text, but I usually stick with black text. For a category X axis, you can eliminate tick marks.
If you are using gridlines, you may be able to remove the corresponding axis line, leaving only the tick labels.
You can often unclutter a chart by using fewer tick mark labels. As pointed out in the comments, keeping the unlabeled tick marks is useful.
5. Number Formatting
Use custom number formats to reduce the complexity of axis labels and other text in the chart.
Above left: original cluttered axis labels.
Above right: remove unnecessary ‘cents’.
Below: replace thousands by ‘K’ suffix.
6. Chart Types
Replace every 3D chart with the corresponding 2D type.
Only use pie charts if there are three or fewer wedges. Use column or bar charts for more categories.
Cluttered pie (above left) and simpler pie (above right).
Unclutter a pie chart’s data by using a column or bar chart.
Use column charts to show values for discrete categories. Use line charts to trends over time.
7. Series Formatting
For column, bar, and area charts, remove the black outline. Use light to medium colors for fills, and don’t use patterns or gradients. Use darker, saturated colors for lines and markers. Use colors with appropriate contrast against the background and compared to other series colors. Avoid combinations that react with each other.
Too gaudy (left) and too faint (right).
Better color combinations.
8. Horizontal Text
Vertical text is difficult to read, and inclined text on a monitor is bad because of distortion of the characters.
In a column chart, Excel will leave out some tick labels if they would overlap, but this leaves the readers guessing about the missing labels.
Turn the whole chart on its side.
9. Labels, not Legends
It is easier for a reader to identify series if they are directly labeled. Using a font color that matches the series formatting is also helpful. A legend takes up valuable space and makes the reader divert attention back and forth.
Top left: Ineffective Legend. Top right, bottom left and right: Effective Labels.