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.
What's on this webpage
1. How to add a secondary axis to an Excel Chart
- Press with right mouse button on on series B.
- Press with left mouse button on "Format Data Series...".
- Select "Secondary Axis".
- 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.
1.1 Add two blank series
This is the data I am working with.
Here is how to add a blank series.
- Press with right mouse button on on the chart.
- Press with mouse on "Select Data...".
- Press with left mouse button on the "Add" button, see the image below.
- Select a blank cell range, in this case D2:D7.
- 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:
1.2 Add spaces between columns series
- Press with right mouse button on on series A on the chart.
- Press with left mouse button on "Format Data Series...".
- Add "Gap width" to 400%.
- 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.
- Press with mouse on the empty legend entry and delete.
- Do the same with Series4.
- Move the legend below.
- Add more descriptive text.
- Delete grid lines.
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.
- Press with right mouse button on on one of the second series' columns/bars.
- Press with mouse on "Add Data Labels".
This article explains how to customize data labels: How to add and customize chart data labels
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.
- Press with right mouse button on on one y-axis.
- Press with mouse on "Format Axis...".
- A dialog box appears or a settings pane if you use a more recent Excel version.
- Change the minimum and the maximum values. Choose values that are closer to the largest and smallest series values.
- Exit the dialog box or settings pane.
Charts category
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]
I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.