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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Wonder what happened to the data behind a PivotTable? It could be in a number of places, and tracking it down could be a ...
Discover MorePivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple ...
Discover MoreCreate a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way ...
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 © 2018 Sharon Parq Associates, Inc.
Comments