How to Add a Total Row in Excel

To add a total row in Excel, click any cell in your table, open the Table Design tab, and select the Total Row check box. Excel inserts a totals row at the bottom and writes =SUBTOTAL(109,[Column]) for you. To total a column without a table, click the cell below your numbers and press Alt and = to run AutoSum.

Last updated: August 25, 2026

Instead of manually calculating totals, Excel can do the heavy lifting for you. This guide covers six ways to total a column in Excel, the exact total formula Excel writes behind the scenes, how to total a row instead of a column, and what to do when the total row is missing or returns the wrong number.

What is on this page

• Method 1: Insert a total row in an Excel table
• The Excel total formula: what SUBTOTAL actually does
• Method 2: AutoSum and the Alt and = shortcut
• Method 3: The SUM function
• Method 4: See a column total with no formula at all
• Method 5: The Quick Analysis tool
• Method 6: Total an entire column in Excel
• Which method should you use?
• How to total a row in Excel instead of a column
• How to total multiple columns at once
• Add a total row and display the average for a column
• Total row missing, grayed out, or wrong? Fix it here
• Doing this in Google Sheets instead
• FAQ

Method 1: How do you insert a total row in an Excel table?

Excel's Table feature makes adding a total row quick and dynamic. The totals row recalculates automatically when new data is added to the table, and it is the only method that survives you inserting new rows in the middle of your data. Here is the step-by-step guide.

1. Convert data into a table

Select the range of data you want to include in the table. Go to the Insert tab in the Excel ribbon. Click on the Table button, or just press Ctrl + T. Excel will ask you to confirm the data range, and you can also indicate if your data has headers (check the box if it does).

2. Enable the Total Row

After converting the data into a table, you will see a new Table Design tab in the ribbon (older versions call it Table Tools). In this tab, look for the Total Row check box and select it. A total row will immediately appear at the bottom of the table. The keyboard shortcut is Ctrl + Shift + T.

3. Customize the Total Row

By default, Excel adds a Sum to the last column only, and only if that column holds numbers. Every other cell in the total row starts empty. Click any cell in the row Excel created and use the drop-down list that appears to pick Sum, Average, Count, Count Numbers, Min, Max, StdDev, Var, or None.

Rule to remember: the Table total row is the only total in Excel that grows with your data. Add a row to the table and the total updates itself. Every other method needs its range edited by hand.

The Excel total formula: what SUBTOTAL actually does

When you select Total Row, Excel does not write a plain SUM. Click the total cell and look in the formula bar. You will see something like this:

=SUBTOTAL(109,[Cost])

SUBTOTAL takes a function number and a range. The number decides which calculation runs. This is why the total row can switch between Sum and Average from a drop-down without you rewriting anything.

The 100-series numbers ignore rows you have hidden manually. The single-digit versions (9 for Sum, 1 for Average) include manually hidden rows. Both versions ignore rows removed by a filter. That difference is the single most common reason an Excel total row disagrees with a SUM formula sitting next to it. Microsoft documents the full function-number list on the Total the data in an Excel table support page.

Method 2: AutoSum and the sum a column in Excel shortcut

The AutoSum feature is the fastest way to add up a column in Excel when your data is not in a table. It works on small and medium datasets and needs no setup.

1. Place your cursor below the data

Navigate to the column where you want to add a total to your Excel spreadsheet. Click on the empty cell directly below the last number in the column. This is where the total will appear.

2. Use the AutoSum button or the keyboard shortcut

In the Home tab of the Excel ribbon, find the AutoSum button (it looks like the Greek letter sigma, ∑). Click it and Excel will detect the range of numbers above your selected cell. The keyboard shortcut is Alt + = on Windows and Command + Shift + T on Mac.

3. Check the formula range

Excel highlights the range of cells it assumes you want to sum. It stops at the first blank cell or the first cell containing text, so if your column has a gap in it Excel will guess short. Drag over the correct cells or edit the range directly in the formula bar before you commit.

4. Press Enter to confirm

Once the correct range is highlighted, press Enter. Excel calculates the sum and displays the result in the selected cell.

5. Drag AutoSum across columns or rows

You can use the fill handle to add totals to the entire row or multiple columns. After applying AutoSum in one cell, click the small square at the bottom-right corner of the cell and drag it across the row. The column letters shift as you drag, which is what you want here.

Rule to remember: AutoSum guesses a range, it does not know your data. Always read the highlighted range before pressing Enter.

Method 3: How to total a column in Excel with the SUM function

If you prefer more control or do not want to convert your data into Excel tables, you can add a total row using a simple formula. This is the method to use when your total needs to sit somewhere other than directly under the data.

1. Locate the last row of data

Scroll to the bottom of your dataset or place your cursor in the cell below the last row of numbers in the column you want to total.

2. Enter the SUM formula

In the first empty cell below the numbers, enter =SUM(range), where range is the group of cells you want to total. To sum the values in cells B2 through B6, enter:

=SUM(B2:B6)

3. Drag the formula across columns

Once you have added the SUM formula in one column, copy it to other columns by clicking the small square at the bottom-right corner of the cell (the fill handle). Drag this fill handle across the row to apply the same formula to adjacent columns. Leave the references relative, with no dollar signs, or every column will report the same number.

4. Make the total box stand out

Apply bold formatting, a top border, or a different fill color to the total row so the total box separates visually from the rest of your data. Select the cell, then use Home > Font > Bold and Home > Font > Fill Color.

5. Watch out for text that looks like a number

SUM silently skips any value stored as text, so a column that looks correct can still total low. If your total is smaller than expected, check for left-aligned numbers and green triangles in the corner of the cells.

Method 4: How to get a column total in Excel with no formula at all

Sometimes you do not want a total row, you just want to know the number. Excel will tell you without changing your sheet.

1. Click the column letter, or select just the cells you care about.
2. Look at the status bar along the bottom-right of the Excel window.
3. Read the Sum figure. Average and Count sit next to it.

If you do not see Sum there, the display is switched off. Right-click anywhere on the status bar and select Sum in the list that appears. This is a per-user setting, so it can be off on a fresh install or on a colleague's machine.

Rule to remember: if you only need to read a total once, use the status bar. Nothing is written to the file, so nothing can break later.

Method 5: Adding a total row using the Quick Analysis tool

The Quick Analysis tool is a fast way to apply common operations such as summing data. It does not require you to write formulas or use the Table feature.

1. Select your data

Highlight the range of data you want to analyze. Include the numbers you want to total. Quick Analysis needs a selection of two or more cells, and it is not available in Excel for the web.

2. Activate the Quick Analysis tool

After selecting the data, look at the bottom-right corner of the selection for a small Quick Analysis icon (it resembles a grid or lightning bolt). Click this icon or press Ctrl + Q to open the Quick Analysis tool.

3. Navigate to the Totals tab

The Quick Analysis tool displays options such as Formatting, Charts, Totals, Tables, and Sparklines. Click the Totals tab to see options for common totals like Sum, Average, Count, and more.

4. Choose the Total option

From the Totals tab you can select several total options for your data:

• Sum: Adds up all the numbers in the selected columns or rows.
• Average: Calculates the average of the selected data.
• Count: Counts the number of entries in the selection.
• % Total: Shows each value as a share of the total.
• Running Total: Builds a cumulative total down the column.

The first row of icons applies the total below your data as a total row. The second row of icons applies it to the right as a total column. After selecting one, Excel writes a normal SUM formula for you.

Method 6: How to total an entire column in Excel

If rows keep getting added to the bottom of your data and you do not want to edit a range every week, total the whole column instead:

=SUM(B:B)

That adds every number in column B, including rows you have not typed yet. Two prerequisites matter here. First, the formula cannot live in column B itself, or Excel returns a circular reference warning and shows 0. Put it in a different column, or in a summary cell above the data. Second, any stray number elsewhere in column B, such as a year in a note at the bottom, gets added too.

To total a column from a starting row down to the end of the sheet, use =SUM(B2:B1048576). This skips the header row and still catches everything below.

Which method should you use?

How to total a row in Excel instead of a column

Everything above totals downward. To total a row in Excel, the formula runs sideways instead:

=SUM(B2:E2)

Put that in F2 and it adds cells B2 through E2. To fill it down for every row, select F2 and double-click the fill handle. Excel copies the formula to the bottom of the adjacent data and shifts the row numbers as it goes, so F3 becomes =SUM(B3:E3).

In a table, the same job is done by adding a calculated column. Type the formula once in the first cell of a new table column and Excel fills the whole column instantly.

How to total multiple columns at once

You do not need one formula per column. Select the empty row directly underneath all your columns of data, then press Alt + = once. Excel writes a separate AutoSum into every selected cell, each pointing at its own column.

If you want a single grand total across several columns instead, give SUM a rectangular range:

=SUM(B2:D10)

That returns one number covering columns B, C, and D across rows 2 to 10. To use SUM in Excel with multiple non-adjacent columns, separate the ranges with commas: =SUM(B2:B10,D2:D10).

Add a total row to this table and display the average for a column

This exact task turns up in Excel coursework and certification practice tests, and the wording trips people up because it asks for two things at once. Here is the sequence.

1. Click any cell inside the table.
2. Open the Table Design tab and select Total Row.
3. Click the total cell underneath the column you were asked about, for example a Cost column.
4. Open the drop-down arrow that appears inside that cell.
5. Choose Average instead of Sum.

The cell now holds =SUBTOTAL(101,[Cost]) and shows the average rather than the total. The rest of the total row is unaffected, so one column can show an average while another shows a sum. If you are not working in a table, the equivalent formula is =AVERAGE(C2:C13).

Total row missing, grayed out, or showing the wrong number

Doing this in Google Sheets instead

Google Sheets has no Total Row check box, so the steps are different. If you are working there rather than in Excel, follow our guide to how to sum a column in Google Sheets instead.

Final thoughts on adding a total row in Excel

The best way to add a total row in Excel is the Table feature. Select your data, press Ctrl + T to create a table, then select the Total Row box in the Table Design tab. Excel adds the totals row and keeps it correct as you edit the data. If you just need a fast one-off number, press Alt + = under the column, or select the column and read the Sum from the status bar.

You can visit our homepage for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel/Google Sheets Templates!

FAQ on adding a total row in Excel

Can I add a total row without converting my data to a table?
Yes. Click the cell below your numbers and press Alt and = to run AutoSum, or type a formula such as =SUM(B2:B6). You lose the automatic updating that a table gives you, but you keep full control over where the total sits.

Will the total row update automatically when I add new data?
If you use the Table feature, yes. Type into the row directly under the last data row and Excel expands the table and pushes the total row down. With manual SUM formulas you have to widen the range yourself.

What formula does the Excel total row use?
It uses SUBTOTAL, not SUM. A Sum total row contains =SUBTOTAL(109,[Column]). The 109 tells Excel to add the values while ignoring rows hidden by a filter.

What is the shortcut to sum a column in Excel?
Alt and = on Windows, or Command, Shift and T on Mac. Click the empty cell below the column first, then press the shortcut and Enter.

How do I total an entire column in Excel?
Use =SUM(B:B). The formula must sit outside column B, otherwise Excel reports a circular reference and returns 0.

Why is my Total Row check box grayed out?
The sheet is protected or the file is in Protected View. Click Enable Editing, or go to Review and unprotect the sheet, then the check box becomes available.

How do I total a row in Excel rather than a column?
Put =SUM(B2:E2) in the cell at the end of the row. Double-click the fill handle to copy it down for every row in your data.

Related Articles

How to Remove Page Breaks in Excel

How to Combine 2 Columns in Excel With a Space

How to Swap Columns in Excel

How to Remove a Table in Excel

How to Convert Text to Numbers in Excel