Master Pivot Tables: Top 5 Hidden Features!

Today, we're diving into Pivot Tables - Excel's secret weapon for turning raw data into actionable insights. Whether you're crunching numbers for your startup or analyzing research for your studies, Pivot Tables can be a game-changer.

Imagine transforming a messy spreadsheet into a clean, interactive summary with just a few clicks. That's the power of Pivot Tables! They're fast, flexible, and help you avoid those pesky manual calculations.

Let's explore how to harness this tool to uncover hidden patterns and make data-driven decisions.

Want to receive regular Newsletters over e-mail? - Subscribe here.

Five Features of Pivot Tables to Simplify Your Life

1. Create an Excel Pivot Table in Just Two Clicks

Many Pivot Tables focus on common questions, like “What are the total sales by salesperson?” or “What are the daily sales?”

You can create these Pivot Tables quickly using the Recommended Pivot Tables feature located on the Insert ribbon.

This displays a panel with thumbnail images of common Pivot Tables based on your data. Simply select the one that suits your needs and place it on a new or existing sheet. These are fully formatted and ready to use, but you can modify them as needed.

Alternatively, you can simply click on Insert > Pivot Tables.

2. Adjust Pivot Table Fields Settings and Layout

When working with the PivotTable Fields drag-and-drop interface, rearranging the field list and field wells can make your work easier.

Click the Tools (gear) icon in the PivotTable Fields panel to choose from four different layouts.

From here, you can rearrange the field list and field wells into one of four layouts.

Each layout has its pros and cons, and your screen size, number of fields, and personal preference will guide your choice.

Sorting the field list alphabetically can also help locate specific fields more easily.

Want to receive regular Newsletters over e-mail? - Subscribe here.

3. Add a Timeline Slicer to a Pivot Table

Timeline Slicers are invaluable for filtering data by date in Pivot Tables.

They allow you to set levels such as years, quarters, months, or days and use drag-and-drop controls to adjust ranges.

Customize the Timeline Slicer’s appearance and controls to suit your needs.

Timeline Slicers can be created by selecting PivotTable Analyze (tab) – Filter (group) – Insert Timeline.

When creating the Timeline Slicer, any date-based fields in your Pivot Table will be listed for use by the control.

You can customize the Timeline Slicer’s color, as well as which controls and information you wish displayed for the user to utilize.

4. Using a Hidden Pivot Table for Dynamic Reports

Typically, the Pivot Table is the final link in the analytic chain. But it doesn’t have to be. Consider this use for a Pivot Table.

Since we can’t use Slicers to filter individual cell calculations, we can use a Slicer to filter a Pivot Table, then use the Pivot Table’s filter results to drive single-cell calculations. Here’s how this works:

We start with a Pivot Table that uses a field from the data (ex: Salesperson).

Create a Pivot Table Slicer that uses the Salesperson field.

Select a single entry from the Pivot Table Slicer to reduce the Pivot Table to but a single entry.

We can now use the single entry display in the Pivot Table (cell G3) in a variety of cell formulas.

Changing the Slicers to a different person changes the Pivot Table which then updates all the downstream calculations.

NOTE: Be aware that if the user selects multiple items from the Slicer, only the first entry in the Pivot Table will be used in the calculations. This is because the formulas are only looking at a single cell in the Pivot Table area.

5. Display the Top N Items in a Pivot Table (Top N Analysis)

Pivot Table results can get rather lengthy, especially when working with dozens, hundreds, or thousands of items in a field, like product names or employee names.

Suppose you only want to see the Top 3 (or whatever number you wish) items of the data set.

To do this, select the Filter dropdown for the Rows field (ex: Salesperson), and choose Value Filters.

In the Top 10 Filter dialog box, select the number of items you wish to display, along with the field that holds the number that the Top N will be based around.

If you are using a row-based hierarchy (ex: Year to Month to Salesperson), each month will be reduced to only show the top N salespersons based on the Order Total.

An added touch would be to right-click one of the Order Total values and sort from largest to smallest. This performs the sorting on a per-Top N basis.

Want to receive regular Newsletters over e-mail? - Subscribe here.

Pivot Tables are an indispensable tool for simplifying data analysis and making informed decisions. By mastering these hidden features, you can transform raw data into meaningful insights, empowering you to make impactful choices in your studies or business operations.

Stay Exceling, and see you next week!

Your Excel Coach!

CA Isha Jaiswal

Co-Founder, Grad Me Up!