Contents
In the last lesson, we got acquainted with pivot tables in Excel, learned how to create them, and also use them to answer a variety of questions in relation to the data under study. Today we will continue our acquaintance with pivot tables – we will learn how to summarize data, use slicers and filters, as well as build pivot charts in Excel. So let’s go!
Rolling up data in Excel
One of the best features of pivot tables is the ability to quickly summarize and reorganize data, allowing you to view it from different angles. Pivot helps you answer a variety of questions and even experiment with your data to discover new trends and patterns.
In our example, we used a PivotTable to answer the question, “What is the total amount of each salesperson’s sales?” But now we would like to answer a new question: “What is the total amount of sales in each month?” We can do this by simply changing the field in the field Rows.
Changing strings
- Click on any field in the area Rows and drag it outside of this area. The field will disappear.
- Drag a new field from the field list to the area Rows. In our example, we will use the field Month.
- The pivot table will be adjusted for the new data. It now displays total sales by month.
Adding a column
So far, our pivot table has only displayed one column. To display multiple columns, you need to add the desired field to the area Колонны.
- Drag a field from the field list to the area Колонны. In our example, we will use the field Region.
- A few more columns will appear in the PivotTable. In our example, columns with sales amounts for each region appeared in the table.
filters
Sometimes it is necessary to focus on a particular segment of data. Filters can be used to narrow down the amount of data displayed in an Excel PivotTable, allowing you to see only the information you need.
Adding a filter
In the following example, we’ll apply a filter to salespeople to see how they affect the total amount of sales.
- Drag a field from the field list to the area filters. In this example, we will use the field Seller.
- The filter will appear above the pivot table. Click the arrow button and then check the box Select multiple elements.
- Uncheck the boxes for items you don’t want to include in the pivot table. In our example, we will uncheck 2 sellers, then click OK.
- The PivotTable will be adjusted to reflect the changes.
slices
Slicers further simplify filtering in Excel PivotTables. At their core, slicers are filters, but they are much easier to use, making it even faster and more convenient to process data. If you often filter information in Excel PivotTables, then it is more beneficial to use slicers instead of filters.
Adding a slice
- Select any cell in the pivot table.
- On the Advanced tab Analysis click command Paste Slice.
- A dialog box will appear Insert slices. Select the required field. In our example, we will select the field Sellerand then press OK.
- A slicer appears next to the pivot table. Each selected item will be highlighted in blue. In the following example, the slicer contains a list of all salespeople, and six of them are currently selected.
- When using slicers, just like filters, only the selected elements are displayed in the pivot table. If you select or deselect slicer items, the changes are instantly reflected in the PivotTable. Try selecting a few items to see how they affect the PivotTable. Press and hold a key Ctrl on the keyboard to select multiple slicer items.
You can click the filter icon in the upper right corner of the slicer to select all items at once.
Pivot Charts
PivotCharts in Excel are very similar to regular charts, except that they display data in a PivotTable. Just like with regular charts, you can choose the type, layout, and style of the chart that will present the data.
Create a PivotChart
In this example, the PivotTable shows each salesperson’s sales for the month. We will use a pivot chart to present the information more clearly.
- Select any cell in the pivot table.
- On the Advanced tab Insert, click command Pivot Chart.
- A dialog box will open Insert a chart. Select the appropriate chart view and layout, then click OK.
- The PivotChart will appear on the Excel sheet.
Try applying slicers and filters to your PivotTable to change how much information is displayed. The PivotChart will automatically adjust based on the new data
