Excel chart formatting tricks

Excel's charting engine is powerful, but don't forget simple tricks, such as adding images, can make all the difference.

There’s a lot to love about the new charting engine in Excel 2007 and 2010. However, not all the things you can do with the tools are immediately obvious or intuitive to use. Here we show you some hidden techniques, including how to colour negative values differently to positive values, conditionally colour data labels, add text and pictures, and more.

Invert if negative

Use the ‘Invert if Negative’ feature to format a chart so that negative values are coloured differently to positive values. To see this at work, create a 2D column chart using a single data series that includes some negative and some positive values. Click on one chart column to select all of them, right-click and select ‘Format Data Series’ —> Fill —> ‘Invert if Negative’ and with the dialog still open, select ‘Pattern Fill’ and set the two colours you want to use. For now, ignore that the tool is applying a ‘5% Fill Pattern’. Keep the dialog open, select the ‘Solid Fill’ option and you’ll see some new selectors for the foreground and background fill colours. If the data in the chart changes, the column fill colours will change, too.

Coloured labels

You can conditionally colour the data labels for an Excel chart. So, if you make a column chart with negative numbers one colour and positive values another, you can match the labels to the chart colours or near enough. To do this you’ll use a custom format, so select the chart and from the ‘Layout Tools’, select ‘Data Labels’ —> ‘Outside End’. From the same dialog, click ‘More Data Label Options’ —> ‘Number group’ and choose the Custom category. Type a conditional format code like this for the labels and click Add: [color 46] general; [color 10] general.

This code provides colours for the axis labels — the first colour is used for positive values, the second for negative values and if you add a second semicolon, the third and final colour would be used to format zero values. To see the colours and their numbers, visit http://dmcritchie.mvps.org/excel/colors.htm.

You can also build more complex colouring solutions this way such as: [Color12][<=100]General ;[Color37][=0]General.

Coloured pie segments

To control colours on an Excel pie chart, such as that in the example below, you can select colours on a slice-by-slice basis. To do this, click on the pie chart and then click on the individual pie slice. Right-click and select ‘Format Data Point’ —> Fill —> ‘Solid Fill’ and choose a colour to use. Repeat for every other pie slice.

Three easy Excel charting tips

1. Add descriptive detail or titles to a chart using a text box. Go to Insert —> ‘Text Box’ and draw a box on the chart or click in the title box. Click in the Formula Bar, press the = (equals) sign and click in a cell in the worksheet to add that content to the selected object.

2. To format every column in a single-series column chart a different colour, click on a column to select them all. Right-click, select ‘Format Data Series’ and click the Fill group. Check the ‘Vary Colors by Point’ option and each chart bar will be a different colour.

3. To add an image over a chart, click in the chart, go to Insert —> Picture —> ‘From File’ and select an image to add. Scale it and place it on the chart. Repeat to add more images. These images move and resize automatically with the chart.