by contextures.com
Instead of typing a pivot chart title, which never changes, use this method to create a dynamic heading. See how to create a formula on a worksheet, then link to that cell, to create a pivot chart title from a report filter cell.
Continue reading “Get Pivot Chart Title from a Report Filter Cell”
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.
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.
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.
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”
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”
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.
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”
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”
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”
