Primary data is hidden behind secondary data
If you have two series of data and one of the series is on a different scale, it might be impossible to interpret the smaller series.
Data in series A on the excel chart above is really hard to compare and analyze. You can solve this by moving series B to a secondary axis.
Add a secondary axis
- Right click on series B
- Click "Format Data Series..."
- Select "Secondary Axis"
- Click 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.
Add two blank series
This is the data I am working with.
Here is how to add a blank series.
- Right click on chart
- Click on "Select Data..."
- Click "Add" button
- Select a blank cell range, in this case D2:D7
- Click OK
Repeat step 3 - 6 to add a fourth blank series, you can use the same cell range. Then click OK button. The chart now looks like this:
Add space between columns series
- Right click on series A on the chart
- Click "Format Data Series..."
- Add "Gap width" to 400%
- Click OK
Customize legend
- Click on the empty legend entry and delete
- Do the same with Series4
- Move the legend below
- Add more descriptive text
- Delete grid lines
Download excel *.xlsx file
Primary data is hidden behind secondary data.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
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 […]
Color columns in chart based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Rearrange data source in order to create a dynamic chart
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
Change chart series by clicking on data [VBA]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Compare data in an Excel chart using drop down lists
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
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.
