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.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!