From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Modifying pivots for readability

From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Modifying pivots for readability

“

- [Instructor] Pivots are critical storytellers. When our decision-makers are asking for pivots or numbers, they're really wanting to be able to look at the numbers and make some decisions and gain understanding. The issue is that numbers, and specifically lots of them and large ones, like what we have in our dataset here, can be challenging for someone reading a pivot to get the information they need and get it quickly. Let's work through the three most common scenarios, which are values as a percentage of the grand total, percentage of the row total and a percentage of the column total. The very first thing I want to do is go ahead and name this sheet. I'll double-click that and call it Original Data Pivot. Then I'll control drag and drop a copy, cause I don't want to rebuild it each time, and I'll go ahead and name this one Percentage Values. I'm not exactly sure if I want all three or if I just want one of them. So Percentage Value's a good name for now. Okay, the very first thing I want to do is look at, what are each one of these by year, as compared to the grand total? Meaning, 1690 of this first product, what does that 1690 represent as a percentage of the total of all of the products in all years? I can accomplish this by changing this value to show as a percentage of the grand total. So what I'll do is I'll right-click, I'll choose show value as, and I'll choose percent of grand total. Automatically, I see one of the features kick in of a PivotTable, and that's the automatic sizing. My product names are long, and there's a really lengthy one in this list. So instead of always re-sizing, I'm going to size it this one time and I'm going to turn off the setting that does that particular column fit. I'll go to my PivotTable Analyze, I'll go to PivotTables, I'll choose options, and I'll uncheck the box for autofit column width on update. All right, I'll go ahead and click OK. Now, as I make other changes, it won't automatically size every single time. All right, so what is the percentage of grand total actually showing me? The very first thing I want to do is take a look at the very bottom of the pivot. Scroll to the bottom here. The grand total equals 100%, and all of these totals total up to be 100%, which you can see there in the auto calculate. That means that, for every year and every product individually, it is showing me how much of it represents the actual percent towards the grand total. So let me go back up to the top here and let me sort my grand total by the highest one. So I'll go ahead and sort and I'll sort largest to smallest. So I can see this first product is actually 2.39% of the grand total, and then I can see its breakdown of each year. So in 2018, the products ordered makes up 0.71% of the grand total for all years. So that's what the percent of the grand total gives me. Now, what if I want to look year over year? How did all of the products compare to each other year over year? So what I'll do is I'll go ahead and right-click, I'll go to show value as and I'll do a percentage of the column total. Okay, so in year 2018, this product represented 2.42% of the 2018 values. Let's go back down to the bottom of the pivot, and notice that it equals 100%, and each one of these columns actually equals up to 100%. So if I'm doing an annual comparison of each product against each product, and I want to look that way, well, I do a percentage of the column total. All right, let's go back up to the top of our pivot. Every year, a product may have had its best year ever. We don't know that unless we look. So we're going to actually adjust the value to show as a value of the row total. Now this one's easy enough because you can see the row total equals up to 100%, and all the values for each year equals up to 100%. And now I can see for this product that 2019 was actually its best year. That's what the percentage of the row total will show me. It'll let me look at a single product and its year-over-year performance. People will ask me, "Well, which one do you use?" And I tell them, "It depends on the story I'm trying to tell." If I'm looking for how something is done in the collective over all time for all products, I might use the percentage of grand total. If I want to look at an annual look at each product and its performance against other products, I'll use the percentage of column total. And if I want to look at a single product and it's year-over-year performance against itself, I'll use the percentage of the row total. Remember, this is data analytics, not just data cleaning. When you're preparing pivots, remember, they're going to be explored for decision-making and further analysis, and using value percentages definitely helps make them more readable.

Contents