If you remove items from a pivot table’s source data, those items might still appear in the pivot table drop down lists, even after you refresh the pivot table. Here’s how you can stop those old items from appearing – watch the short video, or read the written steps below.
Author: Debra
Create a Simple Calculated Field in Pivot Table
See how to create a simple calculated field in a pivot table in Excel, to show the bonus that each sales representative will receive. The formula will multiply each person’s total sales by 3%, to create a new value in the pivot table.
Continue reading “Create a Simple Calculated Field in Pivot Table”
Excel Pivot Table Sorting Macro Data Model
When you add fields to the pivot table row area, you can sort the items alphabetically, or by the numbers in the Values area. The sorting can be done manually or with a macro. See below for an Excel pivot table sorting macro. Data Model pivot tables can be sorted with it too.
Continue reading “Excel Pivot Table Sorting Macro Data Model”
Unpivot Excel Data With Get & Transform
If you want to build a flexible pivot table, you might need to rearrange your source data, before you start. For example, if there is a separate column for each month’s sales, you should “unpivot” the data, to get all the amounts in one column. The good news is that you unpivot Excel data with Get & Transform, and your original data isn’t changed. If you don’t have G&T, there’s a macro that you can use instead
List Pivot Table Calculated Fields in Excel
With a built-in pivot table command, you can quickly list pivot table calculated fields and calculated items. This helps if you’re starting to use a pivot table that someone else built, or even for documenting a pivot table that you built yourself. Continue reading “List Pivot Table Calculated Fields in Excel”
Excel Pivot Table Error Cannot Group That Selection
When you try to group dates in an Excel pivot table, or other pivot table items, you might get a pivot table error, “Cannot group that selection.” In Excel 2010, and earlier versions, that error was usually caused by blank cells in your source data, or text in the number or date columns. If you’re using Excel 2013 or later, there’s another reason that might prevent you from grouping pivot table items. Continue reading “Excel Pivot Table Error Cannot Group That Selection”
Show Distinct Count in Data Model Pivot Tables
In a pivot table you might want to see a distinct count (unique count) for some of the data, instead of an overall count.
For example, if pens and binders are sold in different colours, how many unique colours were sold for each product? Here’s how to show a distinct count in Data Model pivot tables.
Continue reading “Show Distinct Count in Data Model Pivot Tables”
Stop Pivot Table Date Grouping
When you add a date field to your Pivot Table, Excel automatically groups the dates into a hierarchy, such as years and months.
See how to stop pivot table date grouping in the latest versions of Excel, and a couple of workarounds for Excel 2016. You can also read about why this feature was added, and watch a date grouping video.
Continue reading “Stop Pivot Table Date Grouping”
Pivot Table Value Errors
If there are errors in an Excel table, you might see those errors when you summarize that data in a pivot table. See which Summary Functions show those errors, and which ones don’t (most of the time!)
Excel Pivot Table Name Rules
When you build a pivot table, Excel automatically creates a numbered name for it, such as PivotTable1. You can leave that automatic name as it is, or change an Excel pivot table name to something more meaningful, such as “SalesPivot”.