When you’re setting up a pivot table, you can use the Ribbon commands to change its appearance, and the source data, and several other settings. There’s another place where you can control the pivot table’s behaviour too – in the Pivot Table Options. See how to access that dialog box, and run this pivot table options list macro to see the current settings, for a few of the key options.
Author: Debra
Copy Pivot Table Values and Formatting
In some situations, you might want to send someone a copy of a pivot table, but as values only, not an interactive pivot table. That would give them a summary of the data, but without access to the underlying details, which might be confidential. If you want to copy the pivot table formatting too, it requires extra effort, and the manual steps are shown below, as well as a macro which makes it easier to copy pivot table values and formatting.
Change a Pivot Table Calculated Field Formula
Pivot tables are a great way to summarize a large amount of data, without complex formulas. But if you need to, you can create formulas within a pivot table, with calculated fields and calculated items. After you create one, here’s how to change a pivot table calculated field formula. Continue reading “Change a Pivot Table Calculated Field Formula”
How to Revise Pivot Table Macros
There are lots of pivot table macros on this blog, and some of them affect the first pivot table on the worksheet only. Other macros make changes to the selected pivot table only, based on the active cell. See how to pivot tables macros, so the macros make changes to a specific pivot table, or all the pivot tables in a worksheet or workbook.
List All Pivot Field Details
Here’s another macro to help with documenting what’s in an Excel workbook. Use this macro to make a list of all the fields in a pivot table. The list includes details for each field, such as its caption and location in the pivot table layout.
List All Pivot Tables with Source or MDX
If you have multiple pivot tables and lists in an Excel file, you might need to identify which data source each pivot table uses. This macro will list all pivot tables, with source , or MDX, for OLAP-based pivot tables. Continue reading “List All Pivot Tables with Source or MDX”
Remove Pivot Fields with Macros
If you want to quickly remove all the pivot fields from one part of a pivot table, these macros will help. The sample macro below remove all the row fields, and there are more examples on my Contextures site, as well as a workbook that you can use for testing. Unlike the previous versions, these macros work with both normal pivot tables, and for OLAP-based pivot tables (Data Model).
Excel Macro Lists Pivot Table Details
If you see error messages when refreshing, this Excel macro lists pivot table details, and might help with your troubleshooting. The macro code is shown below, and there’s also a free workbook with the macro in it, that you can download.
Clean Up Pivot Table Subtotals
When there are multiple row fields in a pivot table, the outer fields automatically show subtotals. The pivot table layout can look cluttered if there are too many subtotals, especially if they are close together. Here are a couple of tips to help you clean up pivot table subtotals, to make the data easier to read.
Show Numbers as Text in Pivot Table Values
In an Excel pivot table, you can add text fields to the Row and Column areas, and in the Report Filters. However, pivot table Values area will only show numbers, so if you add a text field there, Excel shows a count of those text items. The technique shown below lets you show number fields as text Values, so you can display the names (East, West), instead of ID numbers (1, 2), for a small group of items.
Continue reading “Show Numbers as Text in Pivot Table Values”