by contextures.com
After you create a pivot table, you might add new data, or change the existing data. When you refresh the pivot table, it might not show all the new records, or pick up the changes.
To find the problem, you can check the pivot table’s data source. It might not include all the rows, and you’ll have to adjust it.
Continue reading “Find the Source Data for Your Pivot Table”
When you set up a pivot table, the outer field names each appear once, at the top of the group. In the screen shot below, The category names are in the left column, and the products for each category are listed below the headings.
In Excel 2010, and later versions, you can change a pivot field setting, to show the field names in every row, instead of just once. This is useful if the the lists are long, and you can’t see the headings as you scroll down. You can also do lookups from the pivot table, if the names are filled in.
When you create a pivot table, the items in each field are sorted alphabetically, in ascending order, like the products and cities shown below.
Instead of looking at all the data in a pivot table, you can use filters to narrow your focus. See how to apply one filter, or apply multiple filters on a pivot field, without clearing the other filters.
A pivot table is a great way to see a summary of sales to your customers. You can see how much they bought, and how much they spent, on each product. In the pivot table below, each customer is listed, and below the customer name is a list of products and quantities.
Continue reading “Pivot Table Shows Customers With No Purchases”
In the source data for your pivot table, you can use the drop down filters in the heading cells, to show only the records for a specific date range.
For example, in the table shown below, you could show just the orders that were shipped this week.
Continue reading “Dynamic Date Range Filters in Pivot Table”
When you add new items to a pivot table, they might not appear in alphabetical order. In the screen shot below, Binders is a new item, and it’s at the end of the list, instead of at the top.
Continue reading “Show New Pivot Table Items in Alphabetical Order”
If you frequently use filters in your pivot tables, here is a quick way to clear them. Thanks to AlexJ, who shared this tip. You can find more of AlexJ’s Excel sample files on my Contextures site.
Last year, I described how to create a unique count in an Excel 2010 pivot table, by using the PowerPivot add-in. In that version, you can right-click on the table name in the field list, to add a new measure.
Continue reading “Count Unique Items in Pivot Table With Excel 2013 PowerPivot”
When you create a pivot table from your source data, it only shows the items that have data. For example, if you put Customers and Products in the Row area, it only lists the items that each customer has bought.
