John Glentees 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 a 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!
If you are using a data set that includes a number of zero values, you may not want those values to appear in a PivotTable ...
Discover MorePivotTables are used to analyze huge amounts of data. The number of rows used in a PivotTable depends on the type of analysis ...
Discover MoreIf you create a PivotTable in Excel 2007, you may have problems editing or updating that PivotTable in Excel 2003. The only ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2014-06-02 17:01:57
Candi
If you change the format of the cells in the column to general text rather than a number, the default will be count not sum.
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 © 2017 Sharon Parq Associates, Inc.
Comments