Hide an Axis in an Excel 2007 Chart
Introduction
This page demonstrates how to set up a chart in Excel 2007 using both a primary and a secondary vertical axis. It then goes on to show how you can hide one or even both of the vertical axes to help remove some clutter from the chart by removing unnecessary items.
Compare the following two charts to see the effect: before and after
|
|
Before |
After |
We have created an illusion that all data series are now to be found on the primary vertical axis. The main point, though, is one of decluttering. As a bonus point, if you include such an “after” chart in a report with the table of data clearly showing that the grey bars are shown as percentages whilst the primary vertical axis is showing currency values, you might earn some respect for having worked some Excel 2007 Magic!
Creating the Chart
The chart is based on actual gold bullion prices taken from the Research and Statistics section of the World Gold council’s web site (See http://www.research.gold.org/) and a selection of the prices ranging from 1900 to 2007 are shown below:
Download and open the file gold_price_hide_vert_axis_two_b.xlsx (see the link at the bottom of this page)
Select all of the data except the Implied Exchange Rate Column: Select all columns and then deselect the Implied Exchange Rate column or select the data in columns A, B and C and then press <Ctrl>+the data in column E, the War Years data.
Now press the F11 key for an instant chart that will appear on its own work sheet ... you can move it now or later to embed it in your work sheet. NOTE: if you do everything that follows and then move the chart, Excel 2007 might undo some of your work by resetting the font to default ... better to move it to begin with.
Right click on the chart and select Move (you must right click with the cursor in an area on the right or top, bottom or left of the chart away from data series, gridlines ... or you might not see the Move option in the right click menu). Tell Excel 2007 to move your chart to the sheet Annual Average Price data.
Left click and hold the chart to drag it so that the top left hand corner rests on cell G31. To position your chart more precisely, as you are moving the chart and have got near to G31, press and hold the <Alt> key. Move it slowly over cell G31 and you should see it click and lock into place so let go of the <Alt> key and the mouse button.
Using your existing knowledge of charts and Excel 2007 right click on the War years data series and, using the Format Data Series option, tell Excel 2007 to put it on the Secondary Axis.
You can format the secondary and the other two axes, the legend and any grid lines as you see in the screen shot of the chart at the beginning of this page if you wish. Alternatively, format just the horizontal and primary vertical axis together with the legend.
To hide an axis do the following:
- right click on the secondary vertical axis and select the Format Axis option: change Line Color to no line
- right click the secondary vertical axis and select the Font option: change the font to white, or whatever your chart’s background colour is
If you had a title on your secondary vertical axis either set its colour to the same as your chart’s background colour or select it and press the delete key.
You should now have an “after” chart like the one at the beginning of this page with the secondary vertical axis hidden from view. Here is a larger view of that chart.
The grey bars, by the way, have been included on the chart to highlight the first and second world wars: you could add other markers to highlight other major events either that you think are important or that do help to explain the behaviour of the data you are currently analysing.
© Duncan Williamson
August 2009
Download this page in PDF format
Download the Excel 2007 file for this page
