How to Display Average Monthly Sums in an Excel PivotTable
Question details
The user wants to display both the total sum and the average for monthly spending or categories simultaneously within an Excel PivotTable.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing monthly numerical data in a PivotTable and needing to view two different summarization types (Sum and Average) side-by-side for the same data field.
- Observed behavior
- By default, dragging a field to the Values area applies only one summarization method, requiring the user to find a way to add a second calculation for the same field.
Ensure your source data is organized in a clear tabular format with distinct columns for categories, dates or months, and the numerical values you wish to calculate.
Use Value Field Settings to Display Both Sum and Average
You can add the same numerical field into the Values area of your PivotTable multiple times and change the summarization method for each instance.
This is the standard and most straightforward method to show multiple calculations for a single data point in Excel PivotTables. It works across all operating systems and versions.
Click anywhere inside your existing PivotTable to display the PivotTable Fields pane on the right side of the window.
In the PivotTable Fields pane, drag your numerical field (e.g., 'Spending' or 'Sales') into the 'Values' area. Then, drag the exact same field into the 'Values' area a second time.
Click the dropdown arrow next to the second instance of your field in the 'Values' area, and select 'Value Field Settings' from the menu.
In the dialog box, under the 'Summarize value field by' tab, select 'Average' from the list of calculation types. Click 'OK' to apply the changes.
Use Power Pivot to Create Custom DAX Measures
If you are working with a complex Data Model, you can use Power Pivot to create separate explicit measures for Sum and Average.
Calculate Averages and Sums in PivotTables with WPS Office
WPS Spreadsheet provides a robust and intuitive PivotTable feature. You can effortlessly calculate sums, averages, counts, and other metrics simultaneously, fully compatible with your existing Excel files.
- 1. Insert a PivotTable: Open your spreadsheet in WPS Office, select your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
- 2. Drag Fields to Values: In the PivotTable fields task pane, drag your category fields to 'Rows', and drag your numerical data field to 'Values' twice.
- 3. Modify the Calculation: Double-click the second instance of the data field in the Values area to open the Value Field Settings.
- 4. Select Average: Choose 'Average' as the summarization method, rename the column if desired, and click 'OK'.
Frequently Asked Questions
Can I format the average column as currency in the PivotTable?
Yes. Open the Value Field Settings for the average field, click the 'Number Format' button at the bottom left, and choose 'Currency' or 'Accounting'. This ensures the averages display properly without affecting the source data.
Why does my average calculation show an error or incorrect value?
This usually happens if there are blank cells or text values in your source data column. Ensure that all cells in the column you are summarizing contain valid numerical values. Blank cells may skew the count used to determine the average.
Can I display Count, Sum, and Average all at once?
Yes. You can drag the same field into the Values area as many times as you need. Open the Value Field Settings for each instance and choose the respective calculation type (Sum, Average, Count, Max, Min, etc.).
