Home / Business & Management / Big Data & Analytics / How to Use Microsoft Excel for Data Analysis / Basic Dashboarding Using Pivot Tables and Pivot Charts
Basic Dashboarding Using Pivot Tables and Pivot Charts
Learn more about basic dashboarding using pivot tables and pivot charts.
Hello, welcome back. In this part, we’re going to create a dashboard using pivot tables, pivot charts, and slices. Pivot tables we already saw. Pivot charts and slices we’ll see in a minute. And all three of them together will help us in creating this dashboard. Back to Excel, we want to create a series. We had one pivot table. Now, we want to create a series of pivot tables next to each other that will represent different aspects of the data. If you remember our first attempt to create a dashboard, we had multiple charts based on different axes of the data– years, geographies, categories, and so on. So we’re going to do the same, but this time using pivot tables.
So instead of using this one, I will create a new pivot table, and go from there. So we’re back in Excel, and in the first attempt, we saw one way to create a pivot, which was from the table, clicking on Summarise with the pivot table. There are a few other entry points to create a pivot table. For example, I can be anywhere, and I can say Insert, and right there, the first item on the list is a pivot table. And the pivot table’s asking me what is the data? Where is the data that you want to base your pivot on? And I want to base the pivot on the table. I know the table is named table one.
Let’s actually change this name before we create a pivot, because I prefer things to be called in proper names instead of just default names. So let me show you that. I’ll go back to the Sales table. I’m here on this table. And remember there is a special area in the ribbon for the table. And the first thing here is the table name. So maybe we’ll call this Sales Data. So we click on the name, click Enter, and now the name is named Sales Table. So when we create our pivot, we will just say just that– sales data. Doesn’t have to be case sensitive. So default is to create it in existing worksheet.
I will switch to a new worksheet, because in my new worksheet, this is where I have my dashboard. I click OK, and again, I get something similar, just the beginning of a new pivot. By the way, one thing that I would like to do right away is go to the View area in the ribbon, and uncheck the grid lines. Notice that immediately, when you uncheck the grid lines, the things looks cleaner. And for something like a dashboard, we don’t really need these grids, which are helpful when you use a lot of formulas with reference to cells. OK. First, again, we’re going to start with revenue. Again, we want to forward the revenue to be currency.
So one of the first things we do is to properly format it. And we want to do a summary of country and year.
Now, I will click Year. Now, notice what happened. Something very weird happened when I clicked on the year. It didn’t happen before, because instead of clicking it, I was dragging it right to the right area. Remember the different areas in the pivot? But when I just checked, Excel actually was doing the default thing that it will do for checking a column. And the default behaviour for Excel in the pivot will be that, if the field that I was clicking on is numeric, it assumes that its right area is the values area, that you want to Summarise it. So I get a summary of year, which is obviously not what I wanted. It’s very easy to fix.
I can just take it from here and drag it to the right place, which is the columns. So now, I can actually drag it to begin with, like I did before, to the columns, or I can drag it after. There’s no harm. Just wanted to let you know that sometimes, the default behaviour is not the best one. So I have my first pivot, and I want to also add state. Now, I want to add state. But when I add state, it becomes pretty long. There’s a lot of states. So what I want to do– I want to start with everything collapsed. Remember there is a collapse and there is expand.
I want to collapse all the countries, so I can go to a country. Click on right click, open this menu, and then go from Expand, Collapse to Collapse Entire Field. So now everything is nice, neat. And it’s collapsed, and if I want to, I can click on the plus, and see more detail. Now, I want to introduce a new element, which is a pivot chart.
Again, if I click on the pivot, you’ll see that there is special areas in the ribbon open just for the pivot. If I go out of the pivot, they disappear, into the pivot, they appear. And there’s pivot table two, and below it, there’s two areas– analyse and design. We’ll use them quite extensively. In the analyse, there is something called Add a Pivot Chart. I click on it, and actually, it shows me the different types of chart that I can create. And now we have my chart, and I can just move it next to the actual pivot.
Now, this is similar to one of the charts we saw before, but the difference is that this is a pivot chart, and not a regular chart. A pivot chart is actually tightly connected to the content of the pivot. For example, if I click on the plus, and the plus actually expanded all the states in France, it also had the same effect on the chart. If I go, for example, here, and I select a subset of the countries, I say I want to see only Australia, Canada, and UK, it has the same effect on the chart.
So the chart shows exactly the same thing that the pivot chart– the same content– and it’s just a visual way to see the same data. I can use different types of charts. I have these buttons here to allow me to philtre directly from the chart, so I can decide to leave only the charts on the dashboard, and not the numbers, or I can choose to say to have both. I may want to leave some space just to drag it down. The chart is a floating object, I can move it around. So now, we have more space when I open the pivot. It doesn’t really overlap with the chart.
Now, I want to create another chart next to it, so I will do another Insert, Pivot Chart, based on sales data. And in this case, I would like to do it by product category and subcategory. Again, go to the revenue. Check the revenue, again, format the revenue.
Currency, no decimals. OK. And again, I want this to start in the collapse mode. And then, if the user wants to, the person who’s using this dashboard would like to, they can actually expand any part. From here, I can add a pivot chart. Again, a pivot chart, maybe like this. I have lots of different, again, different types of chart that I can select from. I wouldn’t spend too much time now on the visual aspect, but just want to show you one thing. And this is a new thing, this is something that you will see only on Excel 2016. Don’t look for it if you’re using an earlier version. You see this plus and minus here.
That can be used the same as the pluses here. So when I click on a plus, every one of the categories was expanded. And I see it now in the detail level of a subcategory. You see that there’s one subcategory which stands above all others, which is the road bikes. If I click on the minus, it again collapsed into the original level of categories. So I can manage it, I can drive it from the chart, or I can drive it from the pivot. I can leave the pivot here, or maybe I can decide to actually move all the charts to their own worksheet, and have a dashboard make of only pivot tables. Let’s do one more thing.
Let’s create another pivot, and this will be by customer. And so I will start. Go here, start another one, Insert, Pivot Table, based on sales data.
And this one I will do by customer age. And within customer age, the– oh, again, customer age was recognised as numerics, so I move it to the rows area, and I want to also use the customer bucket. If I put it like this, it would be wrong, because I always want the more aggregated one above, and the less aggregated below it. So I’m going to switch their positions.
Yes. And I will also going to collapse the field. And again, I will click on Revenue, and maybe click here, too. So we’re here. And notice, it looks OK. I still didn’t format it. I can format it, but we’ll do it next time. But notice something wrong here. The buckets are not coming in the right logical order. So the first one is OK. The second one is completely wrong, because it’s the one that should be in the end. And the reason is that the pivot sorts them in alphabetical order, and doesn’t know that there’s a logical order that those elements should be sorted by.
We’ll be handling, or we’ll fix this in the next part, which is the continuation of building the dashboard. We built our basic dashboard. Again, we might want to add a chart to this one also. So we have three, with three different charts. We’ll manage the area of the sort next time, and also, we’ll add slices. So see you next time.
In the previous step, you experimented with pivot tables by summarising data from your Excel table, and you’ve learned that pivot tables are great for summarising large quantities of data.
In this step, we’ll introduce you to dashboards. We’ll also experiment with creating pivot charts.
Dashboards
We’ve explored various ways in which data can be presented in Excel. Another useful tool for displaying data in dashboards.
Dashboard reports provide you with a broad overview of your data and allow you to make informed decisions based on this overview of your dataset.
Pivot Chart
A pivot chart is the visual representation of a pivot table in Excel and is connected to the pivot table.
You can display multiple charts using many different chart types when creating pivot charts.
Before you proceed to the following step, take a moment to reflect and recap.
In case you missed it, check out the overview of Excel tables. Also, take a few minutes to browse through these resources:
In the next section, we’ll continue our exploration of dashboards. We will learn how to manage the sort function in pivot charts to maintain the logical order of data and we’ll add slicers to our pivot charts.
Join The Discussion
When we think back to the beginning of this course, we note that standard charts and pivot charts are not that different from each other. Can you think of a scenario where you’d use standard charts instead of pivot charts or vice versa?
This article is from the online course:
How to Use Microsoft Excel for Data Analysis
Created by
This article is from the free online
How to Use Microsoft Excel for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
