Ways to change the presentation of an Excel chart using a secondary axis

This article mainly uses the example to introduce the use of the secondary axis to change the display shape of the Excel chart, at the same time show the two groups of different numerical range of data is relatively large.

If you want to show both sets of data series that have a larger range of values on the chart, you will most likely use the secondary axis. The most common example is to show both numbers and percentages on the chart, such as the following data:

The table contains two sets of data: "Sales" in each quarter and their "year-on-year growth" ratios to the same quarter of the previous year. The range of these two sets of data is different (500~800 vs 0.1~0.3), and the numerical level is also a big difference, and if you use a clustered histogram to present the two data sets in the same chart, the results will look like the following:

Although you can see two data series in the legend, only a set of columns is displayed in the actual chart. In the case where the levels and sizes of the two sets of data are equal, the normal clustered column chart with two series should be like this in the figure below. Obviously, the previous chart shows only a set of columns because the number of data representing the growth rate is too small to be clearly displayed on a chart that has a reference to the range of sales values.

In the article, "Using logarithmic scale axes to reduce the morphological differences in Excel charts," once introduced to a single set of data, if there is a large number of differences in the case, you can use the "logarithmic scale" approach to solve, and now for such a large difference between the data group, can no longer rely on " Logarithmic scale, but is better suited to choose the settings to use the secondary axis to resolve.

The secondary axis is equivalent to the original "primary axis", a new coordinate reference system is established, two axes are independent, you can set different maximum minimum value range and interval. For column and line charts that display data vertically, you can create primary and secondary axes, which are located on the left and right sides of the chart, and for bar charts, you can create primary and secondary axes, which are located at the top and bottom of the chart, respectively.

The different data series in the chart are free to select any one of the two axes as its reference standard. For the above case, we can set up a secondary axis, in order to 0~0.4 such a range of values as the display range, to accommodate the normal display of the percentage data.

The way to do this is to select the "Year-on-year Growth" data series in the chart, and then right click on the "Format data series" command, and in the Open dialog box, select the option to draw the series on the secondary axis.

There is a problem that can be confusing to a lot of people, in the chart see "year-on-year growth" of this group of graphics, how to select it? There are at least three ways to help you select an invisible graphic:

The first method can be made out of nothing, the above data table in the "year-on-year growth" of a data to a relatively large number, and then will be shown in the Chart column shape, select this series set the secondary axis, then the data in the table restore;

The second way is to compare the step-by-step menu operation, after you select a chart, you can find the current selection Drop-down box in the Format tab of the Chart tool, and in the Drop-down box, you can select the main objects in the chart, including the Year-on-year growth data series that are not displayed properly.

The third way is to curve the nation by the legend shown in the chart, although the chart does not display a column graph with the "growth on", it still appears in the legend, "individually selected" in the Legend (you need to click Two) for the legend of the data series, and then in the right-click menu, you can choose the Format data Series command.

After the "Year-on-year Growth" data series setting is displayed on the secondary axis, the following results are obtained, with a different ordinate axis on the left and right of the chart (for the appearance of the display, both sides of the coordinates can be set to use the same scale interval). The two groups of columns are stacked together, and the column height corresponds to the axis ruler positions on both sides respectively. In this way, the same chart solves the problem of two large groups of different data groups.