Use Advances vs. Declines chart to understand change in values

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

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!

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.

Last day for enrollments – Join our Power Pivot course & become awesome analyst

Last day for enrollments – Join our Power Pivot course & become awesome analyst:
Hi folks,
I have a quick announcement & an awesome Power Pivot technique to share with you. First the announcement.

Only few hours left to join our Power Pivot course…

As you may know, I have opened enrollments for our inaugural batch of Power Pivot course few days ago. The aim of this course is to make you awesome in Excel, Advanced Excel, Dashboards & Power Pivot.
We will be closing the doors of this program at midnight, today (11:59 PM, Pacific time, Friday, 15th of February).
If you want to join us, click here and enroll now.

How many people have joined the class?

At the time of writing this, we have 195 students enrolled in Power Pivot class. We are eager to share Power Pivot knowledge & techniques to as many more of you as possible. So go ahead and join us because you want to be awesome in Excel & Power Pivot.

5 Important things about the Power Pivot course

1. This course is self-paced
That means, you can access Power Pivot & Excel lessons from anywhere at any time. You can pause, repeat or skip lessons as per your needs.
2. You will get a course completion certificate
At the end of the course, you will get a e-Certificate showing that you have completed online course on Power Pivot. Use it to show off or claim reimbursement from your boss.
3. You need Excel 2010 or Excel 2013 Pro Plus
To enjoy our Power Pivot course, you need either Excel 2010 (any edition) or Excel 2013 Professional Plus or Office 365 Professional Plus Editions.
However, to enjoy Excel School alone, you just need Excel 2007 or above.
4. Guest lessons by Rob Collie & Ken Puls
2 of the finest & most awesome people in Power Pivot world have agreed to share their wisdom, wit & way of doing things with us. Rob’s topic is “Power Pivot Magic Show” and Ken’s topic is “Power Pivot for Accounting & Financial Analysis”. Mouth watering stuff…
5. Special Bonuses
I have struck a deal with Rob Collie’s publishers to bring you an e-book edition of DAX formulas for Power Pivot Analysts – the definitive guide on Power Pivot measures & calculated fields. You will be able to download your copy once you join the class (download link will be available after we discuss DAX formulas in the class)
Apart from this, there are more bonuses – special modules on Excel, SQL & Dashboards, cheat sheet on common DAX formulas and so much more…

When will be the next batch of Power Pivot course?

As this is the first time we are running a power pivot course, I am expecting to spend a lot of time fine tuning the lessons, answering student doubts & thinking up creative ways to use Power Pivot.
I am hoping to re-open Power Pivot class again in July this year. But I may delay it if needed.
So sign-up already.

How much time I’ve got? – Count down timer using Power Pivot

We are going to close the enrollment window at today midnight (11:59 PM, Pacific Time – Friday 15th February).
Since we are talking about Power Pivot here, I thought it would nice to make a Power Pivot table that tells how much time you have when you select your city (or a nearest one).

How is this made?
Using measures obviously.

1. First I downloaded city-wise closing times by using timeanddate.com site (here)
2. Then, I added this data to Power Pivot.
3. Next I created few measures:
   
   1. time now: for current time (in user’s computer),
   2. time left-internal: difference between city’s closing time and time now
   3. time left: formatted version that shows time left in “xx.x hours left” format.
   4. time left measure uses IF formula to show different text based on how much time is left.
4. Added a slicer for cities and bingo!

Download this example power pivot workbook
Click here to download the workbook.
OR see demo here (if you do not have Power Pivot yet).

Thank you…

Thank you so much for your generous support to our newest course. I am so glad to see so many enthusiastic professionals from all over world join us to learn Power Pivot. Thank you…
PS: Go ahead and join us today, because you want to be awesome.

Love letters to Chandoo.org

Love letters to Chandoo.org:
Hi folks,
Today is valentines day. So I thought I should share a few love letters I recently got.
Correction: These letters are not for me, but for our site – Chandoo.org
Addendum: Don’t write me off yet. In my college days I did get all of 17 love letters, mostly because campus postman mistook me (room number 79) for the six foot athletic hunk in room number 29.
While I may not get cat calls & suggestive remarks when I walk on the street, Chandoo.org, the site would totally be kissed and hugged by people of all sexes and ages (well 21 and above).
You see, our site is like Ryan Gosling of Excel websites (or Mila Kunis). Those of slightly older can imagine Chandoo.org as George Clooney or Marissa Tomei of the data analysis world.
How else can I explain the barrage of love letters I get for Chandoo.org?

Here is a sample

Sent by Paul Wyatt,

Hi Chandoo.
Many thanks for your patience and understanding. I have now successfully purchased your course and look forward to discovering Power Pivots and applying your teachings to my own employment. It will be very interesting to see what can be achieved by bringing Excel, VBA (downloaded but still yet to learn) and SQL together with Power Pivots.
I am very excited to be on this course. You really are a fantastic tutor and have made a real difference. You don’t offer value for money, you offer excellent and complete services for incredible prices. There is nothing else out there that is as immediately useable as your courses are.
Kindest regards,
Paul Wyatt

Read our love letters this valentines day

Of course, Paul is not the only one raving about Chandoo.org. There are more. So I created a love letters page. Here you can find some of the recent letters I got. I will update this page with more as we go.
Check out our love letters.

Love is not one way street

Of course, it would be cruel to take all this love and give nothing back. But you know I am not like that.
So here we go again.
I truly love you,
because you take time to learn

because you respond to me with comments

because you share your knowledge with me

because you correct me when I am wrong

because you always open my mails

because you tell others about me

because you smile when I joke (even if it is a geeky silly joke)

because you support me by buying what I sell

because you force me to be better at what I do

and above all,

because you are awesome!!!

Do you love Chandoo.org?

We love you too!!!
Go ahead and profess your love for Chandoo.org in these 4 ways:

1. Tell your colleagues / friends why you love us. Send them here.
2. Tweet your love
3. Like us in Facebook. Tell the world about your relationship with us.
4. Leave a comment and tell why you love us.

Go!
PS: The picture (of woman writing a love letter) is how I am imagining you. Gorgeous, happy, fun & awesome.

Shading above or below a line in Excel charts [tutorial]

Shading above or below a line in Excel charts [tutorial]:
When comparing 2 sets of data, one question we always ask is,

• How is first set of numbers different from second set?

A classic example of this is, lets say you are comparing productivity figures of your company with industry averages. Merely seeing both your series as lines (or columns etc.) is not going to tell you the full story. But if we can shade our productivity line in red or green when it is under or above industry average… now that would be awesome! Something like below:

The above chart tells us where we are lagging and where we are good. It will let us ask poking questions about the gap and find answers (may be removing coffee machine from 2nd floor last May was a bad idea!)
So how do we create such a chart?
PS: This chart and article is inspired from a question asked by arobbins & excellent solution provided by Hui here.

Creating a shaded line chart in Excel – step by step tutorial

1. Place your data in Excel

Lay out your data like this.

2. Add 3 extra columns – min, lower, upper

If you look at the chart closely, you will realize it is a collection of 4 sets of data. See this illustration to understand.

Write formulas to load values in to min, lower (green) & upper (red) series.

• Min is minimum of productivity and ind. average
• Lower (green) is difference between productivity and ind. average (or NA() if negative)
• Upper (red) is difference between ind. average and productivity (or NA() if negative)

3. Create a stacked area chart from this data

Select all the 4 series (productivity, min, lower & upper) and create a stacked area chart.
This is how it looks.

4. Format the productivity series as line

Right click on productivity series and using “Change series chart type” option, change it to line chart.

5. Make the min series transparent

Select min series and fill it with “No color”

6. Format lower & upper in green & red colors respectively

And you are done!

Optional: adjust series formatting, add grid lines etc.

As a bonus, you can add vertical grid lines (so that we can understand the red green changes easily) and format the horizontal axis. You can also move around the legend and remove the words “min” from it.
This will make the chart look really awesome.

Is this the only way to compare productivity with industry averages?

Although our shaded line chart is an excellent way to visualize differences between 2 series of data, I kept thinking if there are other ways to compare this.
After a bit of doodling & drawing inspiration from various charts I have seen earlier, here are 4 more options we can consider.

Option 1 – Productivity vs. variance wrt Ind. average

This chart shows the variance (industry average-productity) at bottom so that we can easily look at overall trend & understand how we fared with respect to industry.
To create this chart, you just have to calculate the variance in a separate column and create a column & line chart combination (column for variance & line for productivity). Once such a chart is ready, go to fill options for the column chart and check invert colors if negative option and set up green & red colors!

Option 2 – Productivity vs. better or worse indicators

This chart just shows whether productivity surpassed industry average or not in a boolean state (green for yes, red for no)
This chart is a combination of line & column chart with same principle as above (invert if negative option).

Option 2 (made using Excel 2010 Sparklines)

You can create this chart very easily with Excel 2010 sparklines. Line chart for productivity and win-loss chart for better or worse indicators.

Option 3 – Collapsed Productivity vs. variance wrt Ind. average

Since the color is already telling us whether variance is negative or positive, we can collapse both to same side of axis (thus saving some space & reducing redundant information).
To create this chart, we need two series of data – positive variance & negative variance as 2 sets of areas on the chart.

Option 4 – Collapsed Productivity vs. better or worse indicators

Well, this is same as option 2 but collapsed.

Download Example workbook

Click here to download the Excel workbook containing all these examples. You can also see detailed steps for making the shaded line chart in it.

How do you compare one series with another?

I must confess that I never made shaded line chart until today. For smaller data sets (<15 items), I usually compare by making column charts or thermo-meter charts. These are easy to make and easy to understand. For larger data sets, I try to make dynamic charts so that I can choose which series to include in comparison or make indexed charts.
Now that I learned how to set up shaded line charts, I will try them in my upcoming projects & consulting assignments to see how they fare.
What about you? Which types of charts do you use to compare one series with another? Please share your techniques & implementations using comments. I would love to learn more from you.

Compare often? Check out these charts

If you compare apples to apples (or to an occasional  bushel of oranges) for living, then check out these charting tutorials & techniques.
WARNING: After learning these techniques, Suddenly you will become incomparably awesome in your office.

• Comparing one product sales with another using Excel charts
• INDEX charts – compare uneven values over time
• Comparing world education rankings – case study
• Best charts to compare actual vs. target values
• Us vs. Them – Interactive comparison chart
• Dynamically comparing any 2 series:
  
  • Example 1 – Customer Service Dashboard
  • Example 2 – KPI Dashboard

Distinct Count & Blanks – Power Pivot Real Life Example

Distinct Count & Blanks – Power Pivot Real Life Example:
When it comes to analyzing business data, managers are always asking, “so how many distinct x each y is doing?”
And that sends us, data analysts & reporting professionals running from pillar to post figuring out the best way to do it.

• We can use variations of SUMPRODUCT, COUNTIFS etc, but the methods are not flexible..
• We can use VBA, but it would become slow as you add more data.
• We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x.
• We might as well shave our head with a shovel before manually counting values.

And that brings us to 2 distinctly simple solutions:

• Using Power Pivot & Excel 2010
• Using regular pivot tables in Excel 2013

Today, lets talk about these 2 approaches & see why they are so better than anything else for distinct count situations.

Distinct count problem faced by Joanne:

Joanne, one our readers sent me this email few days ago,
Here is something that I thought your readers might be interested in, as a real life business example. I always have the need for unique counts in my Excel sheets and was elated to find out that MS had built this function into 2013 pivot tables. I generally use SUMPRODUCT to get to my answer, but some of my files take hours to calculate with this formula. Since I am still waiting for my 2013 CD to arrive, I took advantage of the addin to 2010 to play around with this new technology. At first, I was very excited at the ease of use to get to a unique number. But then looking into the results a little more, I found out it was skewed. Here is what I mean.
This is my data, simplified a little and any confidential information has been taken out. The last two columns are “helper” columns for my pivot. They basically return the agreement number if the proposal type is a certain value. If it doesn’t find a match, it leaves it blank. Data is sorted only so its easier to see how the pivot tables arrive at the answers.

The blank is what messes it up. Doing a simple pivot with DistinctCount, the numbers are definitely wrong. Its counting the blank fields as one of the items, so everything increases by one. The Grand Total is correct, sort of, but its misleading. There are 4 unique MBP accounts, 3 with actual agreement numbers and one blank, but looking at the numbers in the column, it doesn’t add up to 4 visually.

So there was my challenge, to get a DistinctCount, but ignoring all the blank fields within the data set. Using a New Measure, I was able to create a formula for the pivot that ignored the blank cells. It’s a little complex, but what good Excel formula isn’t? Once that was created, the pivot worked and the grand totals are more accurate. I did try using the DistintCount-1, but it didn’t come to the same answer. When it did work and the total was 1, it showed zero, which is correct. But I like the clean look below of showing nothing, thus not tempting the user to click on the zero.
MBP formula: calculate(distinctcount(('Implemented'[MBP Accounts])), filter(Implemented,Not(isblank('Implemented'[MBP Accounts]))))

Distinct Count Problem & various solutions

I think Joanne’s measure for Distinct count is excellent. It shows how easily we can calculate anything we want using Power Pivot.
But do you know we can modify it and use various other solutions as well?
In the below video, I have explained 3 different solutions and how they fare.

1. Joanne’s original CALCULATE (… FILTER(…)) solution
2. Improved CALCULATE(…) solution
3. Using Excel 2013 regular pivot tables to calculate DISTINCT Counts

Watch the Distinct Count using Power Pivot & Pivot tables video [17 min]

Go ahead and watch this video. This is just a sample of what you get when you join our Power Pivot course.

Download Example workbooks:

Click below links to download example workbooks shown in this lesson:

• Excel 2010 version
• Excel 2013 version

Thank you Joanne

Thank you so much Joanne for sharing this example with us. It was fun learning from your example & exploring alternative solutions.
If you enjoyed this example, say thanks to Joanne.

Want to learn more? Join our Power Pivot Classes

If you would like to learn more about Power Pivot, then please consider joining my new course – Power Pivot online classes. The objective of this class is to make you a data analysis & dashboarding monster.
We are in enrollment stage now and we will be starting online classes from 18th of February. All classes will be pre-recorded so that you can watch them at your own time.
Click here to learn more & join us.