From the course: Managing Data with Microsoft 365

Using PivotTables

From the course: Managing Data with Microsoft 365

Using PivotTables

“

- [Instructor] Have you ever been given a table of data and just thought, "Where do I even start to review this?" I know I have. Luckily, PivotTables can help you make sense of your data easily. In this lesson, I'll show you how PivotTables can simplify data and present it in an easy to read format. In the top left corner of office.com, I'll select the app launcher and open Excel. From here, I'll open the Test Sales Order spreadsheet. You can follow along with this document in your exercise files. We have lots of good data in here. Let's see what it looks like in a PivotTable. There are two PivotTables in this workbook, Pivot Table by Product and Pivot Table by Customer. I'll select the Pivot Table by Product tab near the bottom of the screen. This table not only sorts the data by products, but also provides an overview of who purchased each product. Since this table combines the customer and total spending per item data points, I can easily see that the total sales for AWC's Logo Cap was $277. And that Eastside Department Store purchased the most. Place your cursor inside the PivotTable by selecting cell B3. Then in the top ribbon select the PivotTable tab, then select Field List. In the PivotTable Fields panel you can see the two columns selected under rows, Product and Customer. This corresponds to the two columns in A and B on the left. There's also a column in Values, Sum of LineTotal, which corresponds to column C and aggregates customer product in the sum total. Now let's look at the PivotTable that is sorted by customer by selecting the Pivot Table by Customer tab at the bottom. This shows the breakdown of items purchased by customer. Here you see that Action Bicycle Specialists purchased 10 AWC Logo Caps and spent $38 on Bike Wash. In this table I can easily add and remove columns by selecting and deselecting the headers in the PivotTable Fields panel. I'll select UnitPrice which will add unit price to the PivotTable to show the individual price of each item. The PivotTable function in Excel is a great way to quickly sort and analyze any data points you've been given. Think about how you can introduce PivotTables into your projects and in doing so, save yourself some time.

Contents