# Use Advances vs. Declines chart to understand change in values

Share

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.

## 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:

### 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%”)

### Step 3: Calculate % break-ups for the chart

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

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:

### 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:

### 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).

And you are done!

## 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.

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.

### 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

Excel School made me great at work.
5/5

– Brenda

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.

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.

### CP05: Interview with MrExcel – Bill Jelen (on his incredible work ethic)

Bill Jelen is one of my most favorite people on earth. That is why I wanted to have him as my first guest when I restarted the podcast. Even though I recorded this few weeks ago, only now I got around to publishing it. Please enjoy the conversation with Bill.

## Related Tips

Charts and Graphs

### Make an Impressive Interactive Map Chart in Excel

Charts and Graphs

### How to Create a Dynamic Excel Dashboard in Just 5 Steps

Charts and Graphs

### How to create a fully interactive Project Dashboard with Excel – Tutorial

Charts and Graphs

### 9 Box grid for talent mapping – HR for Excel – Template & Explanation

Charts and Graphs

### 6 Must Know Line Chart variations for Data Analysis

Charts and Graphs

### 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:

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. [...] Use Advances vs. Declines chart to understand change in values [...]

9. 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

10. outlr says:

I really felt pleasure after reading this.

11. TellTheFolks says:

Wow you just made excel charting so simple.

Thanks.
TellTheFolk

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

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

16. Himanshu says:

What do we take away and interpret from this chart?

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.