To calculate a row total in Excel, add a SUM formula in the cell where you want the answer. For example, with four amounts in B2:E2 and the total in F2, enter =SUM(B2:E2); if the amounts are 25, 40, 35, and 25, the result is 125. You can copy the formula down to total other rows or use AutoSum to insert it for you.
A row total adds values from left to right, such as monthly sales, quantities, or expense amounts. The steps below are for Microsoft Excel for Microsoft 365 on Windows. The core formula works in Excel for Mac and Excel for the web too, though ribbon placement and keyboard shortcuts can differ.
What You’ll Need
- An Excel workbook with the values you want to add arranged across a row.
- An empty cell in which to display each total. For a total at the right of the example range B2:E2, use F2.
- A quick check that the cells contain numbers, not number-like text. SUM ignores text in a referenced range, which can make a total smaller than expected.
Step-by-Step
- Identify the values to include. Check the row headings and decide which columns belong in the total. If B2:E2 contain January through April amounts, for instance, include all four cells. Do not include a label, a percentage that should not be added, or an existing subtotal unless you intend to count it.
- Choose the total cell. Click the empty cell immediately to the right of the values. For the example, that is F2. Keeping totals in a separate column makes the worksheet easier to scan and prevents the total from being mistaken for another input value.
- Enter the SUM formula. With values in B2:E2 and the answer intended for F2, type
=SUM(B2:E2)in F2 and press Enter. The colon means “all cells from B2 through E2.” Excel should display 125 for the example values 25, 40, 35, and 25. The formula appears in the formula bar when you select F2, while the calculated result appears in the cell. - Use AutoSum if you prefer not to type. Select the empty cell immediately after the row values, F2 in this example. On the Windows ribbon, choose Home > Editing > AutoSum, then inspect the range Excel highlights. If it covers the intended cells, press Enter to accept it. If it selects the wrong cells, correct the range before pressing Enter. AutoSum is also available on the Formulas tab. On Mac or Excel for the web, use the AutoSum button on the ribbon if the same Home-tab placement is not shown; the formula itself is unchanged.
- Copy the total to other rows. Select F2 and point to the small square at its lower-right corner, called the fill handle. Drag it down alongside the rows you want to total. Excel adjusts the row references as it fills: the next row uses its own values, not row 2 again. You can also copy F2, select the destination cells below it, and paste. In Windows Excel, Ctrl+D fills the selected range downward when the top cell contains the formula; on Mac, use Command+D.
- Check the results and save. Compare one or two totals with a manual addition, especially in a new worksheet. Click a total cell to confirm its formula refers to the correct row and columns. Save the workbook with Ctrl+S on Windows or Command+S on Mac.
If the values are not next to one another, SUM can add separate cells too. With amounts to include in B2, D2, and F2, and the result planned for G2, use =SUM(B2,D2,F2). In locales or Excel installations configured for a different list separator, Excel may require semicolons instead of commas; follow the separator Excel accepts in your formulas.
Common Mistakes
- Including the total cell in its own range. If the total goes in F2, do not sum B2:F2. That range contains F2 itself and creates a circular reference. Sum only the input cells to the left of the total.
- Leaving out a value at either end. A range that starts at C2 rather than B2, or stops at D2 rather than E2, can calculate without an error while still being wrong. Look at the highlighted range when using AutoSum and verify the first and last cells.
- Adding cells that should not be added. A row may contain units, a discount rate, a note, or an already calculated subtotal alongside the amounts. SUM will add numeric cells included in the range, whether or not they belong in your intended total. Select only the relevant columns.
- Using plus signs for a long list. A short addition expression can work, but it is easier to miss a cell when manually joining many values. SUM with a range is clearer and less likely to omit an entry when the range changes.
- Expecting SUM to count text as a number. Values imported from another system may look numeric but be stored as text. SUM generally ignores text in referenced cells, so the displayed total can be too low. Check the cell alignment, warning icon, and number format, then convert the entries to numbers where appropriate.
Tips
Keep a consistent layout: input values in a continuous block, the row total in a clearly labeled column, and no blank or unrelated columns inside the range. A heading such as “Total” above the results helps other people understand what the figures represent. If you add a new input column later, update the formula range so that the new values are included.
Format the result to match the data. Select the totals and use the number-format controls on the Home tab to display currency, a number of decimal places, or a thousands separator. Formatting changes how a value appears, not the underlying arithmetic. For example, a currency format does not convert an amount from one currency to another.
For a one-time total, AutoSum is convenient. For a worksheet you will update regularly, keep the formula in place and enter new values in the same columns. In an Excel table, a formula entered in a calculated column can fill down automatically; check the first resulting rows to ensure the table has copied the formula as intended.
Remember that SUM includes hidden values in an ordinary range, including rows hidden by a filter. If you want a total that changes to show only visible rows after filtering, use the subtotal option below rather than an ordinary SUM. This distinction matters for reports where a filtered view is meant to control the displayed total.
Troubleshooting
- The result is zero or too small: Confirm the formula range first. Then inspect the input cells for numbers stored as text, blank-looking cells containing text, or values outside the selected range. If a cell has a warning icon, use its conversion option when available, or re-enter the value as a number. Changing the display format alone may not convert text into numeric data.
- The cell displays the formula instead of an answer: Check whether the cell is formatted as Text. Change its format to General or Number using Home > Number Format, then edit the cell and press Enter so Excel evaluates the entry. Also check that the formula begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles formula display; on Mac, use the equivalent shortcut if assigned or check the Formulas tab.
- AutoSum selects the wrong cells: Excel makes a guess based on nearby values. Before accepting, inspect the outlined range and drag over the correct cells if needed. You can also cancel and type the range yourself. Do not assume that the nearest block is the right block if the row contains gaps or other figures.
- The total does not change after editing values: Make sure calculation is set to Automatic under Formulas > Calculation Options > Automatic. If the workbook is set to Manual, choose Calculate Now on the Formulas tab or switch to Automatic. Also verify that the edited values fall inside the formula’s range.
- You need a filtered total of visible rows only: With values in B2:E2 and a result cell in F2,
=SUBTOTAL(109,B2:E2)adds the visible numeric cells in that range and excludes rows hidden by filtering or manually hidden. This is most useful when filtering a list of records; for a single ordinary row total, SUM is usually simpler.
Frequently Asked Questions
Can I calculate a row total without using AutoSum?
Yes. Type a SUM formula directly into the result cell, or use AutoSum to create the formula. The result is still a formula that recalculates when the inputs change.
How do I total every row in a list?
Enter the correct formula in the first total cell, then drag its fill handle down or copy and paste it into the remaining total cells. Check that each row’s formula points to the values on that same row.
Can SUM add cells that are not beside each other?
Yes. Separate cell references with commas in the SUM function, as in the nonadjacent-cell example above. Use the separator expected by your Excel regional settings if commas are not accepted.
Why does my total skip a number that looks correct?
The entry may be stored as text, or it may be outside the formula’s selected range. Confirm that the formula includes the cell, then convert text-formatted numbers to numeric values. A currency or decimal display format by itself does not guarantee that an entry is a number.
Does SUM include hidden rows?
Yes. A normal SUM includes values in hidden rows. If you are filtering a list and want the total to reflect only visible records, use SUBTOTAL with function number 109 as shown above.
Will these steps work in Excel for Mac or the web?
Yes. SUM formulas and filling formulas down work in both. Ribbon labels or placement can vary, so use the AutoSum button on the Home or Formulas ribbon if necessary. Keyboard shortcuts can differ on Mac; use the ribbon commands when a Windows shortcut does not apply.
Related
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort an Excel Column Highest to Lowest: Quick StepsTo sort numbers in Excel from highest to lowest, select a cell in the column and choose Data…
- IRR Function in Excel: Calculate an Investment ReturnUse Excel’s IRR function to estimate the rate of return for a series of cash flows that occur…
- Excel Max Index: Row, Column, and INDEX Function LimitsIn current Microsoft Excel for Microsoft 365, the largest worksheet row index is 1,048,576, and the largest column…
- Lock a Number in Excel: Keep It Fixed in FormulasTo keep a number fixed when you copy a formula, make its cell reference absolute by adding dollar…
- Excel Formula to Round to the Nearest Whole NumberTo round a number in Excel to the nearest whole number, use the ROUND function with zero decimal…
- Excel Formula or Operator? Understand the DifferenceIn Excel, a formula is an expression that calculates a result, while an operator is a symbol that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.