Visualizing pivot tables

- [Instructor] A picture is worth thousands of lines of data, and this is where visualizing your pivot data can be impactful. This will allow you to explore your data visually versus just reading the numbers. I think it's important that you do use visuals to show the different stories of the data, but again, I like to have both the numbers, as well as the visual. Let's take our existing pivots and we'll create these different visualizations of the data. And we found after speaking with our team that they also found it impactful to look at the single year over year performance per product, but they noticed a few things that we need to adjust for our upcoming meeting. For example, they want to look at the volume in price versus the volume in quantity. So we'll go ahead and copy this sheet because it's really easy to make this change in a pivot. I'll go ahead and remove my sum of quantity. I'll go grab the TotalLine and drag that to values. Because they want to look historically at the last three years, I'll go ahead and filter out 2021. I'll do filter and then I'll hide those selected items. All right, I'll go ahead and sort by my grand total, that'll move my highest volume products to the top. And because they don't need to see the grand total for the display, I'll go ahead and right click and remove that grand total. Also for the purposes of our meeting, they really only want to see a handful of the top items. So let me sort it again. I want to say the highest in 2020. Perfect, so any volume that was over 50,000 makes it into our final visualization, okay? So there's my checkpoint right there. I'm going to go ahead and highlight these values. I'll right click and filter, and instead of hiding them I'm going to keep only these items. One of the last little changes we need to make is we don't need to filter the years down to the quarters of the month. So I'll go ahead and drag out quarters and drag out months. Okay, now I have everything set except I need to show that value as a percentage of the row total. Now that I've made my changes, I'm ready to go ahead and start visualizing this data. The very first thing I'm going to do is add columns. I'm going to add columns using Sparklines. I'll go to Insert, I'll go to Sparklines and I'll choose columns. My data range is this line, it's going to show me columns based on the percentages. I'll go ahead and click okay, and immediately say my last two years of performance were better than my 2018 performance. All right, so I'll go ahead and highlight this and bring it on down. Perfect, now I'm visualizing based on the size of the column and that might draw my eye to look at the actual numbers that will then take me to look at the actual product. There's some more we can do with these Sparklines to make them stand out. I'll go to the SparkLine up top. I'll go to my marker color, I'll choose my high point, I want it to be green, and then I want my low point to be that deeper red. Now I can see my high points, my low points and I can see where everything else falls in between. It's just a quick easy way to visualize all that line of data without having to have an individual chart for every product. Okay, let's go ahead and name this product year over year and then we want to go ahead and save our work, I'm not going to lose any of that valuable work. Okay, looking at our Sparklines, we decide maybe a line might be more appropriate than the columns. So let's go to SparkLine let's choose line, after review, we look at it, we decide maybe column would be better. We go ahead and adjust it back. We can learn a lot more about Sparklines inside the library. So now what we want to do is actually start looking and exploring an actual visual. Let's build a comprehensive bar chart. Let's go up and close our field list. Let's go to our analyze tab. We're really structuring our pivot. So I'm going to go ahead and double click and bring my ribbon back so that not an a must. I'll go ahead and choose pivot chart. I'll go ahead and choose bar. And for this particular scenario, I don't want staggered bars. I actually want stacked 100%. So let me go ahead and choose this 100% stacked bar, that lets me see the percentages all the way out to 100% which is appropriate for this particular data set. I'll go ahead and click okay. I think it's important to note with a bar chart that's 100% stacked or even a column chart that's 100% stacked. I don't have to have that percentage of row set. It would do that automatically for me inside my chart. Okay, I'm going to go ahead and just size a little bit. I'm going to zoom out so I can see. I'll drag it down. Okay, perfect. Starting to see some really valuable information here. So now that I have my pivot set for my percentages which is easier to read, I have the line by line visuals and I have a comprehensive visual of all the products that remained in the set. It might be nice to have a slicer, that way my decision makers can at different aspects of this data, I'll click in my pivot. I'll go to my analyze tab and I'll insert a slicer. I want a slice on that invoice date, click okay and it gives me the months, which is perfect. Okay, so now if I want to look year over year for January, I can click January and I can say all of the data update. If I want to look at February or March or even compare the first quarter, I can Control + Select those filters, perfect. This can be used as a precursor to building dashboards. That way, you can ultimately get on the same page with your decision makers about the ways they might want to explore the data. I think we can all agree that using Sparklines to show a line by line comparison and a visual definitely can help support the analysis of the data. And then looking at your slicers and filters, definitely helpful to break your data down when you have large datasets. No matter why you visualize or how you visualize or what you visualize, ultimately, each PivotTable and visual you create should be defined to tell a particular story about that data.