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,
…
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.
…
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.
…
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,
…
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,
…
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 “$”
…
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.
…
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,…
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.
…
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,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
