PivotTables - Microsoft Excel Tutorial

- [Instructor] A PivotTable is a quick and easy way to summarize information in Excel, which looks like pure magic to those who aren't familiar with it, but it's also an essential tool for any accountant. I'll walk you through this amazing tool, I'll show you how PivotTables can quickly get you the data you need, as well as tell you a few of their limitations. I'm working with a list of 10,000 sales and it includes columns for the date, salesperson and amount. You can find this data in the exercise file. Let's say you want a monthly summary for each salesperson, you could do that with COUNTIFs or SUMIFs formulas, but the quickest and easiest way is using a PivotTable. First, highlight all columns with data, then go to the Insert ribbon and select PivotTable, make sure your PivotTable will populate on a new worksheet, then click OK. This will instantly take you to a new sheet. On the right side of the screen, you'll see a PivotTable Fields section listing the columns you selected and four boxes, Filters, Columns, Rows, and Values. Go to where it says Salesperson in the top box and click and drag that down to the Rows box. You'll see in the worksheet section the names of the sales people appear as a list, then click and drag the word Date to the Columns section. A great thing about PivotTables is that Excel will automatically summarize them by month, here in the Columns section, it's added Months as a field, over in the workbook, it's added the months as the columns. Now drag the word Amount to the Values box, Excel defaults to making this sum of amount, and if you look at the worksheet, the table has automatically been built and adds up the sales by salesperson. This is a bit hard to read, so let's highlight these numbers, go to the Home ribbon, select accounting format and then remove two decimal places. If you want to know the number of sales click anywhere on the PivotTable to bring up the PivotTable Fields box, then go to the Values section, click on the dropdown where it says Sum of Amount and choose Value Field Settings. In this dialogue box, instead of Sum, click on Count. While PivotTables are a great way to summarize your data in just a few seconds, there are a few drawbacks of PivotTables. First, they don't look that great. You've got this weird blank row here and this weird less than 1/1/21 thing up here, and the style isn't the prettiest, formatting PivotTables is difficult and time consuming. Second, linking formulas to PivotTables doesn't work well, you never want your spreadsheet to calculate something based on the results of a PivotTable. The PivotTables should be the final result not an intermediate step. If you're summarizing information as an intermediate step, use COUNTIFs or SUMIFs instead. Finally, PivotTables don't automatically update, so if you change this back to Sum of Amount, then on the first worksheet, change this sale for Ellie in April to a billion dollars, then look back at your PivotTable, you'll see the amount hasn't been updated. In the real world, the fact that your PivotTable is showing outdated information may not be so obvious. If you update your underlying data, it's very important to remember to update your PivotTable by right-clicking on it and choosing Refresh and now you see that billion-dollar sale is showing up. PivotTables may not win any beauty pageants, but they vastly improve your workload when you need to sort and summarize data with ease. I encourage you to experiment with making some PivotTables of your own so you can get a better understanding of this incredibly useful tool.