Learn how to use the SUM function in Excel

Simplify your formula

In the previous module, we saw how to build a formula.

But if you need to add many cells, building a formula like =B2+C2+D2+E2+F2 causes real problems:

• It takes a very long time to select each cell 😡
• It's a common source of errors — easy to forget a cell 😱
• Formulas like this are difficult to maintain 😤

The SUM function solves this: it adds up multiple values across many cells in one go. Just follow these steps:

1. Write =SUM
2. Then open parenthesis
3. Select the range of cells to add: B2:F2
4. Close parenthesis
5. Press Enter

The result is exactly the same — but the formula was much simpler to build 😀 👍

Copy the SUM

When you copy the formula using one of the methods shown in this tutorial, the sum is calculated for each row individually.

• Here, the formula was copied downward.
• So each reference's row number increased by one.
• This way, you only need to write the formula once for the whole sheet.

Example: sum for Monday

What formula should you write to calculate the sum for Monday?

To calculate the total Monday sales, select the range containing all sellers' sales, such as B2:B9.

Copy the SUM function for the other columns

Copy the formula to the right again to get the sum for each day of the week — this time, the column references change in the formulas.

Shortcut to call the SUM function faster

The SUM function is used so much in Excel that it can be called from the ribbon or by keyboard shortcut.

From the Ribbon

1. Select the cell where you want the total to appear — in this example, B10
2. Click the Σ icon (AutoSum) from the Home tab

Clicking the Σ symbol does the following:

1. Selects all cells with numbers (excluding text and empty cells)
2. Displays the function in the formula bar
3. Press Enter to validate the result

Keyboard shortcut

Instead of clicking the Σ symbol, you can get the same result with the keyboard shortcut Alt+= 😉

Use the Quick Analysis tool to do a SUM

Quick Analysis is the name given to the icon that appears after making a selection in your spreadsheet.

1. Select all cells with numbers
2. The Quick Analysis icon is displayed at the bottom right
3. Select the menu Totals
4. Finally, select the Blue Sum option (the icon has the symbol Σ and a blue line for the Total)

 The sum of all columns is instantly calculated and added to the row below the selected range.

Same trick for rows

Now, if you want to sum all the cells of the same row, the technique is the same.

1. Select the cells you want to add
2. Click the Quick Analysis icon
3. Go to the Totals tab
4. Select the Yellow Sum option (symbol Σ and a yellow column for the Total)

The result is as follows.

Now practice it yourself

Time to put the SUM function to work on a real case: calculate a week of ticket revenue, full-price and half-price, day by day, then the grand total for the week.

Continue learning

Keep building your Excel skills with the rest of this tutorial series:

• Tutorial - Adjusting the Size of rows and columns
• Tutorial - How to make calculations in Excel
• Tricks to Insert, Delete, Hide Rows and Columns
• Tutorial - All Excel tricks to Copy-Paste
• Tutorial - Moving cells or columns with the mouse
• Tutorial - The Numbers Format
• Tutorial - Excel Alignment text tips
• Learn the secrets of Sorting Data with Excel
• Excel Tutorial - Filtering your data
• Manipulate Text in Excel without formula
• Excel Tutorial - Creating a dropdown menu
• Learn how to use the SUM function in Excel
• Tutorial for the Excel Functions AVERAGE, MIN, and MAX
• Learn how to use the Dollars in an Excel formula