by contextures.com
If you have multiple copies of a pivot table in a workbook, on different sheets, you can use the GETPIVOTDATA function to pull the total amount from a specific pivot table. In this example, there are 3 copies of the pivot table in a workbook, each with a different layout and filter applied.
The sheets are named consistently, starting with “PT_”, and the pivot table location is the same on each sheet.
Continue reading “Get Total Amount from Specific Pivot Table”
When you add numeric fields to a pivot table, Excel applies General format, in most cases. It doesn’t matter if you’ve spent hours applying fancy custom formatting to the numbers in the source data – all of that is ignored.
After the numeric data has been added to the pivot table, you can manually change the formatting of each field, in the Field Setting window. It’s not too painful in a small pivot table, but can take a while if there are several Values fields.
Continue reading “Copy Pivot Table Source Number Formatting”
As much as I love Excel pivot tables, there are a few annoying problems that you’ll run into, while working with them. I made a list of my top 5 annoyances, and you might have other problems to add to the list.
When you add fields to the Values area, they are set as either Sum or Count, and the field is automatically renamed. For example, Quantity becomes “Sum of Quantity”, or “Count of Quantity”. These long field names can make the columns wider than necessary, but if you try to change those names, Excel might show an error message — “PivotTable field name already exists.”
In this video, a pivot table summarizes the products sales over a two year period. With a Value Filter, you can quickly show the top products, and compare top and bottom products.
Continue reading “Compare Top and Bottom Products”
Thank you for following my pivot table tips, here on the Excel Pivot Tables Blog. I appreciate it!
There are many more tips and tutorials on my Contextures website, and I’ve just finished setting up a Pivot Table index, so you can find everything.
If you’ve bought a copy of my PivotPower Premium add-in, I sent out an update email last week, so you can download the latest version.
There are a few new features, that I hope you will find useful. A big thank you to my lovely customers, who suggested these new features!
If you create a calculated item, extra items might appear in the pivot table, with zero amounts in some rows. In the example shown below, a calculated item was created in the Category field. Because of that, all the cities are listed under each region, instead of just the region in which they are located.
Los Angeles and San Diego are not in the East, so their sales in that region show up as zero. It makes the pivot table look messy and confusing!
Last week, you saw how to calculate the fiscal year and month, by adding formulas in a pivot table’s source data.
The formulas referred to a named cell – FYStart – where you enter the start month of the fiscal year.
