Microsoft Office/Making Beautiful Charts

Changing the Color Palette

The first problem in creating beautiful charts is the choice of colors. Excel only offers 64 colors, and most of them are hideous.

Fortunately, you can change them, and any changes you make travel with the workbook. This means that when your client opens it up, it's going to have your changed colors in it. It also doesn't affect any other workbooks - their colors will be unchanged.

Here is the standard Excel color palette, with very few light colors, which are important for attractive graphics. Alongside is shown an alternative, with many more light colors, arranged in sequence instead of all jumbled up.

So how do you change them? Click on Tools, Options, then the Color tab. You should see the color palette below.

Click a color you want to change, and then the button labeled Modify on the right hand side.

Either of the two dialogs below will then come up. You'll see you can flick between them by choosing the Standard or Custom tabs.

The Standard colors let you choose from a much larger number of colors by simply clicking on them. This is all you need, most of the time.

Suppose, however, that you like a color but it is little too dark. Select it, then click the Custom tab. You can then use the slider bar at the right of the rainbow in the middle, to make it lighter.

If you are feeling creative, you can play with the numbers at the bottom to create the perfect color.

If you've gone to the trouble of changing colors and you want to copy them into another workbook, or if you want to use the palette I've created for you in this workbook, it's quite easy.

Open both workbooks (the one you want to copy from, and the one you want to copy to)
Activate the one you want to copy into.
Select Tools, Options, and then the Color tab. You'll see a dropdown labeled "Copy colors from". Click this and pick the workbook you want to copy from.
All done. Save.

Applying Formatting

Standard Excel charts use strong colors and strong lines. This is probably so they show up clearly in all sorts of situations.

While this approach is understandable, the problem is that it results in charts lacking visual appeal. The best way to correct this is to see the effect of making some changes. Please note that there are many ways to do this, and these changes are not necessarily the best.

The chart should now look like this. Let's change those awful colors next...

The chart should now look something like this…those colors are probably too light, but there's a nice trick we can use to give them some body.

Let's keep it fairly simple, so we'll click the One color button alongside

The chart should now look something like this…the colors have more body

Note: beauty is in the eye of the beholder. You may prefer to use different colors to start with and not go to all this trouble with special effects.

There is no suggestion that you do all this for every chart. But the more you know, the more choices you will have when you build your next chart.

Now, perhaps, some subtleties

Examples

Here is a line chart.

the lines have been made thicker, with no markers (which is OK as long as it will be printed in color - if it is printed)
the colors are a little softer
the legend has been removed & each line has been directly labeled, which makes it easier to read. (You can do this by selecting the last point of each series and showing the series name as the label - when you've selected the point, select Format Data Point, then Data Labels tab, Series Name checkbox).
a vertical grid has been added on the X axis, for every second point.

Here is a single series bar chart. Note how on the left, the gaps between bars are wider than the bars, which distracts your eye. If you narrow the gap, as on the right, it improves the look of the chart. (Do this by selecting the series, click Options tab, and reduce the gap width). If the bars get too fat when you reduce the gap, you can make the chart narrower until the bars look right.