by contextures.com
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!)
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”.
When you’re setting up a pivot table, you can use the Ribbon commands to change its appearance, and the source data, and several other settings. There’s another place where you can control the pivot table’s behaviour too – in the Pivot Table Options. See how to access that dialog box, and run this pivot table options list macro to see the current settings, for a few of the key options.
In some situations, you might want to send someone a copy of a pivot table, but as values only, not an interactive pivot table. That would give them a summary of the data, but without access to the underlying details, which might be confidential. If you want to copy the pivot table formatting too, it requires extra effort, and the manual steps are shown below, as well as a macro which makes it easier to copy pivot table values and formatting.
