From the course: Managing Data with Microsoft 365

Creating your first PivotTable

From the course: Managing Data with Microsoft 365

Creating your first PivotTable

“

- [Instructor] Filters provide lots of benefits, especially when working with data. In this lesson, I'll show you how to build PivotTables, select fields, and use filters in your own data in Excel to inform your business decisions. 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. This spreadsheet has a dataset, Sales Orders and their details. Let's say that I need the data by product and then by customer, so that I can gain insight into which products are doing well, and which customers are purchasing them. To do that, I'll create a PivotTable, which will allow me to see the link between the products and customers. First, I'll select cell A1, the first cell in my data set. Then I'll select the Insert tab in the top ribbon, and select PivotTable. The PivotTable drop down appears, and I will select From Table/Range to get the data from this sheet. The Insert PivotTable pane opens on the right. In Source, I can see that Excel picked the order detail sheet and selected all the values in my table. In the PivotTable pane, I'll use Create your own PivotTable and insert it on a new sheet. This way, I am able to customize the PivotTable to my needs and it will be separated from my current data. This opens a new sheet with the PivotTable on the left and the PivotTable fields panel on the right. In that panel, I can see there are two columns, one for the headers from my data table, and then one with Filter, Rows, Columns, and Values. I'll choose the data points I need. Products sorted by largest customer orders. First I'll select Product which populates column A with all the products sold. Next, since I want to see each customer that purchased those products, I'll add Customer to the Filter section and Line Total to the Sum Values section. This aggregates all the line totals for each customer. Let's filter this by Channel Outlet to see what they purchased. I'll select the dropdown next to Customer in cell B2, deselect Select All and select Channel Outlet. I can also add Order Quantity to Rows so that I can clearly see the number of units this customer purchased. Now it's your turn. Take a look at your organization's spreadsheets and create useful PivotTables in Excel. You'll have your data in an easy to review format. Try them this week.

Contents