My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Excel PivotTable FAQ’s

You are here: Home / Excel PivotTables / Excel PivotTable FAQ’s
by

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:

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.

PivotTable Value Field Settings format numbers

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.

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel PivotTables Posts

Sorting Excel Date Slicers
Use these tricks to custom sort Excel Slicer buttons enabling them appear in a specific order that suits your needs.
PivotTable Formatting
Create PivotTables that don’t look like PivotTables with these really easy PivotTable Formatting tips and tricks.
Images in Excel Pivot Tables
Include images in Excel PivotTables, combined with some slick formatting, and create dynamic and visually rich PivotTables like never before.
Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.
Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.
There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L
Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.
Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.
Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.


Category:

Reader Interactions

Comments

  2. 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.

    Reply

    • 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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *