In a complex Excel file, you might have several lists, and multiple pivot tables based on those lists. To keep things organized, use this macro to make a list of pivot tables in the active workbook. The code is shown below, and there is also a link so you can download a free workbook with the macro.
Why do new pivot items appear at the end of the lists, when you add them to an Excel pivot table? It’s hard to find those new items, if they aren’t sorted alphabetically. Keep reading, to see why that happens, and how you can fix the problem of new pivot items at end of list.
When you create a pivot table to summarize data, Excel automatically creates sums and counts for the fields that you add to the Values area. In addition, you might want to see a distinct count (unique count) for some fields, such as:
- The number of distinct salespeople who made sales in each region
- The count of unique products that were sold in each store
When a pivot table has grand totals, Excel automatically names those totals. I’ll show you some examples, with details on which grand total headings you can change, and which ones you can’t.
Pivot tables are a quick way to summarize your data, and they have another advantage too. After you build the pivot table, it’s easy to change pivot table layout, to get a different type of summary. Watch the video below, and get the free workbook.
If you plan to build a pivot table, check the source data first, to make sure you have it set up correctly. If there is a heading for each month’s sales, instead of just one column where all the amounts are stored, that won’t work well in a pivot table. You need to “unpivot” your data first, and you can use a macro to unpivot Excel data.
When you put value fields into a pivot table, it automatically adds text to the headings, such as “Sum of Quantity”, if the field name is Quantity. You can manually change those headings, to remove the extra text, if there are only a few of them. If there are lots of fields to change, it’s quicker to remove that text with a macro.
If you try to show hundredths of second, or tenths of second in pivot table, you might have trouble. Usually, the times are rounded, and there is a zero, instead of numbers, for the tenths and hundredths.
See how to fix that pivot table time problem, so that the numbers are displayed correctly.
After you create a pivot table, you can insert a pivot chart, based on that pivot table. In this example, the chart shows sales data, per city, over two years. See how to change the chart layout, after you build it.
Top 10 Filters are a quick and easy way to focus on key items in a pivot table. Instead of looking at all the data, hide everything except a set number of top or bottom items.
In the screen shot below, you can see the Pivot Table Top 10 Filter dialog box. There are four drop down lists, where you can change the settings, but Excel won’t let you link to a worksheet cell, like it does in some dialog boxes.