Table of Contents
To create an overlapping bar chart in Excel, follow these steps:
1. Select the data that you want to include in the chart.
2. Click on the “Insert” tab and select “Bar” from the “Charts” section.
3. Choose the first type of bar chart that you want to use.
4. Right-click on the chart and select “Select Data” from the menu.
5. In the “Select Data Source” window, click on the “Add” button.
6. In the “Edit Series” window, enter a name for the series and select the data for the series.
7. Repeat steps 5 and 6 for each additional series you want to add.
8. Click “OK” to close the “Edit Series” window.
9. In the “Select Data Source” window, click on the “Switch Row/Column” button to switch the data series and category labels.
10. Click “OK” to close the “Select Data Source” window.
11. To make the bars overlap, right-click on the chart and select “Format Data Series” from the menu.
12. In the “Format Data Series” window, under “Series Options,” change the “Gap Width” to a smaller number.
13. Adjust any other formatting options as desired.
14. Your overlapping bar chart is now created.
Create an Overlapping Bar Chart in Excel
An overlapping bar chart is a type of chart that uses overlapping bars to visualize two values that both correspond to the same category.
This tutorial provides a step-by-step example of how to create the following overlapping bar chart that shows the predicted vs. actual points scored by various basketball players:
Let’s jump in!
Step 1: Enter the Data
First, let’s create the following dataset that shows predicted points vs. actual points of various basketball players:
Step 2: Insert Bar Chart
Next, highlight the cell range A1:C7, then click the Insert tab long the top ribbon, then click the icon called Clustered Bar:
The following horizontal bar chart will appear:
Step 3: Convert Bar Chart to Combo Chart
Next, right click on any bar in the chart and then click Change Series Chart Type:
In the new window that appears, click Combo from the list of charts on the left-hand side.
Then check the box under Secondary Axis next to the Predicted series name:
Once you click OK, the predicted and actual bars will be overlapping for each player in the chart:
Step 4: Adjust Bar Width
Lastly, we need to make the bars for the Actual values wider so that we are able to see both the Actual and Predicted values for each player at the same time.
To do so, double click on any of the orange bars in the chart.
In the Format Data Series panel that appears on the right side of the screen, adjust the value for the Gap Width to be 60%:
This will cause the orange bars to be wider so that both the Actual and Predicted points values for each player is visible:
Step 5: Customize the Chart (Optional)
Lastly, feel free to customize the colors and the appearance of the chart to make it easier to read:
Additional Resources
The following tutorials explain how to perform other common operations in Excel: