How to set the Sum function as the default function in Pivot table?
Sometimes, when you create a Pivot Table, the Count function is set as the default calculation instead of the Sum function as following screenshot shown. This problem is caused by having some blank cells in the source data of the Pivot Table. How could you set the Sum function as default function in Pivot Table?
To set the Sum function as default function, you should do with the following steps:
1. Select the data range that you want to create Pivot Table based on, and then click Home > Find & Select > Go To Special, in the Go To Special dialog box, select Blanks from the Select section, see screenshot:
2. Then click OK button to close the dialog box, and all blank cells have been selected at once, now, you should enter 0 directly, and then press Ctrl + Enter keys together, and all blank cells have been filled with 0, see screenshot:
3. And then, please select the data range, and click Insert > PivotTable, see screenshot:
4. In the Create PivotTable dialog box, select a cell to put the Pivot Table in the Existing Worksheet or create Pivot Table in New Worksheet, see screenshot:
5. Now, the Sum function has been set as the default function as you need in the created Pivot Table as following screenshot shown:
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!