Indzara

How to Create Bar within a Bar Chart in Excel?


There are multiple ways to display actual versus multiple target values, one of the simplest yet efficient ways is by using a bar within a bar chart in Excel.

Bar within a Bar final chart
Bar within a Bar final chart

Before we get started, if you want to follow the tutorial in a video, please click here. Now, let’s get started.

Consider the target versus actual for a particular metric across departments as shown:

Bar within a Bar sample data

Here is the overview of what we are going to see in this article.

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Now, let’s get back to creating a bar within a bar chart in Microsoft Excel.

Video Guide to create bar within a bar chart in Microsoft Excel

Step 01: Create the chart

Select all data and insert a 2D clustered bar chart.

Bar within a Bar insert 2D Bar

With this, a default bar chart gets inserted with two series: Target and Actual.

Bar within a Bar Target & actual

Step 02: Format the Actual series

To make this a bar within a bar chart, follow the steps here:

i. Click on the chart, and press CTRL+1 to open the format pane.

ii. From the format pane drop-down, choose the “Actual” series

Bar within a Bar format pane

iii. Under the series options to increase the gap width to 300% and move the series to the secondary axis as shown:

Bar within a Bar change Gap

iv. Now, under the fill & line, change the fill color as needed

Bar within a Bar fill and line

Step 03:Adjust the target series

i. Similar to the previous step, from the format pane drop-down, choose the “Target” series

ii. Under the series option, here reduce the gap width to ensure that the target series becomes broader.

Bar within a Bar gap width

iii. Go to fill & line and modify the fill color as required.

Bar within a Bar modify fill color

Step 04: Format the chart

Now, it is time for some common formatting of the chart for a visually appealing chart.

Before that, click on the secondary horizontal axis (on top) and press the delete key to remove this.

i. Add a suitable title to the chart, and format the same using the home ribbon formatting options.

Bar within a Bar chart title

ii.  Click on the chart, and use the “+” that appears on the top right to add axis titles. Format if need be using the home ribbon.

Bar within a Bar add axix titles

iii. Similarly, from the drop-down choose the “Actual” series and use “+” to add data labels.

Bar within a Bar add data labels

iv. Click on the legends and use the legend options in the format pane to position it at the top

Bar within a Bar Legend position

v. From the format pane drop-down, choose the “Horizontal (Value) Axis Major Gridlines”

Bar within a Bar Horizontal value axis major gridlines

And format the lines to make it less apparent.

Bar within a Bar format line color

vi. Click on chart, format the chart border as shown below:

Bar within a Bar format chart border

With these simple steps, your bar within a bar chart is ready!

Bar within a Bar final chart
Bar within a Bar final chart

Leave a Reply

Your email address will not be published. Required fields are marked *