by contextures.com
If you have a big Excel file, with lots of pivot tables, you might want to document everything that’s in the workbook. Maybe you inherited the workbook, and you’re not sure what’s in it. Or perhaps it’s a file that you only use occasionally, and it’s hard to remember what it contains. To get the details quickly, use the code shown below. This Excel macro lists all pivot tables and pivot fields in the active workbook.
Continue reading “Excel Macro Lists All Pivot Tables and Pivot Fields”
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.
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”
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”
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
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”
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”
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”
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”
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!)
