BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Michael Allsop on 01 October 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Making a chart with negative values look good in Excel 2010
Excel has no difficulty in plotting negative values on a standard column chart, but the initial result can be less than ideal. While any special number formatting, such as negative numbers coloured red, should be carried across from the data sheet onto the axis and data labels, the colours of the columns themselves are harder to automatically format. The steps below will show you how to avoid a laborious manual approach to formatting negative columns with a different fill colour.
Creating the chart
First, let us consider a fictional clothing supplier who is having mixed feedback with regard to the quality of their clothing. The small data table below reflects this:
The trousers and shoes aren't impressing the customers.
To keep things straightforward, the chart used in this example is the standard 2D Clustered Column chart. The chart is produced in the usual way by selecting a cell within the list and clicking on Insert --> Column --> Clustered Column (or did you know about the F11 shortcut?). This results in the chart below:
It's OK, but it's not perfect. Those labels have got to move for a start, then we can consider the column fill colours!
Modifying the primary horizontal axis labels
This next step addresses purely the essential formatting needed (in this Owl's opinion). If you wanted to add a background picture of dancing penguins then that is entirely up to you.
To format the Primary Horizontal Axis, right click the mouse cursor on any of the labels, for example 'Trousers'. Then select Format Axis... from the menu:
You can hover with the mouse before clicking to make sure that you're over the Horizontal (Category) Axis.
Halfway down the Axis Options dialog box you'll find the tick mark and label settings. Change the Axis labels setting to Low and this will move the labels to below the plot area.
Choose Low and you'll see the effect straight away on the axis labels
Applying automatic formatting to the negative values
If we wanted to draw attention to the negative values (assuming we do...) then a change of fill colour would make them stand out. However, rather than manually adjusting this for each column as it becomes negative or goes back to being positive, we can get Excel 2010 to pick from two preset fill colours, one for positive values and one for negatives.
To do this we need to get into the formatting settings for the data series - the simplest way is to right click on one of the columns and choose the Format Data Series... option.
Now you need to look at the Fill settings and firstly pick Solid fill:
Choose 'Solid fill' rather than 'Automatic'.
Then, select the tick-box option for Invert if negative. This should then present you with two fill colour options, the first for positive value columns and the second for negatives. To make things obvious, the example below opts for red negative columns:
Pick a contrasting colour for the negative columns.
To complete the process, click the Close button on the dialog box, and you should be left with a chart resembling the one below:
This should make even the toughest manager sit up and take notice.
You can always check to see that it works by changing some of the values in the spreadsheet, in this instance the opinions of the shirts have plummeted while the trouser stitchers have obviously pulled up their socks...
You should see the fill colours change automatically as the columns go from positive to negative.