Excel 2007's secondary axis feature enables you to view two different data series with two distinct scales, all on the same chart. Use this feature to view your business' sales and inventory figures together, for example. However, Excel lacks commands for creating a third secondary axis. One workaround for this shortcoming is to swap the X and Y axes of several pseudo data series to yield a chart with multiple Y axes. Use Excel's Select Data command to plot your actual data series on such a chart.
Open the workbook with the data for which you want to create a chart. Create a new column, labeled "Y," that numbers the data's rows sequentially, starting from 0 and incrementing by 1. If your data has 10 rows, this step will result in a Y column whose numbers range from 0 to 9. This column will form the X axis of the final chart.
Type a new column label, "X1," in the same row as your data's existing column labels. This new column will hold the pseudo series that will become one of several Y axes. Create additional "X
Fill all rows in X1 with "0." Fill X2 and X3 with "-2" and "-4." Fill the remaining "X
Select the Y column and all "X
Write down the number that fills the "X
Double-click the chart's X axis to display labeling controls. Click the "Fixed" option for the Maximum and Minimum controls, then enter the first number you wrote down in the Minimum text box. Enter the second written number in the Maximum text box. This step sizes the X axis to fit the secondary axes and all table rows.
Duplicate your original data. Rescale the values in each series of the duplicated data such that the values fall within the range of the Y column's values. For example, assume your Y axis has value 0, 1 and 2, and series X1 has -2, 20 and 99. Add 2 to all X1 values to yield 0, 22 and 101. Divide those values by 101 to yield 0, 0.22 and 1. Multiply those values by 2 to yield 0, 0.44 and 2. This step enables all data to fall within the bounds of the Y axis.
Click "Select Data" from the chart's context menu, then click the "Add" button. Select the Y series for the X Values field, then select your rescaled X1 series for the Y Values field. Repeat this step to add the other rescaled series (e.g., X2, X3) to the chart. The chart appears with three secondary axes, but the axes lack hash mark labels.
Click the "Add Data Labels" command from the context menu of one of the secondary axes. This labels the axis' hash marks. Repeat this step to label the remaining secondary axes' hash marks.
- Information in this article applies to Excel 2007. It may vary slightly or significantly with other versions or products.
- Stockbyte/Stockbyte/Getty Images