To remove the bottom grand-total row in an Excel PivotTable, select the table and choose PivotTable Design > Grand Totals > On for Rows Only; this keeps the right-side grand-total column. If you mean the subtotal rows within groups, choose PivotTable Design > Subtotals > Do Not Show Subtotals instead. These are separate settings, so use the one that matches the rows you want gone.
Which total are you trying to remove?
A PivotTable can show two kinds of totals, and they are controlled independently. The grand-total row is usually the last row at the bottom of the table. The grand-total column is usually the last column on the right. These summarize the whole report across the fields arranged in columns or rows.
Subtotals appear inside the report, often after a group of items. For example, a PivotTable grouped by region might show a subtotal after the North region and another after the South region, followed by an overall grand total. Turning off grand totals does not necessarily remove those regional subtotal rows, and hiding subtotals does not remove the overall total.
If you are unsure which setting to use, look at the unwanted row’s position. A single total at the bottom is the grand-total row. Repeated total rows between groups are subtotals. That distinction matters when you want a cleaner report without losing useful summaries elsewhere.
Remove only the bottom grand-total row
In Excel for Microsoft 365 on Windows, click any cell in the PivotTable so its contextual ribbon tabs appear. Then follow these steps:
- Open PivotTable Design.
- In the Layout group, select Grand Totals.
- Choose On for Rows Only to keep the right-side grand-total column while removing the bottom grand-total row.
The menu wording can seem counterintuitive: Excel is referring to totals for rows or columns, not simply naming the physical edge where a total appears. In the usual layout, a total for each row appears in the rightmost column, while a total for each column appears in the bottom row. Check the result after choosing the setting, especially if your report has an unusual arrangement of fields.
If you want to remove both the bottom row and the right-side column, choose Grand Totals > Off for Rows and Columns. If you want the bottom row but not the right-side column, choose On for Columns Only. The On for Rows and Columns option restores both. These settings affect the selected PivotTable, not every PivotTable in the workbook.
Hide subtotal rows throughout the PivotTable
To remove subtotal rows that appear between groups, select a cell in the PivotTable and choose PivotTable Design > Subtotals > Do Not Show Subtotals. This suppresses subtotals across the report while leaving the grand-total settings unchanged. It is often the quickest choice when a report has several row fields and you want a straightforward list rather than grouped summaries.
For example, suppose the Rows area contains Region and then Salesperson. Excel may show a Region subtotal after listing the salespeople in each region. Hiding subtotals removes those intermediate summary rows, but the individual salesperson records and any enabled grand total remain.
Subtotal placement is a separate layout choice. The Subtotals menu can also place subtotals at the top or bottom of a group. Changing their position does not hide them; select Do Not Show Subtotals when you want them absent. If that command is unavailable or the PivotTable is laid out in a way that makes the control hard to find, confirm that a cell inside the PivotTable is selected and that the contextual Design tab is visible.
Turn off subtotals for just one field
Sometimes one grouping level needs a subtotal while another does not. In that case, adjust the field rather than turning off subtotals for the whole report. Right-click a label belonging to the field you want to change and choose Field Settings. In the Subtotals & Filters tab, select None under Subtotals, then select OK.
You can also select a label from that field, open PivotTable Analyze > Field Settings, and make the same change. The exact label you click matters: choose an item under the field whose subtotal you want to suppress, not a value cell or a different row field.
This field-level approach is useful for a report with several levels, such as Year, Quarter, and Product. You might keep annual and quarterly summaries but remove product subtotals that add clutter. If you later add another field or change the layout, inspect the report again: the field-specific setting belongs to that PivotTable field, while the global Subtotals menu controls the report more broadly.
Keep useful totals while simplifying the report
Before removing every total, consider what a reader needs to compare. A manager scanning sales by region may benefit from regional subtotals but not from a bottom grand-total row. Conversely, a detailed transaction-style listing may be easier to read with no subtotals and no grand-total column. Excel lets you combine these choices rather than treating totals as one all-or-nothing feature.
A practical setup might be:
- Keep regional subtotals, but remove the bottom grand-total row, when the report should compare regions without repeating an overall summary.
- Hide all subtotals but keep the bottom grand-total row when readers need individual categories and one overall total.
- Turn off both subtotals and grand totals for a clean list intended mainly for lookup or export.
- Keep both kinds of totals when the report is used for analysis and the summary lines are meaningful.
Think about the purpose of the report before changing its appearance. Removing a total changes what is visible in the PivotTable; it does not change the underlying source records or delete the values used to calculate the report.
Why totals may return or look different
If a total reappears after you change the report, first check that you changed the intended PivotTable. A workbook may contain several PivotTables, and selecting a cell in one table changes the contextual ribbon for that table only. Then check whether you removed subtotals or grand totals: turning off one type does not switch off the other.
Refreshing a PivotTable updates its contents from the source data. The grand-total and subtotal controls are layout settings, so a normal refresh is not the same as turning them back on. However, if someone changes the layout, copies in another PivotTable, or rebuilds the report, the new table may use different settings. Recheck the Design tab after substantial edits or when working with a report created by someone else.
Also distinguish a PivotTable total from a row that merely looks like one. A source-data record might contain the word Total, or a formula or manually typed line might sit immediately below the PivotTable. PivotTable Design controls do not remove ordinary worksheet rows or source records. Click the suspected total and see whether the PivotTable contextual tabs appear; if they do not, the row may be outside the PivotTable.
Excel for Mac and Excel for the web
In current Excel for Mac, select the PivotTable to show its contextual PivotTable Design tab, then use the Grand Totals or Subtotals menu in the same way. Ribbon arrangement and tab names can vary slightly by release, so if the tab is not visible, click inside the table and look for the PivotTable-specific controls.
Excel for the web also provides PivotTable controls, though the available commands and ribbon layout can depend on the workbook and current web version. If you cannot find the specific row-and-column grand-total options or field-level subtotal controls, open the workbook in the desktop app and make the change there. After editing, save the workbook so the layout change is retained for other people who open it.
Frequently Asked Questions
How do I remove only the grand-total row in Excel?
Select the PivotTable and choose PivotTable Design > Grand Totals > On for Rows Only. This normally removes the bottom grand-total row while preserving the right-side grand-total column. Confirm the visual result because the option names describe totals by row or column.
How do I remove subtotal rows but keep the grand total?
Choose PivotTable Design > Subtotals > Do Not Show Subtotals. This removes group subtotal rows without changing the separate Grand Totals setting, so the overall total can remain visible.
Can I remove totals for one row field only?
Yes. Right-click a label from that field, choose Field Settings, open Subtotals & Filters, select None, and choose OK. This is different from hiding subtotals across the entire PivotTable.
Does hiding PivotTable totals delete or change my source data?
No. These commands change the PivotTable’s displayed summaries. They do not delete source rows or alter the underlying values. The PivotTable can still summarize those records when totals are enabled again.
Why is the Subtotals or Grand Totals command missing?
Click inside the PivotTable first so Excel displays its contextual tabs. If the control is still missing, verify that the selected object is a PivotTable rather than a regular range or a copied set of values. Some web or older app interfaces may expose fewer controls than desktop Excel for Microsoft 365.
Related
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Line Chart in a Cell: Create and Format SparklinesTo put a line chart inside an Excel cell, insert a Line Sparkline: select the destination cell, choose…
- Excel Combo Chart Not Working? Fix the Common CausesTo create a combo chart in Excel for Microsoft 365 on Windows, select your data, choose Insert >…
- Build an Excel Dashboard: Steps, Design, and ExamplesYes. To build an Excel dashboard, organize your source data as a clean table, summarize it with PivotTables…
- Build a Gantt Chart in Excel: Step-by-Step GuideTo build a Gantt chart in Excel for Microsoft 365 on Windows, list each task with a start…
- How to Make an Org Chart Bigger in PowerPointTo make an org chart physically bigger in PowerPoint, select its outer boundary and drag a corner handle…
- Pivot Chart Not Showing All Data? How to Fix ItA PivotChart usually misses data because its PivotTable source has not been refreshed or expanded, or because a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.