This question came from a Painless Excel Pivot Tables™ student.
How can I make the SUM function be the default in pivot tables? Currently mine always defaults to COUNT.
First, we must determine why a column full of numbers is automatically summarized using the COUNT function. The reason is simple: if a column of numbers contains blanks, the values will be summarized using the COUNT function by default.
The solution lies in converting all blanks in number column(s) to zero before you create the pivot table.
Step 1 (Optional) – Select all of the numbers in your pivot table’s data source.
Depending on how your data is structured, you can use keyboard shortcuts to select cells.
CTRL SHIFT Down Arrow – Selects from current selection to the end of your column.
CTRL SHIRT RIGHT ARROW – Selects from current selection to the furthest column on the right.
CTRL SHIFT END – Selects from current point to the end of the data.
Note: If you do not have blanks in text columns, you can skip this step. Try this tutorial by starting at Step 2 and see if it works for you.
Step 2 – Select all blanks.
Use Excel’s Goto Special feature to select all blanks.
Type CTRL G, then click the Special button at the bottom of the dialog box.
Select the Blanks option box, then click OK.
For you keyboard shortcut warriors, the sequence is GoTo (CTRL G) Special (ALT S) Blanks (ALT K) Enter.
Anomaly: If your pivot table’s source data is in an Excel table, Excel won’t recognize blanks using GoTo Special. Instead, you have to filter blanks. For this reason, it might be easier to convert your table to a regular range, follow the steps in this tutorial, then convert your source data back into a table.
Step 3 – Replace blanks with zeros.
Type 0 once, then hit CTRL ENTER. This will fill all of the blanks with zeros.
Once you’ve done this, create your pivot table. The formula for your numbers values will default to SUM.
Interested in learning about pivot tables? Learn more about our Pivot Tables and PowerPivot courses.
Do you have an Excel question?
Comment below and it just might make it to a blog post!
About the Author
Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint. Upcoming Excel courses are on our calendar, click here.
Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.
Click to Grab Your Excel Ebook – Free!
