Stacked Bar and Indicator Arrow Chart – Tutorial

Posted on September 12th, 2016 in Charts and Graphs , Excel Howtos , Huis , Posts by Hui - 22 comments

Last week in the 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


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


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!$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.


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



Written by Hui...
Tags: , , , , , ,
Home: Main Page
? Doubt: Ask an Excel Question

22 Responses to “Stacked Bar and Indicator Arrow Chart – Tutorial”

  1. Colin says:

    Thanks Hui, this is very helpful - and interesting.

  2. EXCELent Bacon says:

    I opened this post thinking, "Hmmm, I wonder how he's going to do the indicator icon"... and I had a technique or two in mind. THIS was not one of them! I had no idea images could be pasted into chart data. SO COOL! I've been Excelling pretty hard for about ten years now and I've somehow missed this trick. Thank you!!

  3. indzara says:

    Smartly done, Hui. I can see many use cases for this type of visualization. Thanks for sharing. Best wishes.

  4. Utkarsh says:

    Great Idea! Useful in Dashboards.

  5. David says:


    Select the range C3:G4 and goto Insert Chart
    Select Bar, Stacked Bar
    When I follow your instructions I do not get anything close to what is displayed. I get each value in row C displayed by itself. It is like the stacked is not working.

    • Hui... says:


      I have migrated to primarily using Excel 2016 as my main Excel version.
      What you have found is a difference between Excel 2016 and an earlier Version, I will assume you are using Excel 2010

      Once you have got your chart as you described
      With the Chart selected goto the Chart Tools, Design Tab
      Click on the Switch Row/Column Tab

      You should now be able to continue on.

      I have added an extra section in the post for 2016 and 2010 versions of Excel



  6. MF says:

    Very nice!! 🙂

  7. Sethumadhavan says:


    I'm using Microsoft Office Excel 2007.

    Unable to do the below step.
    "Now in the Formula Bar enter!$C$5 and apply"
    Can you help me?


  8. Abdul Kader says:

    Greetings from Holly Jerusalem
    From you it seems easy
    Many Thanks

  9. Ashish Timania says:

    Dear sir, can it insert date in arrow and move it with same that is as day pass progress can indicate theoretical v/s actual.

  10. David says:

    Great - I finally get all to work, but the bar cart is about an inch wide and the same for the column chart. Distorted.

    Thanks for the lesson


    • Hui... says:

      You need to Select the Chart and then Select the Chart Area (The area that surrounds the Columns/Bars and is within the Whole Chart)
      Then drag the Side or Top handle to resize the chart
      The columns/Bars resize to suit

  11. Gabriel says:

    Please make a video for this great tip.

    • Hui... says:


      The issue with Video's is that there are sections here that are slightly different for different versions of Excel.
      If you follow through here step by step and read every instruction you will get the desired outcomes

  12. Yulia says:

    Thank you for the great lesson! Very valuable!
    Can you please also share the quick conversion to the column view? I tried to switch it to the Column Chart, but it didn't work. Thanks again!

    • Hui... says:


      Select the Bar Chart
      Right Click on the Bar and select Change Series Chart Type
      Select Column, Stacked Column
      Withe the whole Chart selected Change the Horizontal Size so that it is narrow and change the Height so it is taller
      On the worksheet Insert a Shape, Triangle
      Format the Triangle to Point left and Fill as required
      Copy the Triangle
      Select the Down arrow in the Chart and Paste
      Reformat the Data Labels

Leave a Reply