Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Use Advances vs. Declines chart to understand change in values

Posted on February 21st, 2013 in Charts and Graphs - 18 comments

Lets say you are responsible for sales of 100s of products (which belong to handful of categories). You are looking at sales of each product in last month & this month. And you want to understand whether sales are improving or declining by category. How would you do it?

Turns out, this is not a difficult problem. In fact, this question is asked every day & answered using Advances vs. Declines chart.

You may have seen this chart in financial newspapers or websites. Shown below, Advances vs. Declines chart tells us how many items have advanced & how many have declined.

Advances vs. Declines chart - Creating it using Excel

When should you use Advances vs. Declines chart?

As you can see, advances vs. declines chart does not give low level details about actual movement of values. Instead, it gives you a sense of what is going on. Use it in below situations:

  • To get a feel of how values have changed over time.
  • When you are dealing with data that constantly changes (sales, number of customers, defects etc.)

Create Advances vs. Declines chart in Excel

You can easily create this chart in Excel from raw data. Just follow below tutorial.

Step 1: Get the data & arrange it

You need at least 4 columns of data – item, category, previous value, current value

Once we have these, calculate % change in 5th column. Arrange data like below:

Data for advances vs. declines chart

Step 2: Calculate Category-wise summaries

First list all unique categories in a column. Then using COUNTIFS formula, calculate the number of products declining & advancing.

The formula to count number of products going down by more than 10% is,

=COUNTIFS(Sales[category], Category name, Sales[% change], “<10%”)

[Related: Introduction to Excel SUMIFS / COUNTIFS Formulas]

Using COUNTIFS formula to calculate number of declines & advances

Step 3: Calculate % break-ups for the chart

Once all the numbers are calculated, you can easily calculate the % split.

Calculating Declines & Advances in percentage

NOTE: Make sure you negate the % values for declines. This will ensure that our chart shows stacked bars on both sides of axis.

Step 4: Create a stacked bar chart from this data

Once all the numbers are in place, just select them and create a stacked bar chart. Your output should look like below:

Stacked bar chart from advances and declines percentage data

Step 5: Adjust chart series order if needed

You may notice that, our stacked chart bars are not in correct order. Excel would have plotted <10% and >10% series before <0% and >0% series. To fix this:

  1. Right click on the chart
  2. Go to Select Data
  3. Now, select the series area
  4. Using up / down buttons adjust the order of series
  5. Done!

See this demo to understand:

Adjusting chart series order - advances vs. declines chart using Excel

Step 6: Adjust the colors & format the chart

Unleash your creativity and format the chart as you see fit. Make sure you add legend (otherwise the chart becomes very difficult to read).

Advances vs. Declines chart - Completed

And you are done!

Download Advances vs. Declines chart template

Click here to download the chart template. Examine the formulas & chart settings to understand this better.

Do you use Advances vs. Declines chart?

I use variations of this chart often in my dashboards & reports. These charts are very concise and present a lot of information about distribution of changes.

What about you? Do you use advances vs. declines charts? How do you create them? Share your experiences & techniques using comments.

Looking to advance your charting knowledge?

If you want to one up your Excel awesomeness quotient & create kick-ass charts, then you are at the right place. Check out below tutorials & see how deep the rabbit hole goes:

Recommended: If all these sound exciting, you will incredibly benefit from our Excel School program, where we teach advanced charting & data analysis skills. Click here to know more & join us.

Your email address is safe with us. Our policies

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

18 Responses to “Use Advances vs. Declines chart to understand change in values”

  1. This is a great learning tip for category professionals or sales people! Thank you!

  2. John says:

    How did you get the category axis labels all the way to the left?
     
    I can’t seem to move them
     
    Thank you

    • Chandoo says:

      @John.. Welcome to Chandoo.org and thanks for your comments.

      To show labels at end, Right click on vertical axis, and set axis-labels position to LOW. This will move the labels to all the way left.

      • Rebecca says:

        Hello Chandoo,

        My question is: how could I add lables to the chart? I got the same chart as in Step 5 and I noticed there were no lables in it. How can I make my chart as beautiful as yours in Step 6. Thanks!

  3. Dave says:

    This is great. 
    Would there be a way of varying this so that instead of the centre being 0, it could be an average amount and the sides showing the over or under amount or % they are from the average score?  Thanks.

  4. prasha99 says:

    buen grafico !

  5. Hemant Rathi says:

    Dear Sir,
    I want in excel sheet say various loan repaynebt or various fd mature date should be highlight as their time come near month/week.
    could it possible.
    Regards
    Hemant

  6. David - Nairobi Kenya says:

    will join in later classes

  7. SomeintPhia says:

     
    Dear Chandoo,
    thank you very much for this nice example. I can adjust it very well for your categories.
    Happy Charting and greetings, SomeintPhia
     

  8. Gibran says:

    Chandoo: I Think that we have an error in this example, May be in the symbols.
    if you account cell by cell. you will realize that we don´t have the really result
    MExico

  9. outlr says:

    I really felt pleasure after reading this.

  10. TellTheFolks says:

    Wow you just made excel charting so simple.
     
    Thanks.
    TellTheFolk

  11. Anushe says:

    Hi Chandoo,
    I tried the same on my sheets and it didn’t happen, I’ve been racking my brains to figure out what went wrong. I identified the range names, but all the values were coming out to be 0, I also don’t know how you defined “Sales”, I named the entire data array as that and even then none of the values were coming!
     

  12. Sid says:

    Hi Chandoo,
    Thanks for this useful charting method. Please explain the way how the naming of the ranges is done in this sheet.
    Thanks.

  13. Sid says:

    Hi Chandoo,
    Thanks for this useful charting method. Please explain the way how the naming of the ranges is done in this sheet.
    Thanks.

  14. Sid says:

    Hi Chandoo,
    Thanks for this useful charting method. Please explain the way how the naming of the ranges is done in this sheet.
    Thanks.

  15. Himanshu says:

    What do we take away and interpret from this chart?

Leave a Reply