Written by Allen Wyatt (last updated December 31, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
John asked if there was a way to change the default SUM function used when creating PivotTables; he would prefer to use COUNT or AVERAGE. The short answer is that no, there is no way to change the default—you are still left with changing the field options for whatever you place in the Data Items area of the PivotTable.
If you find it bothersome to change from SUM to COUNT or AVERAGE after the PivotTable is finished, consider making the change while constructing the table. At step 3 of the PivotTable Wizard, click the Layout button and drag your fields to their respective locations. After you drag a field into the Data Items area, double-click the field and you can change how the field is aggregated.
You can, if desired, also change the way that you put together your PivotTable to simulate a count, even though the SUM function is really being used. Just add a column to the right side of your data table, and label it Count. In each cell of the new column place the number 1. If you use this column (Count) as the Data Item in the PivotTable, the result at each intersection of the table is a count of the items matching the aggregate column and row. Even though SUM is used in creating the PivotTable, the result is the same as if you had changed to COUNT.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2392) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
PivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple ...
Discover MoreExcel allows you to link to values in other workbooks, even if those values are in PivotTables. However, Excel may ...
Discover MoreWhen you update a PivotTable, Excel can take liberties with any formatting you previously applied to the PivotTable. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments