Last week in the Chandoo.org Forums, I was asked could I reproduce the chart below in Excel
Which I did.
This post will describe how to tackle this chart step by step.
You can follow along using some sample data: Download Sample File
Data
To produce this chart we are going to use an Excel Stacked Bar chart with two series of data
The first Series will be for the Colored Bars
The second series is for the Arrow and the gap to the left of the arrow
The data required is shown in the above table
The Arrow Value is an input and is the value the Arrow will point to
The data is the values for the colored bars
The Arrow is two calculations that setup the Arrow, the 58 is the offset from zero to the left side of the arrow
The 4 is the width of the arrow. That is the arrow will point to 60 = 58 + 4 /2
The Cumulative Data is required for the Legend
Chart
In Excel 2016
Select the range C3:G4 and goto Insert Chart
Select Bar, Stacked Bar
In Excel 2010
Select the range C3:G4 and goto Insert Chart
Select Bar, Stacked Bar
Now with the Chart selected goto the Chart Tools, Design Tab
Click on the Switch Row/Column Tab
Excel All Versions
You should now have a chart like:
Bar 1 is the data and Bar 2 will become the arrow
We don’t need the Charts Title, Legend, Grid Lines or Axis, so select each and press Delete
Next we will add an arrow
The Arrow will be placed as a Fill in the Upper Orange Bar
Select a Blank Cell eg: I3
Then goto the Insert, Shapes Menu and select an Isosceles Triangle
Fill the Arrow with what ever color you want and drag the Handle down so that the arrow points down
To insert the arrow, select the Arrow and press Copy (Ctrl+C)
Now select the Chart and click on the Upper Orange Bar, click on it again until it is the only Bar with Handles Showing, the press Paste (Ctrl+V)
Now select the Upper Blue Bar and set its fill and outline to None
Right Click on any Bar and select Format Data Series
Set the Series Overlap to 100% and set the Gap Width to 0%
Now click on the chart, just above the Blue Bar
When the Resize Handles appear, drag them to resize the chart so that the gap between the Top and Bottom Bars is none
We can now add the labels
Select the Orange Bar and then Select it again until it is the only Bar with Handles
Right Click on it and Add Data Label
Now click on the new Data Label and click on it again until the Handles change as shown below
Now in the Formula Bar enter =Chandoo.org!$C$5 and apply
Right click on the Data Label and select Format Data Labels
Set the Label Position to Inside Base
Now repeat for each of the other Bars
and Finally add a Label to the arrow linking it to cell C2. You have to manually drag the Label to above the arrow.
Conclusion
Now that you know how to make a Bar Chart with Indicator Arrow, it should take you less than a minute to copy the bar chart, convert it to a Column chart and reformat it to a Column Chart with arrow as shown below
I hope you enjoyed the above tutorial
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.