Add a Column Total at the Top of an Excel Worksheet
Question details
Display a column total above the data and keep it updated as values are added.

- 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.
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.
Use a SUM Formula Above a Standard Range
Use this method when you want a simple total above values in a known range.
Click cell A1, assuming the values to total start in A2.
Type =SUM(A2:A50000) in A1 and press Enter. Adjust the column and last row to cover the range you need.
To show text and the total in the same cell, enter ="Column Heading ("&SUM(A2:A50000)&")" in A1 instead.

Use an Excel Table for an Expanding Column Total
Use a table reference when you expect to add rows and want the total to include the expanded table column.
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. Open the worksheet: Open WPS Spreadsheets and choose Open to select your workbook.
- 2. Choose the total cell: Click A1 above the values, assuming the data begins in A2.
- 3. Enter SUM: Type =SUM(A2:A50000) and press Enter. Change the range to match your data.
- 4. Save the workbook: Choose File > Save As and select an Excel-compatible XLSX format if needed.

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.




