Calculation Issues

Add a Column Total at the Top of an Excel Worksheet

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Display a column total above the data and keep it updated as values are added.

Add a Column Total at the Top of an Excel Worksheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to see a column total at the top of a worksheet rather than below the data.
Observed behavior
The desired total appears above the values and recalculates when the referenced data changes.
Before you start

Choose a cell above the values for the total and check which rows contain the data. If you plan to use an Excel Table, leave the total cell outside the table.

Solution 1Recommended

Use a SUM Formula Above a Standard Range

Use this method when you want a simple total above values in a known range.

1
Select the total cell

Click cell A1, assuming the values to total start in A2.

2
Enter the formula

Type =SUM(A2:A50000) in A1 and press Enter. Adjust the column and last row to cover the range you need.

3
Add a heading if needed

To show text and the total in the same cell, enter ="Column Heading ("&SUM(A2:A50000)&")" in A1 instead.

Use a SUM Formula Above a Standard Range
Range limit: The total updates when values within the referenced range change, but rows beyond row 50000 are not included. The heading formula returns text rather than a numeric value.
WPS Office

Create a Top-of-Column Total in WPS Spreadsheets

Use a SUM formula in WPS Spreadsheets to display a total above your data and update it as values change within the selected range.

  1. 1. Open the worksheet: Open WPS Spreadsheets and choose Open to select your workbook.
  2. 2. Choose the total cell: Click A1 above the values, assuming the data begins in A2.
  3. 3. Enter SUM: Type =SUM(A2:A50000) and press Enter. Change the range to match your data.
  4. 4. Save the workbook: Choose File > Save As and select an Excel-compatible XLSX format if needed.
Keep a column total visible above the dataRecalculate the total when referenced values changeOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface for free
microsoft office alternative - wps office

Frequently Asked Questions

Why should the total cell be outside the SUM range?

Including the total cell in its own formula creates a circular reference. For example, if the total is in A1, start the range at A2 rather than A1.

Will a fixed SUM range include new rows?

New values are included if they fall within the referenced range. For =SUM(A2:A50000), values beyond row 50000 are not included unless you change the formula.

How do I show a heading and total together?

In the cell above the data, enter ="Column Heading ("&SUM(A2:A50000)&")". The result is text containing the heading and total.

How can I make the total expand with an Excel Table?

Select the data and choose Insert > Table, then enter a formula such as =SUM(MyTable[ColumnWithValues]) in a cell outside the table. Replace the names with your table and column names.