Author: Oscar Cronquist Article last updated on May 18, 2021

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...".
    Primary data is hidden behind secondary data2
  3. Select "Secondary Axis".
  4. Press with left mouse button on Close button.

Primary data is hidden behind secondary data3

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.

Primary data is hidden behind secondary data4

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.

Primary data is hidden behind secondary data5

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.
    Primary data is hidden behind secondary data7
  4. Select a blank cell range, in this case D2:D7.
    Primary data is hidden behind secondary data8
  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:

Primary data is hidden behind secondary data10

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.

Primary data is hidden behind secondary data11

1.3 How to customize the chart legend

Primary data is hidden behind secondary data13

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.

Primary data is hidden behind secondary data13

Back to top

2. How to add data labels to chart series

Excel chart problem Hard to read series values add data labels

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.
    Excel chart problem Hard to read series values add data labels1
  2. Press with mouse on "Add Data Labels".

Excel chart problem Hard to read series values add data labels2

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

Excel chart problem Hard to read series values adjust min max axis values

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.
    Excel chart problem Hard to read series values format y axis
  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