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%”)
[Related: Introduction to Excel SUMIFS / COUNTIFS Formulas]
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:
- Right click on the chart
- Go to Select Data
- Now, select the series area
- Using up / down buttons adjust the order of series
- 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!
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:
- Visualizing tax changes over time using Excel
- Index Charts – to understand change over time
- Use Box plots to understand distribution of values
- Visualize monthly changes using Pivot Tables + Conditional formatting
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.
11 Responses
Ciao Hui,
Collecting Excel tricks under the title “Notable Excel Websites (Non-MVP) Edition” is a brilliant idea…
Thank you in the name of all The FrankensTeam.
On our site there is a box with a picture and text highlighting:
This is a no-MVP site
we think ourselves “bad boys” a bit 🙂
For those who would like to know why our site is a no-MVP site, enough to click on the link:
http://goo.gl/lxDszY
Thank you again!
Thanks a lot
I really enjoyed this (newsletter). I must admit that I rarely read an Excel newsletter (and I subscribe to quite a few) all the way though, but this grabbed my attention and before I realized it, I was engrossed in it. I must also admit that most of this I don’t understand, yet. But, it excites me when I do learn something new in Excel. I can’t wait to see how much of this I can implement into my (constantly-evolving) ‘House Budget’ & ‘Family Medical’ worksheets that I have developed over the past few years! I sure hope to see more of these type of newsletters in the future! Thanks!
Thanks for doing this Hui! I appreciate being included.
I like Tom’s tip a lot. I posted about a tool I wrote to automate this at http://yoursumbuddy.com/tables-edit-query-dialog/
EXCELLENT !
Hui, This post is Superb! More over I have always been a fan of Roberto’s work and have learnt a lot from him.
Here are some of my recent contributions
1. Customising markers in a chart – http://www.goodly.co.in/customize-markers-in-a-chart/
2. Charting Hacks to work faster – http://www.goodly.co.in/5-charting-hacks-to-help-you-work-faster/
3. 7 Date formulas to make life easy – http://www.goodly.co.in/date-formulas-in-excel/
4. Customised scrollbar using VBA – http://www.goodly.co.in/customized-scroll-bar-in-excel/
5. Adding Direct Legends – http://www.goodly.co.in/customized-scroll-bar-in-excel/
Hope everyone enjoys!
I like the Excel Ninja Menus.
1. Select a cell or range then move till the 4-way cross appears. Right-Click and drag the selection to another place in the worksheet then, like a ninja, a menu full of skills and throwing stars pops up allowing me to do all kinds of awesomeness.
2. When you click the fill box on a Date and right click and drag it down, a lot of amazing Date options pop up.
I also brand my Excel to remind myself that I’m awesome. In my personal macro workbook I place the following code.
Private Sub Workbook_Open()
Application.Caption = “SuperKrishna’s Awesomeness”
End Sub
My favorite tip goes along with #17. If you try to copy subtotaled data (and in earlier Excel versions filtered data),when you paste it all the data displays instead of just the summarized data.
To get around this, select your summarized data, click on Find and Select tab and then select Go to Special. Click Visible cells Only and click OK. Now paste and you will see that only the summarized data has been copied.
You can also go CTRL+G and then click the Special icon at the bottom of the dialog box.
What a great idea, Chandoo! I’d love to be included in your next edition:) Perhaps a VBA exclusive version?
@Ryan
I will review this concept about 6 months out from the original post and be sure to keep your site in mind
Hui…
That sounds great, Hui:) I just realized I gave credit to Chandoo for the idea and I should have attributed it to you.
Sorry about that!