Excel Pivot Table Grand Total at Top: What Works

You cannot move a PivotTable’s built-in grand total row to the top in Excel for Microsoft 365 on Windows; Excel keeps that row at the bottom. To show a total above the report, place a formula such as GETPIVOTDATA in a normal worksheet cell above the PivotTable, or build a separate summary that references the report.

Why Excel Keeps the Grand Total at the Bottom

A PivotTable is a structured report, not a freely arranged range of cells. Excel generates its row and column labels, detail values, subtotals, and grand totals according to the fields in the PivotTable. The grand total for row fields appears after the listed items, while a grand total for column fields appears at the right edge. There is no built-in position setting that changes either grand total to the top or left.

This is why dragging the grand-total row above the detail rows does not work like moving an ordinary worksheet row. The row is part of the PivotTable layout, so Excel will not let you rearrange it independently. Refreshing or changing the fields can also rebuild the report, which would undo a manual arrangement even if you could make one.

The limitation applies to the PivotTable’s own total, not to the information you can display around it. You can put a separate, formula-driven total above the report. It will look like a headline total, while the PivotTable continues to calculate and display its built-in total at the bottom.

Best Workaround: Put a Formula Above the PivotTable

For a total that updates when the PivotTable changes, use GETPIVOTDATA in a regular cell outside the PivotTable. This function retrieves a value from the report, so it is generally more robust than adding a SUM formula over a fixed set of PivotTable cells. It can also follow report filters and slicer selections when those selections change the PivotTable’s displayed results.

For example, suppose the PivotTable occupies A3:B8, with Region labels in A4:A7 and the Sales value field in B4:B7. Assume the value field’s displayed name has been set to Sales, and A3 is a cell inside the PivotTable. Put the formula in B2:

=GETPIVOTDATA("Sales",$A$3)

If the four displayed regional sales totals are $42,000, $31,000, $28,000, and $24,000, the formula returns $125,000. The arithmetic is 42,000 + 31,000 + 28,000 + 24,000 = 125,000. If the value field still displays as Sum of Sales rather than Sales, use its exact displayed caption as the first argument. The second argument must refer to a cell inside the PivotTable.

To make it read like a total at the top, add a label such as Overall sales in A2, format B2 as currency, and apply bold text or a fill color. The formula is outside the PivotTable, so it will not be included in the PivotTable’s sorting or layout. Keep enough space between this summary row and the PivotTable so the report can expand when refreshed.

How to Insert the Formula

Click the worksheet cell where you want the top total to appear, outside the PivotTable.
Type an equals sign, then click the PivotTable value you want to retrieve. Excel will often generate a GETPIVOTDATA formula automatically.
For a grand total, use the data-field caption and a reference to a cell inside the PivotTable, without adding row or column item pairs.
Press Enter, then check the result against the PivotTable’s built-in grand total.
Add a descriptive label and format the result. Refresh the PivotTable and confirm the separate formula still points to the intended report.

If clicking a PivotTable value inserts an ordinary cell reference instead, GETPIVOTDATA generation may be turned off. In Windows desktop Excel, select a cell in the PivotTable, open the PivotTable Analyze tab, and check the Generate GetPivotData control in the PivotTable group. The control toggles automatic formula generation; it does not disable the GETPIVOTDATA function itself. You can also type the function directly.

What the Grand Totals Setting Can and Cannot Do

If the goal is to change whether totals appear, use the PivotTable’s grand-total options. Select a cell in the report, then choose PivotTable Design > Grand Totals. The available choices include showing totals for rows and columns, showing them for rows only, showing them for columns only, or turning them off for both. These options control visibility and scope, not position.

Turning off the built-in row grand total can reduce duplication if you have placed a separate total above the report. But check the column grand total separately: a PivotTable with several value columns may have a total at the right that readers still need. You can choose the appropriate option rather than turning off every total indiscriminately.

The PivotTable Design tab is a contextual tab, so it appears when a PivotTable cell is selected. On Mac, Excel also provides PivotTable design controls, though tab names and ribbon placement can vary slightly by release. Excel for the web has PivotTable editing capabilities, but the available controls can differ from desktop Excel; if a particular layout option is missing, open the workbook in the desktop app.

Do Not Confuse Subtotals with the Grand Total

A common source of confusion is the option to show subtotals at the top of a group. Subtotals summarize groups within a PivotTable—for example, a subtotal for each region when the rows include region and salesperson. They are different from the overall grand total.

To change subtotal placement, select the PivotTable and use PivotTable Design > Subtotals > Show All Subtotals at Top of Group. This places each group’s subtotal above its items; it does not move the overall grand total. If the report has only one row field, changing subtotal placement may make no visible difference because there are no nested groups to subtotal.

For example, a report grouped by Region and then Salesperson can show a regional subtotal above the salespeople in each region. The overall total for all regions remains at the bottom. A separate formula above the PivotTable is still needed to put the overall figure at the top.

Keeping the Top Total Reliable After Refreshes

A separate total is useful only if it continues to reflect the PivotTable. GETPIVOTDATA is usually the best starting point because it identifies a value field and a PivotTable, rather than assuming that the total will always sit in a particular cell such as B8. If the number of regions changes after a refresh, the total can still be retrieved without editing a range endpoint.

Several checks help avoid misleading results:

Make sure the formula points to a cell that remains inside the same PivotTable. If the report is deleted and recreated elsewhere, update the reference.
Use the exact value-field caption shown in the report. Renaming a value field can mean the formula’s first argument must be updated.
Test the formula after changing report filters, slicers, or the source data. Confirm that the result matches the grand total you intend readers to see.
Leave a blank row or otherwise reserve space above the PivotTable. A refresh that adds items can expand the report, and a separate formula should not overlap its cells.

GETPIVOTDATA returns a value from the PivotTable; it does not independently recalculate the source data. If the source has changed but the PivotTable has not been refreshed, the formula may continue to show the report’s older result. Refresh the PivotTable first, using PivotTable Analyze > Refresh, or Data > Refresh All if you need to update multiple connections and reports.

Alternatives and When to Use Them

For a quick, fixed report, you can copy the built-in grand total and paste it above the PivotTable as a value. This is easy to format, but it will not update when the data or filters change. It is appropriate for a dated snapshot, not for a report that readers expect to refresh.

A direct SUM formula over the PivotTable’s visible values is another option, but it can be fragile. For example, summing B4:B7 assumes that all the relevant values remain in those exact rows. If a new region is added, the range may omit it unless the formula is adjusted. A carefully designed dynamic range can address that issue, but for a PivotTable total, GETPIVOTDATA communicates the intent more clearly.

If a report must literally have its total row first, rather than merely display a top-line total, consider building a separate presentation table or summary report. It can use formulas linked to the PivotTable or to the underlying data, with the total placed wherever the layout requires. The trade-off is that this is a second report to maintain; it is not a change to the PivotTable’s native grand-total position.

Choose based on what matters most: use GETPIVOTDATA for a refreshable headline total, a pasted value for a static snapshot, or a separate summary layout when the total’s physical row position is essential. Keep the PivotTable’s own total when readers benefit from seeing it at the bottom as a verification point.

Formatting and Presentation Tips

A top-line figure should be unmistakably a summary and not look like another PivotTable row. Use a clear label such as Total sales for current selection, and consider a date or note explaining which filters apply. If the report has a slicer for year or product, a reader should be able to tell that the headline number changes with that selection.

Match the number format to the measure. For sales, currency with sensible decimal places is usually clearer than a long unformatted number; for counts, a whole-number format is more suitable. Formatting the formula cell does not alter the source field or PivotTable calculation. If you add a new worksheet row above the report, check the formula’s reference after the change and make sure the PivotTable still has room to grow.

Frequently Asked Questions

Can I drag an Excel PivotTable grand total to the top?
No. Excel treats the grand total as part of the PivotTable layout, and its row grand total stays at the bottom. Put a linked formula above the report if you need a total at the top.

Is there a setting to show the grand total first?
No. PivotTable Design > Grand Totals controls whether row and column totals appear, not their position. The option for subtotals at the top applies to group subtotals, not the overall grand total.

Will a GETPIVOTDATA total update when I use a slicer?
It generally reflects the value currently shown by the PivotTable after a slicer changes its selection. Confirm this in your workbook, especially if the report uses multiple filters or connections, and refresh the PivotTable if its source data has changed.

Why does Excel return a #REF! error?
Check that the cell reference in the formula points inside the intended PivotTable and that the data-field caption matches the report. The error can also occur if the PivotTable or referenced field is no longer available.

Can I hide the bottom total after adding one at the top?
Yes. Select the PivotTable and use PivotTable Design > Grand Totals to turn off row grand totals, or choose a setting that keeps only the totals you still need. The formula above remains a separate worksheet cell.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.