PivotTables offer great flexibility and powerful analytical features. In this video, learn how to introduce PivotTables.
- [Narrator] Before I get into the details of how I'd like to demonstrate a few of the ways that Pivot Tables help you analyze your worksheet data. As my sample file I will use 0101 introduce. You can find that file in the chapter one folder of your exercise files collection. The data that I'm working with summarizes sales for an olive oil company. And you can see the values here for 2018 and 2019 and they're broken down by extra virgin olive oil also called EVO and regular. So you can see the total sales for those categories for each year and also the grand total. You also see a grand total for the two categories themselves. One of the great things about dates in Excel is that Excel understands them as a hierarchy. So for example here, we see that we have 2018 If I click that control then I get the sales broken down by quarter and the next level below that is by month. See, there you can see monthly sales for January, So that's as far down as we can go. If you want to hide the detail then you can click the hide detail button next to the level Another way to work with data in your Pivot Table then you see that we have the product and then regular broken down by the same years. And you can see total sales for both of them. So let's say for example that I only wanted to see values for 2018 I can go to the real labels cell, click the down arrow, and then I can select my years. So I have 2018, 2019, and 2020. So if I were to clear select all and then click 2018 so I only see those cells. Then you can see them in the background and I'll close the filter window here. If I want to break it down by quarter I still can 'cause that hierarchy is remembered so I'll click the show detail button for both quarter number two and the year. You can change the appearance of Pivot Tables. So for example with the Pivot Table selected I can go to the design contextual tab and from the in ribbon gallery I can select another style to apply. So for example I can go down and click a darker blue with white text for some of my labels. There are many other ways that you can work with your Pivot Table but I think you see the power built in. Actions that take just a few seconds in Pivot Tables
Note: This course was recorded in Office 365 for Mac. However, much of the content will be useful to those working with Office for Mac 2019 and 2016.
- Formatting data for use in a PivotTable
- Creating a new PivotTable
- Connecting to external data sources
- Summarizing totals and other data fields
- Creating calculated fields
- Summarizing field data
- Sorting and filtering PivotTables
- Working with Excel slicers
- Formatting PivotTables styles and layouts
- Applying conditional formats
- Summarizing data visually using a PivotChart