Pivot tables are great at summarizing data, but sometimes your data has gaps, and the pivot tables can’t report on numbers that don’t exist. In today’s example, we have health and safety data for the first quarter. Two departments did not have data for that period, and are not listed in the data, but we’d like the report to show zero incidents. Keep reading, to see how to count missing pivot table data as zero.
Author: Debra
Stop the Excel GetPivotData Formula
If you’re building a formula, and you click on a pivot table value, Excel might insert a GetPivotData formula, instead of a cell reference. Here’s how you can stop that annoying problem from happening.
List All Pivot Tables and Their Styles
In a big workbook, you might have lots of pivot tables, and each of those pivot tables is formatted with a PivotTable Style. To quickly see what’s in a workbook, use a macro to list all Pivot Tables and their styles.
Copy Pivot Table Custom Style to Different File
If you create a pivot table custom style, how can you copy that custom style to a different workbook? Excel doesn’t have a built-in command to do that, but you can copy it yourself, with a few simple steps. These instructions work in Excel 2016, and earlier versions too (way back to Excel 2007).
Continue reading “Copy Pivot Table Custom Style to Different File”
Pivot Table Show Values As % of Parent Total
With a pivot table, it’s easy to show a total Sum or Count, or other quick summaries. You can also use the Show Values As options, to show each number compared to other items in the pivot table. There are several options, such as running total, % of Grand Total, and Difference From other amounts. In this example, we’ll show the units sold for each item, as a % of parent total (% of subtotal).
Continue reading “Pivot Table Show Values As % of Parent Total”
Create Pivot Table from Existing Cache
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 use the same pivot cache, or different pivot caches. Use these macros to create a new pivot table from an existing pivot cache, and choose a specific cache – by cache number or pivot table location.
Pivot Table Compares Weekdays in Fiscal Year
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 comparison, add a few formulas to the sales data, then use a pivot table to line up the weekdays.
Continue reading “Pivot Table Compares Weekdays in Fiscal Year”
How to Troubleshoot Pivot Items Missing From Excel Pivot Table
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 our pivot table?
Continue reading “How to Troubleshoot Pivot Items Missing From Excel Pivot Table”
Macro to Create a Pivot Cache List in Excel Workbook
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.
Continue reading “Macro to Create a Pivot Cache List in Excel Workbook”
Refresh an Excel Pivot Table on a Protected Sheet
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 a protected sheet. Use this macro to turn off the protection and update the pivot table.
Continue reading “Refresh an Excel Pivot Table on a Protected Sheet”