Excel PivotTable FAQ’s

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:

1. 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?
2. How do I change the PivotTable values from Count of… to Sum of… etc.?
3. 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:

• Excel Tables
• Structured References

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.