Author Archives: Debra
When you create a pivot table in Excel, a pivot cache is automatically created. The pivot cache is a special memory area where the pivot table records are saved. If there are multiple pivot tables in a workbook, they might … Continue reading
If you’re tracking retail sales year over year, it’s not too helpful to compare sales by calendar date. The dates will fall on different weekdays each year, so slow weekdays will be compared to busy days. To get a better … Continue reading
Why are a couple of pivot items missing from this pivot table? The source data has these employees marked as “Yes”, but that information isn’t showing up in the pivot table summary. How can we troubleshoot pivot items missing from … Continue reading
In a large Excel file, with several pivot tables, it’s easy to lose track of how many pivot caches there are. Instead of trying to manually figure things out, use this macro to create a pivot cache list.
When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables and pivot charts. However, even with that option turned on, you can’t refresh an Excel pivot table on … Continue reading
Usually, pivot tables are used for exploring data in the workbook. Occasionally though, you might need to print out a pivot table, to include in a report. To help with that, use the pivot table printing macro below, and visit … Continue reading
When a pivot field is in the row or column area, you can sort the pivot items in that fields, using the built-in commands. For fields in the Report Filter area, there isn’t an easy way to sort the pivot … Continue reading
While you work on a big pivot table in Excel, you might need to document which fields are in the layout. To make that job easy, use this macro to list all pivot fields and pivot items in any pivot … Continue reading
In a pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. The innermost field doesn’t show subtotals, but you can force them to appear, by creating pivot table custom subtotals. Learn more … Continue reading
Instead of manually taking fields out of a pivot table, you can remove pivot fields with a macro. The sample macro below will remove all the row fields, and there are more examples, and a free workbook, on my website.