Download the free workbook 👉 HERE and follow along.
Why Use Overlapping Clustered Column Charts in Excel?
Overlapping column charts or “bar in bar charts” are ideal for comparing data and showing trends. These progress charts with clustered columns make data presentations clear and professional, perfect for reports and presentations. Here is an example:
For another creative way to track progress, check out how to create a progress chart using a donut chart in Excel.
How to Create a Clustered Column Progress Chart
Step 1: Setting Up the Data
First, arrange your data in Excel. This will be the base for our chart.
Step 2: Creating the Clustered Column Chart
- Select your data.
- Go to Insert > Charts > 2D Clustered Column.
- This will generate a basic column chart.
Step 3: Adding Background Bars
To add the background bars:
- Insert two new data series named “Full 1” and “Full 2”.
- Drag these new data into your chart.
- Alternative: Select the new data (e.g. cells D3 thru E12), click Copy, then select the chart, and click Paste.
- Here is the updated chart. The background bars are tiny, but we’ll fix that soon.
Featured Course
Professional Excel Charts for Business Reports
Step 4: Placing Data on the Secondary Axis
- Double-click on any data series in the chart.
- In the Format Data Series pane, use the dropdown menu to select the series “Full 1”.
- Set the “Plot Series On” to Secondary Axis.
- Repeat the steps for the series “Full 2”.
Step 5: Overlap Chart
Excel charts place the Secondary Axis data in front of the Primary Axis data. But, for our chart, we need the 100% bars to be in the background. Here’s how to swap the axes to create a bar in bar chart:
- Double-click any data series in the chart to open the Format Data Series pane.
- In the dropdown menu under Series Options, select the series “Yes” (whichever is currently on the Primary Axis).
- Change the Plot Series On setting to Secondary Axis.
- Repeat for the “No” Series.
These steps will place the “Full 1” and “Full 2” series on the Primary Axis and the “Yes/No” series on the Secondary Axis, positioning the 100% representation bars in the background.
Step 6: Adjusting the Axis Limits
To ensure both the Primary and Secondary Axes display the same range, follow these steps:
Adjusting the Primary Axis:
- Click on the numbers along the Primary Axis (left side of the chart).
- Right-click and select Format Axis.
- In the Format Axis pane, set the Minimum bound to 0 and the Maximum bound to 1 (which corresponds to 100%).
- We don’t need to display the percentage labels next to the axis:
- In the Format Axis pane, under Axis Options, set the Label Position to None to hide these labels.
Adjusting the Secondary Axis:
- Click on the numbers along the Secondary Axis (right side of the chart).
- Right-click and select Format Axis.
- In the Format Axis pane, set the Minimum bound to 0 and the Maximum bound to 100.
Removing Unnecessary Elements
- Delete the gridlines by clicking on them and pressing Delete.
Step 7: Setting Chart Colors
Change the colors of the columns to match your desired design. For our example we used the following fill colors:
- Yes: Hex #203864
- No: Hex #6BC0D2
- Full 1 & 2: Hex #EDECE7
Featured Course
Visually Effective Excel Dashboards
Step 8: Narrowing the Gap Between Bars
- Double-click the ‘Yes’ or ‘No’ bar series.
- Set the Gap Width to 100%.
Step 9: Adding Data Labels
- Right-click a “Yes” bar.
- Select Add Data Labels.
- Repeat for the “No” bars
- Adjust the appearance of the labels to make the chart clearer:
- Increase the font size
- Activate the Bold feature
- Set the font color to match the associated bar color
Step 10: Customizing the Legend and Title
To fix slanted X-Axis titles in your Excel chart:
- Select the titles and reduce the font size.
- Alternatively, increase the width of the chart.
- Remove unnecessary legend entries like “Full 1” and “Full 2”.
- Click on the default chart title and press Delete. This removes the title and frees up space.
- Click on an empty space within the chart to select the Plot Area. Then, use the circular handle at the top center to pull down and create space for the titles.
- Add a New Title Using a Text Box:
- Go to Insert > Text Box.
- Click and drag to create a text box above your chart.
- Type in your desired title, such as “Employee Satisfaction Survey Results”.
- Format the text using the font options in the Home tab. You can change the font size, color, and make it bold for emphasis.
- Add Rounded Corners:
- Right click on the chart area and select Format Chart Area
- Check the box for “Rounded corners”
Download the Workbook
Enhance your learning experience by downloading our chart template. Practice the techniques discussed in real-time and master progress clustered column charts in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
