Pivot Table Issues

How to Display Row Totals in an Excel Pivot Table

Elise Williams Sep 28, 2026 869 views

Question details

The user is trying to display row grand totals in an Excel PivotTable, but only column totals appear even though the grand totals feature is enabled.

How to Display Row Totals in an Excel Pivot Table

Product: Microsoft Excel
Device & OS: not provided
Scenario: Summarizing categorized data, such as monthly sales, across multiple columns in a PivotTable report.
Observed behavior: The PivotTable successfully calculates column grand totals but fails to generate row grand totals because the source values are spread across separate unpivoted columns.

Before you start

Verify that your PivotTable settings are properly configured by going to the Design tab, clicking Grand Totals, and ensuring 'On for Rows and Columns' is selected before attempting to modify your data structure.

Solution 1Recommended

Restructure Source Data to a Tabular Format

The most robust way to get row totals is to ensure your source data stores values in a single column rather than spreading them across multiple columns (like individual months).

PivotTables are designed to aggregate data vertically. When identical types of data (like monthly sales figures) are spread horizontally across multiple columns, the PivotTable treats them as separate fields rather than a single measurable dataset, preventing the generation of row totals.

Unpivot your data

Restructure your source data into a strict tabular list. For example, instead of having columns for Jan, Feb, and Mar, create one column named 'Month' and another named 'Value'.

Move data to the new columns

Place all month identifiers in the 'Month' column and their corresponding numerical values in the 'Value' column.

Refresh the PivotTable

Right-click anywhere inside your existing PivotTable and select 'Refresh', or insert a new PivotTable based on the updated data range.

Rebuild the PivotTable layout

Drag the 'Month' field to the Columns area and the 'Value' field to the Values area. Row totals will now calculate automatically.

Restructure Source Data to a Tabular Format

Data Modeling Tip: Keeping your source data in a flat, tabular format (unpivoted) is highly recommended as it prevents formula errors and makes future data analysis much easier.

Solution 2

Add a Calculated Field to Sum Separate Columns

If you cannot change the layout of your original source data, you can create a custom calculated field within the PivotTable to manually sum the required columns for each row.

Free Microsoft Office alternative

Easily Create and Manage Pivot Tables with WPS Spreadsheet

WPS Office provides a powerful Spreadsheet application that fully supports advanced Pivot Table functionalities, including calculated fields and automatic grand totals, with zero learning curve.

1. Open your dataset: Launch WPS Spreadsheet and open your existing .xlsx data file.
2. Insert a PivotTable: Navigate to the Insert tab on the ribbon and click 'PivotTable'.
3. Build your summary: Drag your structured fields into the Rows, Columns, and Values boxes in the right-hand side panel to instantly generate summaries.
4. Enable grand totals: Go to the PivotTable Tools tab, select 'Grand Totals', and turn on totals for both rows and columns effortlessly.

Fully compatible with Microsoft Excel (.xlsx) formats and Pivot Table structuresIntuitive drag-and-drop interface for managing rows, columns, and automatic grand totalsFree, lightweight, and fast to process large datasets without crashingIncludes built-in data formatting tools to quickly unpivot and restructure your source tables

microsoft office alternative - wps office

Frequently Asked Questions

Why are Grand Totals for rows grayed out in my PivotTable?

This usually happens if you are working with multiple consolidation ranges or a complex data model that does not support standard row grand totals without establishing explicit data relationships first.

How do I turn on Grand Totals in my PivotTable?

Click anywhere inside the PivotTable, go to the Design tab on the ribbon, click 'Grand Totals' on the far left, and select 'On for Rows and Columns' from the drop-down menu.

Can a PivotTable calculate totals for text fields?

No, a PivotTable cannot mathematically sum text. If your data column contains text strings instead of numbers, the PivotTable will default to counting the items instead of summing them. Ensure your source data is strictly formatted as numbers.

Why is my Calculated Field returning the wrong total?

Calculated fields perform calculations on the aggregate sum of the underlying data, not row-by-row. If you are using multiplication or division in your calculated field alongside sums, the order of operations on aggregated data may cause unexpected results.

Excel & Data Problems