This is not just a catchy title.
It actually happens.
Read on to find out the secret.
Requires Excel 2013 Professional Edition.
Estimated reading time 4 min
Practice 5 min
Contents
Quick Answer
If you already know Power View, just drag a field in Vertical or Horizontal Multiples to draw the base pie chart n number of times. n is the number of unique items in the field.
Download Sample and Follow Along
The raw data is simple. Each row represents a transaction where a customer invested some money in some type of instrument.
The date and amount is available. The type of investment could be Bank Deposit, Real Estate or Equity.
Why do I need twelve pie charts
Good question. We want one pie chart for each month – to see how customers invested their money.
Create a Power View
This time we are NOT going to use a Pivot Table. Why? Because Excel 2013 (Pro Plus) gives you a new way of analyzing data called Power View. Before you say “I don’t need this new thing”, have a look.
Open the file. Click inside the data and choose Insert tab – Power View.
If you do have Excel 2013 professional and you still do not see Power View tab, read this article for further instructions.
A new sheet will be added. This sheet is just a white rectangle with a field list on the right side. All the data will be displayed on this Power View sheet. We only want few columns here.
Remove the unwanted fields – keep only Investment and Amount fields. Now the data is shown like a pivot table with grand totals for each investment.
Click inside it and choose Other Charts – Pie Chart from the Design tab.
Now we have a pie chart. This is the first pie chart. It shows what we want.
Yes this could have been done using Pivot Table and Pivot chart as well. But wait for few more seconds.
See monthly distribution of investments
Now carefully drag the Month column into the Vertical Multiples area in the field list.
Miracle – 12 pie charts are created instantly. Resize the Pie charts boundary so that it occupies all available space. Depending upon your screen size, a scroll bar may appear.
This is how you create 12 pie charts in 1 second!
Try to interpret the pie charts. Look at the color coding and think about how customers behaved. Think about what happened.
Try doing this with your real business data and then you can not only see what happened, you will also have many questions in your mind about WHY it happened that way. Once you find the answers (Excel may not have these answers), you can manage your work more effectively.
Questions, Questions…
I know you have many thoughts in your mind…
- Why do I need Excel 2013 professional? Could I do this in older versions?
Read this article for details. - What is the benefit over Pivot Table or simple charting? Read this article.
- How will I show this in PowerPoint? How will I send this report to people who DO NOT have this version of Excel? Read this article for details
***
