by contextures.com
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”
In Excel, you can create pivot table calculated item and calculated field formulas. This short video shows you an example of each type of pivot table formulas, and when they should be used.
Continue reading “Create Pivot Table Calculated Item and Calculated Field”
In the Excel PivotTable Options dialog box, there are lots of settings that you can turn on or off. Most of the settings work the way you’d expect, but the “Show the Values Row” option might not seem to do anything. You turn it on and off, and nothing changes. Here’s an explanation of how, and when, the “Show the Values Row” pivot table option works, and you can see the steps in the video below.
After you set up an Excel pivot table, use macros to allow or block pivot table features and commands. That should discourage other people from making layout changes, which could affect other parts of your Excel workbook.
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”
