How to Put a SUM Formula in an Excel Column of Numbers

Formulas & functions · Updated November 20, 2026

To total numbers in an Excel column, select the cell where the answer should appear, type a SUM formula such as =SUM(B2:B6), and press Enter. This example adds the values in B2 through B6. You can also use AutoSum to have Excel suggest a range. Before starting, identify which cells contain the numbers and choose a result cell that is not included in the range being added.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows. The steps are similar in Excel for Mac and Excel for the web, although ribbon placement and some keyboard shortcuts differ.
  • A column of numbers to add, ideally with a heading in the first row and the values directly below it.
  • A blank cell for the total. If you put the total in the same column as the numbers, use a range that stops above the total cell.

Step-by-Step

  1. Find the cells you want to add. Look at the column letter and the first and last row containing the data. For example, if the amounts are in B2:B6, B1 might be a heading and B7 could be a suitable place for the total. Decide whether you want a fixed range of current entries or the entire column. A specific range is usually clearer and helps avoid including unrelated numbers elsewhere in the sheet.
  2. Select the result cell. Click the blank cell where the total should appear. In the example layout, click B7. If you would rather keep the result separate from the source data, select an empty cell in another column, such as D2. Avoid putting a whole-column total formula inside the same column it sums: including the formula cell in its own range can create a circular reference.
  3. Enter the SUM formula. With values 120, 85, 40, 60, and 95 in B2:B6 and the total in B7, enter =SUM(B2:B6). The formula adds those five cells and returns 400. The colon means the range includes every cell from the first reference to the last, including both endpoints. Type the equals sign first so Excel treats the entry as a formula, then press Enter.
  4. Check the result and the selected range. Click the answer cell and look at the formula bar to confirm the formula refers to the intended cells. If the values are 120, 85, 40, 60, and 95, the displayed result should be 400. The result may appear with a currency symbol, decimal places, or a thousands separator because of the cell’s number format; formatting changes how the answer looks, not the sum itself.
  5. Use AutoSum if you prefer not to type the formula. Select the blank cell below a continuous list of numbers, such as B7 when the values occupy B2:B6. On the Home tab, choose AutoSum (the Σ button) in the Editing group. Excel proposes a range; check that its selection covers B2:B6 and does not include the heading or an unrelated cell. Press Enter to accept it. AutoSum is also available on the Formulas tab. If Excel selects the wrong cells, cancel or edit the proposed range before accepting.
  6. Use the Windows shortcut when the range is straightforward. With the total cell directly below the numbers selected, press Alt+= (Alt and the equals key). Excel inserts a proposed SUM formula. Inspect the outlined cells, then press Enter. On a Mac, use the AutoSum command on the Home or Formulas tab rather than assuming the Windows shortcut applies; keyboard mappings can differ by keyboard and Excel version. In Excel for the web, the ribbon’s AutoSum command is a reliable option.
  7. Choose between a fixed range and a whole-column reference. If values are in B2:B6 and the answer is in D2, enter =SUM(B2:B6); the result is 400. To add every numeric cell in column B while placing the answer outside that column, use =SUM(B:B) in D2; with only the five example numbers in B, it also returns 400. A whole-column reference can include later entries added anywhere in B, but it can also include values you did not mean to count. Do not put this whole-column formula in column B itself, because it would include its own cell.
  8. Extend a total to other columns, if needed. Suppose each row has three amounts in C, D, and E, and you want one row total in F. With 10, 20, and 30 in C2:E2, select F2 and enter =SUM(C2:E2); it returns 60. To calculate row totals for more records, use the fill handle: select F2, point to the small square at the lower-right corner of the selection, and drag down. Excel adjusts the row references, so the next row uses C3:E3. This makes a separate sum for each row; it is different from one grand total for a column.
  9. Format the answer if that helps readability. Select the result cell and use Home > Number to choose a suitable number format, such as Number or Currency. For example, if the formula returns 400, applying Currency may show a currency symbol and two decimal places. Keep the underlying formula intact. If the answer appears as a date or with unexpected decimal places, change its number format rather than rewriting a correct SUM formula.

Common Mistakes

Including the total cell in its own range. If B2:B6 contain the data and B7 contains the total, the range should stop at B6. A formula in B7 that adds B2:B7 refers to itself. Excel may report a circular reference or show an unexpected result if iterative calculation is enabled. Correct the ending row so the formula excludes the cell that holds it.

Choosing the wrong range with AutoSum. AutoSum guesses based on nearby cells. A blank row can make it stop early, while nearby numbers can make it include too many cells. Always check the colored outline or the references in the formula before pressing Enter. A heading or a blank cell is not necessarily a problem for SUM, but an incorrect starting or ending row can omit data.

Typing a range with the wrong punctuation. A continuous range uses a colon between its endpoints, as in =SUM(B2:B6). A comma separates individual arguments or separate ranges. Do not replace the colon with a comma when your intention is to include every cell between two rows. Excel’s regional settings can affect separators in some formulas, but a basic one-range SUM formula needs no argument separator.

Expecting text to be added as a number. SUM adds numeric values and ignores text in referenced cells. A number stored as text may therefore be left out even though it looks like a number. This can happen after importing data or when an entry has a leading apostrophe. Check the source cells if the result is lower than expected.

Using a total formula where row-by-row calculations are wanted. =SUM(B2:B20) produces one total for that range. It does not create a separate total beside each record. For row totals, select a result cell on the same row and sum that row’s cells, then fill the formula down as described above.

Tips

  • Use a clear label such as Total in the cell beside the answer. A label makes it easier for someone else to tell what the number represents, especially when a worksheet has several sums.
  • Keep source values together and avoid placing notes or unrelated numbers inside the range. A formula such as =SUM(B2:B100) in D2 will include numeric entries anywhere in B2:B100, including numbers added later. With the five example values in B2:B6 and all other cells in B7:B100 blank or nonnumeric, the result is 400. This bounded range allows room for new rows without summing the entire column.
  • If the data is an Excel table, use its Total Row feature when you want a total that follows the table as rows are added. Select a cell in the table, go to Table Design, and turn on Total Row. In the total row, open the cell’s dropdown and choose Sum. The exact displayed controls can vary slightly by Excel version.
  • Keep the formula separate from number formatting. Applying a currency or number format changes the display, while the SUM formula still calculates from the cell values. This makes it easier to present a result neatly without altering the underlying data.
  • For a quick check, select the numeric cells and look at the status bar at the bottom of Excel. If Sum is shown, compare it with the formula result. If the Sum indicator is not visible, right-click the status bar and enable Sum. This is a check, not a replacement for a worksheet formula.

Troubleshooting

The formula is displayed in the cell instead of a number. The cell may be formatted as Text, or Excel may be treating the entry as text. Change the format to General using Home > Number, then edit the cell and press Enter again. Confirm that the entry starts with an equals sign and that Show Formulas is not turned on. The Show Formulas command is on the Formulas tab; toggling it changes whether formulas or their results are displayed.

The answer is zero or lower than expected. Check that the formula references the correct column and rows. Then inspect values that may be stored as text. Select a suspicious cell: a warning icon may offer Convert to Number. You can also re-enter the value as a number, or use Data > Text to Columns to convert imported text where appropriate. Be careful with identifiers such as postal codes, which may look numeric but should remain text.

Excel reports a circular reference. Find the cell holding the total and remove it from the range. For example, if the answer is in B7, sum B2:B6 rather than B2:B7. If the formula needs to total the entire B column, put the result in a different column or use a bounded range that excludes the result cell.

AutoSum misses a value or includes an extra one. Replace the proposed range by selecting the reference inside the formula bar and entering the correct first and last cells. For example, if the intended source is B2:B6, make sure the formula refers to exactly that range. Press Enter after correcting it, then compare the result against the data.

The result looks wrong but the formula is correct. Check the cell format and the source values. A value displayed with no decimal places may still contain decimals, and a date-formatted result may show a serial number as a date. Use Home > Number to change the answer cell to General or Number. Also verify that hidden or filtered rows are not being treated as you expect: SUM includes numeric cells in a referenced range even when rows are hidden or filtered out.

Frequently Asked Questions

How do I sum an entire Excel column?
Put the answer outside the source column and use a whole-column reference. If the values are in column B and the result goes in D2, enter =SUM(B:B); with the five example values in B2:B6 and no other numbers in B, the answer is 400. If the formula is in column B, use a range that excludes its own cell instead.

Can I add only part of a column?
Yes. Specify the first and last cells you want to include. If B2:B6 contain 120, 85, 40, 60, and 95, enter =SUM(B2:B6) in B7 or another empty cell; it returns 400. Adjust both row numbers to match your data.

Can I use AutoSum for a column with blank cells?
Yes, but inspect its proposed range. A blank row within the data may cause Excel’s guess to stop before later values. You can manually edit the range so it spans the intended cells; SUM can include blank cells without a problem.

Does SUM include hidden rows?
Yes. SUM includes numeric values in the referenced cells even when their rows are hidden or filtered out. If you specifically need a total that changes based on filtering, use SUBTOTAL instead and check its function number and range for the result you want.

Will the total update when I change a value?
Normally, yes. Excel recalculates formulas automatically when a referenced value changes. If the result does not update, check Formulas > Calculation Options and select Automatic. A formula that uses a fixed range will not automatically extend its endpoint when new data is entered below that range; use a table or revise the range to include the new rows.

Is there a difference between typing SUM and pressing AutoSum?
No difference in the underlying calculation if both produce the same formula and range. Typing SUM gives you direct control over the references, while AutoSum is a quick way to insert a suggested formula that you should verify before accepting.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.