Blog Archives

Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart

Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart

Here is a step by step example of how to program a pivot table and pivot chart, including how to place those objects exactly where you want them on your worksheet.

The programming code is in the downloadable workbook,

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Introduced in Version 2016, the Multi-Select Icon for Pivot Table Slicers

Introduced in Excel 2016 is the Multi-Select icon for pivot table slicers, a welcome convenience to the heretofore requirement of pressing the Ctrl key to select multiple labels.
In the picture for example, I clicked the Multi-Select icon, then clicked to deselect the states of Arizona, Iowa, and Michigan from appearing in the pivot table’s Sum of Sales.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet

Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet
Here is how you can copy a specific pivot field and display its data on another worksheet. In the following pictures, only the columns of a pivot table’s Sales field are copied to Sheet2 so you can work with that Sales data independent of the original pivot table.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Setting 2007+ PivotTables for Drag and Drop

Tom’s Tutorials For Excel: Setting 2007+ PivotTables for Drag and Drop

Excel’s PivotTable models changed starting with version 2007. If you miss the ability to drag and drop field items from the field list into a pivottable’s drop zones the way you could do in the good old days of versions 2003,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using The Legacy PivotTable Wizard in Versions 2007 and After

Tom’s Tutorials For Excel: Using The Legacy PivotTable Wizard in Versions 2007 and After

Are you using Excel version 2007, 2010, or 2013? Are you a pivottable builder who misses the legacy PivotTable and PivotChart Wizard? Instead of going the Ribbon’s route of clicking the PivotTable icon on the Insert tab,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Here’s a macro to locate the first and last rows and columns, and the ranges being occupied by your pivottable. The (0, 0) notation after the Address property statements is to omit the look of absolute “$”

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off

Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off

When you are doing calculations in cells outside of a pivot table that involve cells inside of (that is, belonging to) a pivot table, sometimes you want just the pivot table’s cell value, and other times you want the relative position and content of the cell with the GETPIVOTDATA function.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Refreshing All Your PivotTables at Once

Tom’s Tutorials For Excel: Refreshing All Your PivotTables at Once

Here are two macros you can use for refreshing all the pivot tables on your active worksheet, or all the pivot tables in the entire worbook.

Sub RefreshAllActiveSheet()
Dim Pt As PivotTable
For Each Pt In ActiveSheet.PivotTables
Pt.RefreshTable
Next Pt
End Sub

Sub RefreshAllActiveWB()
Dim Sh As Worksheet,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table

Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table

If you’d appreciate the convenience of having your pivot table automatically refresh whenever you change its source data, here’s how you can accomplish that.

In the Before picture, the source data’s State field lists American states.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Delete all Pivot Tables

Tom’s Tutorials For Excel: Delete all Pivot Tables

You may find the need to delete all pivot tables that are on a worksheet, or that are in the entire workbook.

This first macro deletes all pivot tables on the active worksheet:

Sub DeletePivotTablesWorksheet()
Dim objPT As PivotTable,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,