After having thousands of people recently attend our free PivotTable webinar we found there were 3 questions that were asked again and again, which are:
- How do I format my data in an Excel Table so I can refresh my PivotTable and it automatically picks up any new data added?
- How do I change the PivotTable values from Count of… to Sum of… etc.?
- How do I format the PivotTable values area and make it stay that way?
Here are the answers which are video extracts from our Xtreme PivotTable course:
1. Tables
For more on Tables and how to use Structured References in your formulas check out these tutorials:
2. Change Count of to Sum of
3. Predetermined Number Formatting
Bonus tip: If you want to format columns in your Values area differently e.g. some as currency, some as percentages etc. then choose a cell in the column you want to format > right-click > Value Field Settings > in the Value Field Settings dialog box (see image below) click the 'Number Format' button > choose your formatting then click OK, then OK again.
This will set the format for the field so whenever you use it, in any PivotTable that shares the same Pivot Cache, it will retain the formatting.
Rinse and repeat for the other fields in your Values area.
Want More?
If you’re ready to take your PivotTable skills to the next level and stand apart from the crowd then check out John’s Xtreme PivotTable course.
Excel PivotTable Expand, Collapse and Show Details
Chaman jha
Good Excel Tricks
Rodney Jones
I watched your Excel Dashboards webinar last night, and I have re-watched it this morning. I just wanted to say that this is the best and clearest explanation of Excel Dashboards that I have ever seen.
Although this was just an overview of the power of Excel Dashboards, it demonstrated some really exciting (and really useful) new Excel skills which I will be practicing and then using on a day-to-day basis.
Currently, I am not in a financial position to sign up for your Excel Dashboards Course, but as soon as I attain a new job and my finances improve I will definitely be signing-up for it. Who knows – I may even get my new employer to pay for it !
So, I just want to say a big thank-you for sparking my “Excel imagination” once again. It was thoroughly enjoyable and is something I will highly recommend to others.
Rod Jones – North Wales – United Kingdom 18 April 2015.
Mynda Treacy
Hi Rod,
Thanks for your kind words about my Excel Dashboard webinar. I’ts wonderful to know it has sparked your interest in Excel once again.
All the best with your search for a new job and happy dashboarding 🙂
Kind regards,
Mynda