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:
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.
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.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.