To remove grand totals from an Excel PivotTable in Microsoft 365 for Windows, click inside the PivotTable, open Design on the ribbon, choose Grand Totals in the Layout group, and select Off for Rows and Columns. This hides the displayed total row and total column without deleting the source data or changing the underlying records.
What grand totals mean in a PivotTable
A PivotTable can summarize a field across rows, columns, or both. Its grand total is the overall summary for the values currently included in the PivotTable. For example, a report with salespeople as rows and quarters as columns might show a total for each salesperson, a total for each quarter, and an overall total at the bottom-right corner. Depending on the layout, Excel can also show a grand total row or column when there is only one grouping direction.
Grand totals are calculated from the records represented in the PivotTable, taking its current filters into account. They are not ordinary source-data rows. The bottom row labeled Grand Total is part of the PivotTable display, not a record that you can remove from the source table. Turning it off changes the report’s layout, not the data on which the report is based.
Turn off both grand totals in Excel for Windows
Use these steps in Excel for Microsoft 365 on Windows:
- Click any cell inside the PivotTable. The contextual PivotTable tabs appear on the ribbon.
- Click Design.
- In the Layout group, click Grand Totals.
- Choose Off for Rows and Columns.
Excel removes the grand total row and grand total column, if the current layout has them. The category labels and detail values remain. If you later decide the overall summary is useful again, return to the same menu and choose On for Rows and Columns. The setting applies to the selected PivotTable; it does not automatically switch off grand totals in every other PivotTable in the workbook.
If you do not see the Design tab or the Grand Totals command, first make sure the active cell is inside the PivotTable rather than in nearby worksheet cells. The contextual ribbon commands only appear when a PivotTable is selected. On some smaller windows, ribbon groups may be compressed, so the command can appear within a condensed Layout menu.
Hide only the row or column grand total
You do not have to remove both totals. The Grand Totals menu provides choices for displaying totals in both directions, rows only, or columns only, as well as turning them off in both directions. The direction refers to the kind of total shown in the PivotTable layout: a total at the end of the rows is a row grand total, while a total at the end of the columns is a column grand total.
- Choose Off for Rows and Columns to hide all grand totals.
- Choose On for Rows Only or On for Columns Only when one direction is useful and the other is distracting.
- Choose On for Rows and Columns to restore both directions.
For instance, a monthly report may need to show each department’s annual total at the end of its row, but not a bottom line that adds together every department. In that case, keep the row grand total and turn off the column grand total. Which choice is best depends on how readers will compare the figures and whether the overall sum is meaningful for the measure being summarized.
What changes when you remove grand totals
Turning off grand totals changes what Excel displays in the PivotTable. It does not remove, edit, or filter any source records, and it does not change the values in the detail cells. You can still rearrange fields, apply filters, expand or collapse groups, and refresh the PivotTable. If you turn totals back on, Excel calculates and displays them again from the PivotTable’s current contents.
Hiding totals can make a report easier to read when the overall number is not needed, when the total is already shown elsewhere, or when a row or column total could be misinterpreted. For example, adding percentages across categories may not produce a meaningful overall percentage. A total of 100% may be appropriate for one kind of percentage report, while summing percentages calculated on different denominators may be misleading. Removing the displayed total can prevent readers from treating an unsuitable sum as a useful result.
Keep in mind that a PivotChart or another report may still depend on the PivotTable’s fields and summaries. Hiding grand totals is not the same as excluding records from a chart or changing a value field’s calculation. Check the chart and any linked summaries after changing the layout if the workbook is intended for presentation or distribution.
Grand totals are different from subtotals
Grand totals summarize the entire visible PivotTable. Subtotals summarize groups within it. If a PivotTable lists products under categories, a subtotal can show a separate sum for each category, while the grand total combines the categories. Turning off grand totals does not necessarily remove those category subtotals.
To change subtotals, select the PivotTable, open Design, and use the Subtotals command in the Layout group. Depending on the report, you can show subtotals at the top or bottom of groups, or turn them off. This is a separate setting from Grand Totals, so check both menus if the report still shows summary lines after you disable the overall totals.
There is also a distinction between a PivotTable total and a manually typed total in a worksheet cell. Excel’s Grand Totals command controls the PivotTable’s own totals. It will not remove a formula, label, or number that someone placed in a cell outside the PivotTable. If a total remains visible, check whether it is part of the PivotTable or a separate worksheet entry.
Excel for Mac and Excel for the web
In Excel for Microsoft 365 on Mac, select a cell in the PivotTable and look for the contextual PivotTable Design tab on the ribbon. Use its Grand Totals control and choose the option to turn totals off for rows and columns. Ribbon organization can differ slightly between Mac and Windows, so if the command is not immediately visible, check the PivotTable Design tab rather than the regular worksheet tabs.
Excel for the web also provides PivotTable design controls, but the ribbon can differ from the desktop application and may change as Microsoft updates the interface. Select the PivotTable and look for its PivotTable or Design commands for grand totals. If a setting is unavailable in the web interface, open the workbook in the desktop Excel app and use the Windows or Mac steps above. Avoid deleting the worksheet row or column as a substitute: that can alter cells around the PivotTable and does not reliably change its layout setting.
Common problems and how to fix them
- The Grand Totals command is missing. Click within the PivotTable itself. If the selected cell is outside the report, Excel shows the ordinary ribbon instead of the contextual PivotTable tools.
- A summary line remains after grand totals are off. It may be a subtotal. Use Design > Subtotals to control group-level summaries separately.
- The change seems to affect the wrong report. A workbook can contain several PivotTables. Select the specific one you want to change before opening the menu; settings are not automatically shared among separate reports.
- The total returns after rebuilding the report. If you create a new PivotTable, Excel uses the new report’s settings. Set its grand-total option separately. A refresh of an existing PivotTable normally updates its data rather than serving as a reason to delete its total setting.
- You still see a number at the bottom of the worksheet. Check whether it is a separate formula or value outside the PivotTable boundary. The Grand Totals command only controls the PivotTable’s own displayed totals.
When troubleshooting, identify the exact cell that appears to be a total and check whether it belongs to the PivotTable. Clicking it can help reveal whether the contextual PivotTable tabs appear. That simple check avoids deleting nearby worksheet content or changing a different report by mistake.
When to keep totals or use another layout
Grand totals are useful when readers need a quick overall figure, when a report is used to reconcile against a known total, or when the sum provides a meaningful comparison with individual groups. They are less useful when the report is a detail listing, when the total duplicates a headline figure, or when combining the displayed values would be confusing. The decision is about presentation and interpretation, not whether Excel can still calculate the underlying summary.
If you need an overall figure but do not want it inside the PivotTable, you can show the result in a separate report area or create a separate summary. Make clear what filters apply to that figure. A value calculated outside the PivotTable may not respond to PivotTable filters in the same way unless it is deliberately linked to the report. For a shareable workbook, label the number and explain whether it represents all source records or only the filtered subset.
Another option is to rearrange the PivotTable so the categories and measures are easier to compare without an overall total. Changing the layout does not replace the Grand Totals setting, but it can make a report more readable. Before distributing a workbook, check the report with its intended filters, confirm that important categories remain visible, and ensure that any remaining subtotals or separate summary figures are clearly labeled.
Frequently Asked Questions
Can I remove only the bottom Grand Total row?
Yes. Open Design > Grand Totals and choose the option that keeps totals for only the direction you want. The row and column choices let you retain one direction’s total while hiding the other.
Does removing a grand total delete data?
No. It hides the PivotTable’s displayed total. Source records and detail values remain available, and you can turn the total back on from the same menu.
Why do I still see subtotals after turning off grand totals?
Subtotals are group-level summaries controlled separately from grand totals. Use the Subtotals command on the PivotTable Design tab to change or turn off those summaries.
Can I remove grand totals from every PivotTable at once?
The Grand Totals menu changes the selected PivotTable. Excel does not apply that menu choice to every separate PivotTable in the workbook automatically, so select and update each report that needs the change.
Will refreshing the PivotTable bring the total back?
Refreshing an existing PivotTable updates its data and normally retains its layout settings. A newly created PivotTable is a different report and may show grand totals by default, so check its setting separately.
Related
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram: Easy Steps to Create and Format OneTo make an Excel histogram, put your numeric observations in one column, select the data, then choose Insert…
- Excel Pivot Table Gantt Chart: Build and Update a ScheduleYes—you can make a Gantt-style chart from Excel PivotTable data, but Excel does not have a dedicated Gantt…
- Excel Line Chart With Area: Add Shading and Keep the LineTo put shaded area under an Excel line, change the chart to an Area chart, or make a…
- Excel Pivot Table: Filter Out Zero ValuesTo filter zero values out of an Excel PivotTable, open the drop-down for the row or column field…
- Excel Pivot Table Subtotals: How to Show, Change, and Fix ThemIn Excel for Microsoft 365 on Windows, select a cell in the PivotTable and choose PivotTable Design >…
- Sparklines in Excel for the Web: What Works?Yes. Excel for the web can create and display sparklines—small charts placed inside worksheet cells—using the Insert tab.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.