PivotTables in Microsoft Excel are a great way to get insights from big data sets in just a few seconds. However, most people don’t make full use of their capabilities, sticking to their basic functionalities like creating simple summaries. So, here are some ways to expand your PivotTable expertise.

To follow along as you read this guide, download a free copy of Microsoft’ssample data set. Then, before you use the data set to create a PivotTable, make sure each column is assigned the correctnumber format(text, accounting, or short date).

A cell in a data set in Excel is selected, and the top half of the PivotTable button in the Insert tab is highlighted.

Creating a PivotTable From Your Big Data Set

To turn your big data set into a PivotTable, select any cell in the range, and in the Insert tab on the ribbon, click the top half of the split “PivotTable” icon.

Then, in the PivotTable From Table Or Range dialog box, double-check that the correct range ortable nameis selected, and decide whether you want the PivotTable to be added to the active worksheet or a new worksheet. In my case, since my data set is so big, I want to insert my PivotTable on a new worksheet. Then, click “OK.”

The PivotTable dialog box in Excel, with the table named ‘financials’ selected, the New Worksheet radio button checked, and the OK button highlighted.

Let’s say you want to analyze the profit made in each country. To do this, in the PivotTable Fields pane, click and drag the “Country” field to the Row area and the “Profit” field to the Values area.

Click and drag the header of the PivotTable Fields pane to move it to a new position. Then, you may resize it so that it takes up less screen space.

A PivotTable in Excel with the Product field clicked and dragged to the Column area.

If, for example, you also want to see a breakdown of the individual products sold per country, click and drag “Product” to the Columns area.

To remove a field from your PivotTable, click and drag it away from the PivotTable Fields pane.

The Sum Of Profit column is duplicated in an Excel PivotTable.

Showing a Value as a Percentage of the Total

Now that you’ve condensed your big data set into profit by country, let’s say you want to go one step further and display each country’s figures as a percentage of the overall profit.

Without PivotTables, you would probably generate a formula that divides each country’s profit by the total profit, and format the result as a percentage. However, you can execute this task much more quickly using the built-in PivotTable tools.

Percentage Of Grand Total is selected in the right-click menu of a column in an Excel PivotTable.

First, duplicate the Sum of Profit column by re-adding the “Profit” field to the Values area.

Now, you’re ready to convert the duplicated column into a percentage-of-total column. To do this, right-click the column header, hover over “Show Values As,” and click “% Of Grand Total.”

A PivotTable in Excel where each country’s profit is displayed as a percentage of the overall total.

Now, your PivotTable displays each country’s profit as a percentage of the overall total, and you can override the default column header to improve clarity and readability.

Filtering the Top Values

Suppose you want to celebrate the segments that have made the biggest profits by sending a report to all your colleagues.

First, click and drag “Segment” to the Rows area and “Profit” to the Values area.

Segment is in the Rows area of Excel’s PivotTable Fields pane, and Sum Of Profit is in the Values area.

At this point, youcouldsort the PivotTable by the Sum of Profit column by right-clicking any cell in that column, hovering over “Sort,” and clicking “Largest To Smallest.”

However, in its current form, the PivotTable also shows the lowest-performing segments—including one that made a loss—and you don’t want to shame those who work in those departments!

The right-click menu of the Sum Of Profit column in an Excel PivotTable is expanded, and Sort Largest To Smallest is selected.

Instead, you only want to show the top three performing segments. To do this, right-click any cell in the Row Labels column, hover over “Filter,” and click “Top 10.”

Next, in the second field of the Top 10 Filter dialog box, type3, and check that the other fields align with what you want to show. Then, click “OK.”

Segments in a PivotTable in Excel are sorted by profit, with the Enterprise segment shown as making a loss.

Now, the PivotTable only shows the top-performing segments, so once you’ve sorted the data by the Sum of Profit column, you can copy and paste the PivotTable into a team chat, email, or Word document for distribution.

To hide the grand totals, open the “Design” tab on the ribbon, click the “Grand Totals” down arrow, and select “Off For Rows And Columns.”

The right-click menu of the Row Labels column in an Excel PivotTable is expanded, and Top 10 in the Filter option is selected.

Filtering Using Slicers and Timelines

Two of my favorite features of PivotTables in Excel are slicers and timelines, which make filtering the data super easy.

Adding and Using Slicers

Let’s say you’ve created a PivotTable showing total profit by country. However, now, you want to filter the data by product.

Youcouldadd the Product field to the Row area of the PivotTable Fields dialog box and filter the data by right-clicking a product, hovering over “Filter,” and clicking “Keep Only Selected Items.”

However, this method is time-consuming and not very user-friendly if you share the workbook with others.

Instead, remove “Product” from the Rows area, select any cell in the PivotTable, and in the PivotTable Analyze tab, click “Insert Slicer.”

Next, in the Insert Slicers dialog box, check the variable or variables by which you want to filter your data—in this case, “Product”—and click “OK.”

Now, you’re able to display only the profit made from a certain product by selecting one of the items in the slicer. What’s more, you can filter by more than one item at a time by holding Ctrl as you click the options. In this example, the PivotTable displays each country’s profit from Carretera and Velo sales.

Click the button in the top-right corner of the slicer to clear all filters.

Adding and Using Timelines

While you can create a slicer to filter your data according to various dates, PivotTables in Excel have a built-in timeline tool that does the job even better.

PivotTable timelines only work if a column in the source data set is properly formatted as a date.

Select any cell in the PivotTable and, once again, head to the “PivotTable Analyze” tab on the ribbon. This time, however, click “Insert Timeline.” Then, in the Insert Timelines dialog box, check “Date,” and click “OK.”

Now, click the down arrow in the timeline to divide the dates into years, quarters, months, or days. Then, select a single period in the timeline or click and drag the timeline bar to display data from across a longer period.

Grouping Data for Quick Aggregation

Another way to analyze your data using a PivotTable in Excel is to create custom groups.

Here, while the PivotTable displays the number of units sold per country, let’s say you’re more interested in comparing sales in the Americas (North and South America) and Europe.

To do this, select all the countries that will make up the first group by holding Ctrl as you click them. Then, right-click one of the selected countries, and click “Group.”

Now that those countries are grouped together, rename the group.

Finally, repeat the process for the remaining rows.

You can now expand or collapse each group by clicking the “+” or “-” icons next to the group names you assigned.

To change the data aggregation in the Values column (such as from sum to average), right-click the column header, hover over “Summarize Values By,” and select from the options.

Drilling Down Into a Category

While PivotTables in Excel are designed to summarize values for quick analysis, you’re able to drill down into a specific category to explore the data in more depth.

In this example—where the PivotTable rows show countries, segments, products, and the number of sales for each—imagine you want to see more information about Paseo sales made by channel partners in Canada. To do this, double-click the corresponding cell in the Values column.

Now, a new worksheet opens with a table displaying all the columns from the original data set for the criteria you selected.

You can use the same method for broader categories. For example, double-clicking the total units sold in Canada opens a table in a new worksheet that displays all the sales data for all segments and products in Canada.

Creating Report Filter Pages

PivotTables in Excel can be the starting point for creating individualized reports.

Suppose you want to send a report to the manager of each segment containing a breakdown of their profit per country. First, create a PivotTable where Country is in the Rows area and Profit is in the Values area.

Next, click and drag “Segment” into the Filter area of the PivotTable Fields pane, and notice that the corresponding filter is added to row 1 in the workbook.

At this stage, youcouldclick the filter down arrow in cell B1 to select a segment and generate individual reports manually.

However, Excel’s PivotTable tool has a built-in feature that can create these reports for you in one go. Select any cell in the PivotTable, and in the PivotTable Analyze tab on the ribbon, click the “Options” down arrow. Then, click “Show Report Filter Pages.”

Because you already added Segment to the Filters area of the PivotTable Fields pane, this is the only option in the Show Report Filter Pages dialog box, so select it and click “OK.”

Now, Excel has generated individual reports for each segment, which you may analyze and distribute as necessary.

PivotTables work in a similar way toExcel’s PIVOTBY function—they’re both great tools for summarizing and reorganizing data for detailed analysis. However, the methods for using them differ significantly, so be sure to check out their pros and cons before you decide which is best for executing your number-crunching tasks.