OmniDocHow to add dual axes and secondary axes to Excel charts
When two sets of data with different units are to be compared in the same chart, one of the series can be set as the secondary axis. This article explains the basis for judgment, the setting steps in Excel, and how to check whether the chart is legible after adding it.
Key points of this article
- When the units or magnitudes of the two sets of data are very different, it is necessary to set a secondary coordinate axis for one of the series so that the two lines can each use their own scales.
- Right-click the series in Excel, open the data series formatting pane, and change the axis position to the secondary axis to complete the setting.
- The secondary coordinate axis and the combination chart solve different problems: the former manages the scale, the latter manages the graph type, and the two are often used together.
- After setting, add unit titles to the left and right vertical axes respectively, and confirm the axis corresponding to each line according to the legend.
- The visual intersection point of the double vertical axis graph is affected by the scale. The original data sheet should be attached when sending it to others.
When making Excel charts, we often encounter this situation: sales are hundreds of thousands of yuan, and the growth rate is only a few percentage points. Put the two sets of data on the same vertical axis, the growth rate line will be close to the horizontal axis, and the change will be almost invisible. At this time, you need to add a vertical axis on the right side of one of the series, which is the secondary coordinate axis, so that the two sets of data can be displayed with their own scales.
This article is written for those who need to display two types of unit data in one picture at the same time, such as readers who make monthly reports, organize experimental records, or create pictures to accompany papers. After reading this, you will be able to judge when to use dual axes, know how to move a series to the secondary axis in Excel, and check whether the plot after addition is easy to read.
Many people search for "How to set the primary and secondary coordinate axes in excel charts". In fact, what they want to solve is the problem of reasonably displaying two lines in one chart. The following is explained in the order of"judge first, then operate, then check".
# how can I put the two sets of data units into the same image? Conclusion in a word
If the two sets of data are of different units or orders of magnitude and must be compared, set the secondary coordinate axis for one of the series; if you just want the graphic style of the two sets of data to be different, you can use a combination diagram, and the two can be used together.
| Your situation. | Recommended practice | reason |
|---|---|---|
| The two groups of data units are different (such as amount and ratio), and there is a large difference in the range of values. | Put the series with smaller values on the secondary axis | Each of the two series uses its own scale, and the changes can be seen clearly. |
| The units of the two groups are the same and the values are similar. | Maintain the primary axis without adding secondary axes | The same scale makes it easier to compare the size. |
| It is hoped that one group will use columns and another group will use broken lines. | Use the combination diagram and set the broken line series as the secondary axis | Graphic type and scale are two things, often set together |
| The relationship between the two groups of data is weak, and they just want to look at it together. | Break it down into two small pictures | Double vertical axes are easy to misread the cross relationship between two lines. |
# before you start: confirm these points first
- data should be arranged neatly in the same table. * the chart reads contiguous data areas, usually the first column is classified by horizontal axis (such as month), and the remaining columns are a series. The data is arranged neatly, so it is not easy to make mistakes when selecting the data area.
- confirm the units and magnitude of the two sets of data. only when the magnitude difference is large, the secondary coordinate axis is meaningful. If both groups are percentages, or if the values are already close, adding two axes will make the reader misunderstand.
- figure out which series to put on the right. generally put the smaller number, less fluctuation, or the group you want to focus on on the secondary axis so that the reader can find it by looking at the scale on the right.
- confirm whether the data source is reliable. if the data comes from the PDF table, the scanned part needs to be identified by OCR first. The PDF editor of the OmniDoc desktop client supports OCR and can be exported to Excel. Both OCR recognition and export can go wrong. Check the numbers item by item before importing the chart.
# steps to set a series as a secondary axis in Excel
- Open the workbook that contains the chart and click the chart to select it.
two. In the figure, click the line or set of columns you want to move to the right, and make sure that the entire series is selected, not one of the data points.
- Right-click the series, find the entry for formatting the data series in the pop-up menu, and open the corresponding settings pane.
- In the series options section of the pane, change the position of the axis to the secondary axis.
- The new vertical axis appears on the right side of the chart, and the original vertical axis remains on the left.
- Select the left and right vertical axes, and set small and large values in the axis options, so that both sets of data occupy the appropriate height.
- Add titles to the two vertical axes, such as "sales (ten thousand yuan)" and "growth rate (%)", so that the reader can see the unit at a glance.
- If you want the two sets of data styles to be different, right-click the series, find the entry to change the series chart type in the menu, set it as a line chart, and confirm that the setting of the secondary axis is still retained.
# check item by item after completion
| Check items | How to see | What if there's a problem? |
|---|---|---|
| Changes can be seen clearly in both sets of data. | Each line or set of columns accounts for a certain proportion of the height of the chart, rather than close to the bottom. | Adjust the small and large values of the corresponding vertical axis |
| Which axis corresponds to each series? | Check whether the color of the legend is consistent with the vertical axis title. | Title the two vertical axes and indicate the units in the legend |
| Is the starting point of the column chart reasonable? | The vertical axis of a column chart usually starts at 0. | Don't truncate the starting point of the bar chart in order to widen the gap. |
| The chart data is consistent with the original table. | Spot check two or three data points and compare them with the original values of the table | If the data comes from OCR or export file, go back to the original file to check |
| Does the biaxial cause misreading? | When you look at only one axis, will the conclusion change? | The data with weak relationship is divided into two pictures, which are shown separately. |
# Core comparison
There are four common ways to put two sets of data into the same picture, each of which is suitable for different scenarios.
| Practice | What can be solved? | Need to pay attention to |
|---|---|---|
| Put it all on the principal axis | Direct comparison when the unit is the same and the value is close. | Series with small values are easily pressed into a straight line. |
| A series is set as the secondary axis | With different units and large differences in magnitude, we still need to compare the trend. | The scale setting of two axes will affect visual judgment. |
| Combination chart (column plus fold line) | Display different types of data such as quantity and ratio at the same time | It usually needs to be used with the secondary coordinate axis. |
| Break it down into two pictures | The relationship between the two sets of data is weak, or biaxial is easy to cause misunderstanding. | Readers need to understand with reference to the two pictures. |
# frequently asked questions (FAQ)
1. The secondary axis is set, why does the right scale look unreasonable?
The right scale is determined by the series corresponding to the secondary axis. When the value range of the series is small, Excel will automatically select a smaller scale range. Double-click the right vertical axis and manually set the small and large values in the axis options.
2. How do I delete the secondary axis?
Right-click the series, open the data series formatting pane, change the position of the axis back to the main axis, and the right vertical axis will disappear. If you only want to hide the scale numbers, you can set the label position to none in the axis options.
3. Why is the double vertical axis diagram easy to be questioned?
The intersection of two lines depends on the scale setting of the two axes, and adjusting the scale may change the visual "intersection" effect. When showing to others, it is recommended to mark the unit and scale range, and attach the original data table for easy comparison.
4. Can the tables in the scanned version of PDF be used directly to make charts?
Scanned parts usually cannot be directly copied into available data and need to be identified by OCR first. The PDF editor of the OmniDoc desktop client supports OCR and can be exported to Excel. After export, you still have to check the figures item by item, make sure it is correct, and then make the chart.
# Summary
When two sets of different units of data are compared in one picture, it is a direct way to set secondary axes for a series that is small or needs to be highlighted. In Excel, open the data series format setting by right-clicking the series, and then change the position of the coordinate axis to the secondary axis. After setting, mark the units of the two vertical axes respectively, and check the data, this excel chart how to set the two vertical axes of the operation is considered complete.
