Stacked Bar and Indicator Arrow Chart – Tutorial

Share

Facebook
Twitter
LinkedIn

Last week in the Chandoo.org Forums, I was asked could I reproduce the chart below in Excel

SBA01

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

SBA02

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

SBA04

In Excel 2010

Select the range C3:G4 and goto Insert Chart

Select Bar, Stacked Bar

sba21

Now with the Chart selected goto the Chart Tools, Design Tab

Click on the Switch Row/Column Tab

sba22

 

Excel All Versions

You should now have a chart like:

SBA05

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

SBA06

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

SBA07

Fill the Arrow with what ever color you want and drag the Handle down so that the arrow points down

SBA08

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)

SBA09

SBA10

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%

SBA12

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

SBA13

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

SBA14

Now click on the new Data Label and click on it again until the Handles change as shown below

SBA15

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

SBA16

Now repeat for each of the other Bars

SBA17

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

SBA18

I hope you enjoyed the above tutorial

 

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”

  1. Danny says:

    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.

Leave a Reply