by contextures.com
With a running total in a pivot table, you can see how amounts accumulate over a period of time, or through another field, such as products, or customers.
In the screen shot below, the Quantity field shows a running total across the Months column. In March, the Year To Date total for Corner Cabin is 2595.
It would be nice to automatically refresh an Excel pivot table, whenever its source data changes. Unfortunately, there isn’t a built-in way to make that happen. However, here are 3 ways you can refresh a pivot table, after you make changes to the source data.
Continue reading “Automatically Refresh an Excel Pivot Table”
Many years, and thousands of pivot tables ago, I wrote a couple of articles for Jon Peltier’s Excel charting website.
Jon has been updating his website, and asked me to write new versions of those articles. He has just published them, and you can follow the links below, to read them.
Now that fall is here, you might be back into a routine of weekly meal planning, and Excel is a great tool to use for that.
To help you get started, there is a weekly meal planner on my Contextures website, that you can download. Enter the meal and product information, based on your favourite meals.
Then, pick your meals for the upcoming week, and click a button to create a weekly shopping list.
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.
