Advanced Excel Pivot Tables: 10 Techniques + Copilot Tips (2026)

Get the Advanced Pivot Table Workbook

Practice timeline slicers, Top N views, and calculated fields with the exact workbook from Leila’s video, including how to build a Pivot Table with Copilot.

How to Customize the PivotTable Fields Pane

The PivotTable Fields List is the drag-and-drop interface on the right side of Excel that allows you to populate the logic that drives the Pivot Table.

This panel can be customized in a variety of ways.  By clicking the Tools (gear) icon you can rearrange the positions of the field list and the corresponding drop zones.

Each of these arrangement options has its strengths and weaknesses.  Experiment with the options to see which fits your needs best.

You can also sort the items in the Field List either alphabetically or in Data Source (the order listed in the source table) order.  Alphabetically is best when working with unfamiliar data sets containing many fields.

A Search field is available for ease of locating fields.

How to Create Multiple PivotTable Reports Automatically

One of the features new users to Pivot Tables soon learn is the ability to filter the Pivot Table by a “global” field.  In other words, filter the entire table by a single, overall criterion such as “Customer Name” or by “Department”.

Suppose you want to create a separate report for each item in the global filter zone?  Imagine performing the following steps:

1. Select the unfiltered Pivot Table
2. Copy the Pivot Table
3. Create a new worksheet
4. Paste the Pivot Table
5. Set the global filter to the desired field item
6. Return to the original, unfiltered Pivot Table
7. Repeat steps 1 thru 6

Now imagine performing those steps 200 times.  You would quickly tire of the process.

What if you could create every one of those filtered Pivot Table reports in just 1 click? (actually, 3-4 click, but who’s complaining?)

With the desired field placed in the Filters zone, select Pivot Table Analyze (tab) -> PivotTable (group) -> arrow next to the Options button -> “Show Report Filter Pages”.

This displays a dialog box listing all the Pivot Table’s global filters.

Select the appropriate field and click OK.  Then sit back and enjoy the show.

Imagine seeing 200 Pivot Tables appear before your eyes in a matter of seconds.  These would take hours (or days) to create manually.

How to Drill Down Into PivotTable Data with One Click

This one is a “boss-pleaser” feature.  Suppose your boss examines the following pivot tables and asks this question:

“Show me the full details for every record that comprises the $209,280 (Lucas Basics – Europe).”

You could perform the following steps:

1. Return to the original data
2. Filter the “Company Name” field by “Lucas Basics”
3. Filter the “Region” by “Europe”
4. Select the results
5. Click Copy
6. Create a new worksheet
7. Paste the copied results into the new worksheet

…OR…

…you could just double-click in the number “$209,280” and call it a job well done.

This double-click action creates a new sheet and places the complete records from the table that contribute to the original value.

If this question was just a casual curiosity, you can delete the sheet without harm and you are back to where you were moments ago.

How to Add Visual Data Bars to a PivotTable

Data Bars are created by the Conditional Formatting tool and are a great way to create in-cell bar charts.

“But how do we create them in a Pivot Table?”

It’s not much different than creating Data Bars in a regular table but there is an extra step.

Using the above Pivot Table as the example:

1. Add the “Sales USD” to the Values This displays the expected numbers (in column B).
2. Add the “Sales USD” a second time to the Values
3. Select any number in the second set of “Sales USD” and click Home (tab) -> Styles (group) -> Conditional Formatting -> Data Bars -> pick your desired color.

You will see a Data Bar on the single cell you originally selected.

Here comes the extra step.

Click the small button to the right of the cell containing the Data Bar to reveal the Formatting Options list.

If you want EVERY number in that column (including the totals) to display a Data Bar, select “All cells showing “Sales USD” values.

If you only want to display Data Bars for the row-level items (no totals), select “All cells showing “Sales USD” values for “Product Description”.

Tweaking the Results

Since we don’t need to see the numbers twice, we can hide the numbers in the Data Bar column by selecting Home (tab) -> Styles (group) -> Conditional Formatting -> Manage Rules.

Select the Data Bars rule and click “Edit Rule”.

In the Edit Formatting Rule dialog box, place a check in the option labeled “Show Bar Only” and click OK.

If you don’t want to display a title above the Data Bars, you can manually change the name in the cell to a single space.

Word of Warning!!!

If you refresh the Pivot Table, you run the risk of collapsing the column containing Data Bars.

To prevent this from happening, set the Data Bar’s column to your desired width, then select the Pivot Table Options (upper-left).

In the PivotTable Options dialog box, on the “Layout & Format” tab, uncheck the option labeled “Autofit column widths on update” in the “Layout” section.

How to Create Calculated Fields in a PivotTable

Think of a calculated field as an additional field in your data (like “Tax”) that is calculated based on the structure of the Pivot Table.

In other words, if you were only showing sales for each “Company Name” and wanted to display the total tax for each, instead of creating a “Tax” column in the data with the requisite calculation (this could require hundreds or thousands of row-level calculations), we could instead wait to see how the Pivot Table is grouped and only calculate taxes for each item in the group.  (sorry for such a long sentence)

In this example, three calculations are better than thousands.

To do this, perform the following steps:

1. Select the Pivot Table
2. Select PivotTable Analyze (tab) -> “Fields, Items, & Sets” -> “Calculated Field…”

In the Insert Calculated Field dialog box, enter a name for the new column and write the formula as you would most normal Excel formulas, and click OK.

With a bit of number formatting and column heading customization, you have your new calculated column.

A couple of interesting features of Calculated Columns:

• You can reuse the new calculation from the PivotTables Field List in as many Pivot Tables as you like.
• If you update the Calculated Column (ex: change the tax rate), every Pivot Table that uses the calculation is updated to reflect the new value.
• Changing the base row or column selection creates all-new calculations.

How to Show Month-over-Month Changes in a PivotTable

Suppose you have a Pivot Table that displays sales for products across two months.

If you wanted to calculate the increase or decrease in sales from June to July, you can perform the following steps:

1. Add the “Sales USD” field a second time to the Values zone.

2. Right-click on one of the values in the second instance of sales and select Show Values As -> “Difference From…”.

3. In the “Show Values As…” dialog box, set the “Base Field” to Months and the “Base Item” to Previous.

The result (after customizing the heading and hiding the empty column) appears as follows.

How to Use Custom Number Formatting in a PivotTable

Using the results of Tip #6 above, suppose we wanted to display up and down arrows in place of the values, just to indicate an increase or decrease from the previous month.

1. Select a cell in the “Difference” column.
2. Right-click the cell and select “Number Format…”.
3. On the Number tab of the Format Cells dialog box, select
4. Erase the word “General” or whatever text happens to occupy the “Type:” option.

Using the “Windows Key-Period” key combination to open the symbols window, browse or search for “arrow”.

NOTE: If you are not running a Windows 10 environment, you can copy-paste symbols from the Clipboard.

To add a splash of color to the arrows, we’ll add color codes to the Custom Number Format; [Red] for negative values and [Color10] to give the positive values a subdued green color.

If all you are interested in is whether you made or lost money from the previous month, this is easier to recognize than scrutinizing each value.

How to Group Items in a PivotTable (Custom Categories)

Suppose we wish to create three groups of products: “Mens”, “Womens”, and “Other”.

This will be performed in 3 passes; one for each group.

1. Select all rows containing “mens” items.
2. Right-click the selection and click “Group…”.
3. Rename the newly created group “Mens”.

Repeat the process for the rows containing women’s items.

Finally, select the rows for “other” items.  If the needed rows are separated by other rows of unrelated data, use the CTRL key to select multiple, non-adjacent items.

If you would rather display the “Others” group at the bottom of the list, you can click the cell labeled “Others”, then grab the edge of the cell selection border and drag the group to a new position such as the bottom of the list.

One of the great things about this grouping is if you were to create a hierarchy of categories, each would be sub-divided by this new group logic.

How to Group Dates in a PivotTable by Week, Month, or Quarter

Beginning users of Pivot Tables quickly learn that when you add a date-based field to a Pivot Table, the Auto-Grouping feature attempts to group the data by “Year/Quarter/Month”, “Month/Day”, or something along these lines.

What if you wanted to display records grouped in sets of 5-day blocks?  This is easier than you might think.

Using the below Pivot Table showing sales by day…

Right-click a date and select “Group…”.

Ensure that “Days” is the only selected item and set the “Number of Days” option to 5.

Observe the newly “grouped by 5 days” results.

How to Add a Timeline Slicer to a PivotTable

We can give the user an easy-to-use Slicer-style interface for selecting date ranges.  This is done by way of the Timeline Slicer.

To add a Timeline Sliver, select the Pivot Table and click PivotTable Analyze (tab) -> Filter (group) -> Insert Timeline.

Select the field in your data that contains dates you wish to filter by.  Note: only date-based fields will be displayed in the Insert Timeline dialog box.

Size and position the newly inserted Timeline Slicer as needed.

You can use the slider bars to reduce or expand the range of qualifying records based on years, quarters, months, or days.

How to Create a PivotTable Using Copilot in Excel (2026)

If you have a qualifying Microsoft 365 subscription, you can now build a PivotTable from a plain-language prompt instead of setting it up manually. Copilot handles the field selection, layout, and placement. You just describe what you want to see.

This won’t replace knowing how PivotTables work. But for a first draft, or when you’re not sure which fields to use, it’s a fast starting point.

What you need before you start

• A Microsoft 365 Personal or Family subscription (with an AI credits plan), Microsoft 365 Premium, or a qualifying Microsoft 365 business or enterprise subscription with Copilot enabled
• Your data formatted as an Excel Table (Insert > Table, or Ctrl+T)
• The file saved as .xlsx in OneDrive or SharePoint with AutoSave turned on
• The desktop or web version of Excel

Note: Copilot cannot access other files, emails, or external data sources — it works only with the currently open workbook.

How to build a PivotTable with Copilot

1. Open your workbook in Excel for the web or the desktop app
2. Click the Copilot icon on the Home tab to open the Copilot pane
3. Editing is on by default — you’ll see an Excel selector appear in the chat box confirming Copilot is ready to make changes to your workbook
4. Type a prompt describing the summary you want, for example: “Create a PivotTable showing total sales by region and product category”
5. Copilot builds a step-by-step plan, executes it, and places the PivotTable on a new sheet — you can watch its reasoning in the pane as it works
6. Refine with follow-up prompts if needed: “Add a slicer for year” or “Sort by sales descending”

The more specific your prompt, the better the result. Include the field names from your table header row and the calculation you want (sum, average, count).

What Copilot can and can’t do with PivotTables

Copilot is strong at generating a usable first layout quickly, suggesting follow-up analysis, and adding conditional formatting or charts to accompany the PivotTable. For complex multi-step requests, it may take a few minutes. You’ll see its reasoning as it works through each step.

It is less reliable with complex calculated fields, multi-level custom groupings, and anything that requires understanding your specific business logic. Always review the output before sharing it. If you need to undo any changes, use Ctrl+Z or check version history via File > Info > Version History.

For simple – one-step tasks – inserting a single PivotTable from a clear, small dataset, Excel’s built-in Recommended PivotTables (Insert tab) is often faster than Copilot.

For everything beyond a basic summary – calculated fields, custom number formatting, date grouping, data bars – the techniques covered in this post still apply once Copilot has generated the initial table.

Enable PivotTable Auto Refresh

Until recently, updating a PivotTable required a manual step every time your source data changed. That changes with PivotTable Auto Refresh, now rolling out to Microsoft 365 subscribers.

With Auto Refresh on, any change to your source data — a new sales row, a corrected figure, a renamed category — is instantly reflected in the PivotTable. No right-click, no ribbon button, no VBA workaround.

How to enable it:

1. Click anywhere inside your PivotTable
2. Go to the PivotTable Analyze tab on the ribbon
3. Click Auto Refresh in the Data group to toggle it on

Auto Refresh is on by default for any new PivotTable you create. For existing PivotTables, you’ll need to enable it manually using the steps above. You can also set it as your default for all future PivotTables via File > Options > Data > Edit Default Layout > PivotTable Options > Data tab.

Important limitation: Auto Refresh only works with data sources inside the current workbook. If your PivotTable pulls from Power Query, an external database, or a SharePoint list, this toggle will have no effect — you’ll still need to refresh those manually.

If the Auto Refresh button doesn’t appear on your ribbon, your Microsoft 365 build hasn’t received the feature yet. Go to File > Account > Update Options > Update Now to check for the latest version.

When to Use the PIVOTBY Function Instead of a PivotTable

Excel now has a formula-based alternative to PivotTables called PIVOTBY, available in Microsoft 365 and Excel 2024. It’s not a replacement — it’s a different tool for a different situation.

PIVOTBY lets you create a grouped, aggregated summary using a single formula in one cell. The result spills automatically across your worksheet and updates live whenever your source data changes — no refresh step required.

A basic example:

=PIVOTBY(B2:B100, C2:C100, D2:D100, SUM)

This groups your data by the values in column B (rows) and column C (columns), then sums the values in column D — the same output as a traditional PivotTable, but built entirely in a formula.

Use PIVOTBY when:

• You want results that update automatically without any refresh step
• You’re embedding the summary into a larger formula or dashboard
• You need to combine data from multiple tables using VSTACK before summarizing
• You want the logic to be visible and auditable directly in the formula bar

Stick with a traditional PivotTable when:

• You need slicers, timelines, or drill-down interactivity
• You’re working with large datasets from external sources like Power Query or SQL
• You’re sharing the file with colleagues who use Excel 2019 or earlier — PIVOTBY shows a #NAME? error on unsupported versions
• You want automatic date grouping by month, quarter, or year (PIVOTBY requires you to wrap date columns in YEAR() or TEXT() manually)

For most day-to-day reporting, PivotTables remain the faster and more user-friendly choice. PIVOTBY earns its place when you need a summary that behaves like a living part of your worksheet rather than a separate object that needs maintenance.

Frequently Asked Questions

What is the difference between a PivotTable and the PIVOTBY function in Excel?

PivotTables use a drag-and-drop interface and work in all versions of Excel. The PIVOTBY function is a formula-based alternative available in Microsoft 365 and Excel 2024 that updates automatically when your source data changes. No manual refresh needed.

PIVOTBY is faster to set up for simple summaries, but PivotTables are better for interactive dashboards, date grouping, slicers, and large datasets from external sources. Most analysts use both depending on the task.

How do I stop a PivotTable from showing outdated data?

The fastest fix is to right-click anywhere inside the PivotTable and select Refresh. To refresh automatically every time you open the file, go to PivotTable Analyze > Options > Data tab and check “Refresh data when opening the file.”

If you’re on Microsoft 365 and have the latest update channel build, you can also enable PivotTable Auto Refresh via PivotTable Analyze > Auto Refresh. This keeps the table live as your source data changes without any manual steps.

Can Copilot build a PivotTable for me automatically?

Yes, if you have a qualifying Microsoft 365 subscription. Open the Copilot sidebar from the Home tab, make sure your data is formatted as a table and saved as an .xlsx file in OneDrive or SharePoint, then type a prompt like “Create a PivotTable showing total sales by region and month.” Copilot uses “Edit with Copilot” to build the table and places it on a new sheet. You can then refine it with follow-up prompts.

Why does my PivotTable not update when I add new data?

Two common causes. First, your new data may be outside the PivotTable’s source range. If your data is not formatted as an Excel Table (Insert > Table), adding rows below the range won’t be picked up automatically. Convert your data to a proper Table first, then refresh. Second, Auto Refresh may not be enabled. This feature is rolling out to Microsoft 365 Current Channel users and may not yet be available in your build. In the meantime, right-click the PivotTable and select Refresh after adding data.

What version of Excel do I need for PivotTable Auto Refresh?

PivotTable Auto Refresh requires Microsoft 365 (subscription version) on Windows or Mac. It is not available in one-time purchase versions like Office 2019 or Office 2021. It only works with data sources inside the current workbook. PivotTables connected to external sources like Power Query, SQL, or SharePoint lists are not supported.

If the Auto Refresh toggle doesn’t appear on your PivotTable Analyze tab, your build hasn’t received the feature yet — check for updates via File > Account > Update Options.

Download the Workbook

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses