Let’s create a column chart within a column chart as shown below where the target is the background of the actual measure:
Let’s get started!
Check our previous article to display a target line in a column chart, or multiple target lines in a column chart.
Step 01: The data
Consider a sample data as shown which is converted to a table (CTRL +T)
Step 02: Create chart
Select the entire data and (1) insert (2) a 2D column chart.
This creates a chart like below:
Our goal is to overlap these columns and adjust the column widths. For this,
i. Right-click on Measure (2) Format data series and move it to secondary axis (3) adjust the gap width to 300%
ii. Similarly, for the Target series, adjust the gap width to a 100%
This adjusts the charts accordingly:
Step 03: Format to get the column within column chart visually appealing
Let’s format the colors of the columns for a better visual appearance:
i. Right-click on the target series, go to Format Data Series and fill with a lighter color:
.
ii. Similarly, fill the measure column with a darker color fill:
iii. Format the gridlines to be lighter, add title and add borders:
With this, the chart looks something like this:
One issue to notice here is that, for the IT department the measure is 20 and target is 18 but the chart displays the measure to be less than the target. This is caused due to the presence of a secondary axis.
Address it by (1) right-clicking on the secondary axis and (2) deleting it:
That’s it! Our desired chart is ready!
Our YouTube video explaining these steps in detail:
If you have any feedback or suggestions, please post them in the comments below.
