Excel chart problem: Hard to read series values

It might be impossible to read the smaller series values on the y-axis if you have two series of data plotted on a chart and one of the series has much smaller values, see the image above.

Data in series A on the Excel chart above is really hard to compare and analyze. You can solve this problem by moving series B to a secondary axis.

1. How to add a secondary axis to an Excel Chart

1. Press with right mouse button on on series B.
2. Press with left mouse button on "Format Data Series...".
   
3. Select "Secondary Axis".
4. Press with left mouse button on Close button.

If you are unlucky, one of the data series is hidden. In this case, series A is totally hidden behind series B, see chart above. You can change the transparency for series B but in my opinion, it makes the chart better but not great.

Yes, you can see both data series but it is still a mess and the colors in the legend and the columns don't match, see chart above. Don't add transparency, try separating the two series instead. Instructions, below.

Back to top

1.1 Add two blank series

This is the data I am working with.

Here is how to add a blank series.

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select Data...".
3. Press with left mouse button on the "Add" button, see the image below.
   
4. Select a blank cell range, in this case D2:D7.
   
5. Press with left mouse button on OK button.

Repeat steps 3 to 6 to add a fourth blank series, you can use the same cell range. Then press with left mouse button on the OK button. The chart now looks like this:

Back to top

1.2 Add spaces between columns series

1. Press with right mouse button on on series A on the chart.
2. Press with left mouse button on "Format Data Series...".
3. Add "Gap width" to 400%.
4. Press with left mouse button on OK.

1.3 How to customize the chart legend

The steps below describe how to edit the legend and tell the user which chart series belong to which y-axis.

1. Press with mouse on the empty legend entry and delete.
2. Do the same with Series4.
3. Move the legend below.
4. Add more descriptive text.
5. Delete grid lines.

Back to top

2. How to add data labels to chart series

One way to make the second smaller series easier to read is to add labels to each column/bar, however, the size of the columns still makes it hard for a quick comparison.

1. Press with right mouse button on on one of the second series' columns/bars.
   
2. Press with mouse on "Add Data Labels".

This article explains how to customize data labels: How to add and customize chart data labels

Back to top

3. Adjust axis min and max value

The image above displays a chart that has both the min and max values of the y-axis changed. This makes it easier to compare chart column sizes, however, it also may make the increase or decrease more pronounced and more serious than it really is.

1. Press with right mouse button on on one y-axis.
2. Press with mouse on "Format Axis...".
3. A dialog box appears or a settings pane if you use a more recent Excel version.
   
4. Change the minimum and the maximum values. Choose values that are closer to the largest and smallest series values.
5. Exit the dialog box or settings pane.

Back to top

Get the Excel file

Primary-data-is-hidden-behind-secondary-datav2.xlsx

Charts category

Add pictures to a chart axis
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]

Change chart axis range programmatically
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]

Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Excel categories

Leave a Comment