Formulas Quick Start Part 2 of 4: SUM & AUTOSUM
Assign a 'primary' menu

Formulas Quick Start Part 2 of 4: SUM & AUTOSUM

Excel formulas quick start guide for beginners

This post is all about using SUM.

When you are working with a lot of data, creating formulas manually can take a long time and is prone to mistakes.

There is a way to make formulas easier and quicker.

Prefer to watch instead of read? ​

Feature Download: Grab 2 fantastic giveaways - (1) a printable checklist showing 7 steps to perfect formulas and (2) a printable cheat sheet showing 15 of the best Excel functions to get you started

Using Excel Functions

Excel has over 450 pre-programmed functions. The simplest one that everybody learns first is SUM. SUM adds things up.

At the other end of the spectrum, the top-end functions are used by statisticians, mathematicians, engineers and scientists and tend to be quite specialised. However, there are still many that can be used by ‘ordinary’ folk!

A function is used within a formula to simplify it.

For example,

=A1+B1+C1+D1+E1+F1+G1+H1

can be replaced with

=SUM(A1:H1)

This is easier to read and easier to maintain.

To see the full benefit, scale it up. Imagine if you had to add up 25 cells. Or 1,000 cells!

Excel even does the hard work of setting it up for you. You are given a tool called AutoSum.

Using AutoSum

The AutoSum button is ideal for novices who wish to learn and use some simple Excel functions. The AutoSum button provides a list of the five basic functions – SUM, AVERAGE, COUNT, MIN and MAX. The icon is the Greek Sigma symbol and looks like a sideways ‘M’ or a crooked ‘E’ (see below).

Using Autosum is quick and easy.

  1. Select the numbers to be added and the blank cell either to the right or underneath the selection.
  2. Click the Formulas tab.
  3. Click the AutoSum icon.
  4. Where to find AutoSum

    Figure 01: Where to find AutoSum

A SUM function is created in the blank cell (the last cell of your selected cell range). The selected cells are added together and the total is calculated.

AutoSum creates a SUM function in the first blank cell after the data

Figre 02: AutoSum creates a SUM function in the first blank cell after the data

How the SUM function works

By default the AutoSum tool creates a SUM function, which adds together the values in the selected cells. To see what the SUM function looks like, click on the cell that contains the total and study the formula bar.

The SUM function may be typed directly into a cell, rather than using AutoSum. This is quicker for experienced users.

The SUM function in the formula bar

Figure 03: The SUM function in the formula bar

Here’s the process to use once you’re a bit more confident:

  1. Select the blank cell where the total will be calculated (this is often - but not always - directly underneath the main data, or directly to the right of the main data).
  2. Type ‘=SUM(
  3. With the mouse, select the range of cells you want to add up. Never type in the cell or cell range by hand.
  4. Type the closing bracket.
  5. Press Enter.

The structure of a SUM function is =SUM(number1, number 2 …) where each number may be a constant (e.g. 5), cell reference (e.g. A1) or cell range (e.g. A1:A3).

  • Every item within the brackets is added up.
  • Number1, number2 etc. are called arguments. A SUM function may have up to 32 arguments, each separated by a comma.
  • Note that the function is preceded with ’=’ (as for formulas).
  • Note the notation for a range – two cell references with a colon (‘:’) in-between.

The following formulas are valid examples of the SUM function.

=SUM(1,2,3)

=SUM(A1,A2,A3)

=SUM(A1:A3)

=SUM(1,A1,A5:Z10)

5 different ways to start using AutoSum

#1 Select a single blank cell

  1. Select the blank cell underneath the data.
  2. Using AutoSum by selecting the blank cell underneath the data

    Figure 04: Using AutoSum by selecting the blank cell underneath the data

  3. Click the AutoSum icon.
  4. Excel looks around the blank cell and if it finds data it will display the data range in the formula.
  5. AutoSum guesses the data range to use and asks you 'Is this data range correct?'

    Figure 05: AutoSum guesses the data range to use and asks you 'Is this data range correct?'

  6. If the data range is correct, press Enter to confirm the formula.

#2 Select the data (and the blank cell - optional)

  1. Select the data you want to add up.
  2. Optional - you can also select the blank cell underneath - or not. Your choice.
  3. Using AutoSum by selecting the all the data cells and the blank cell underneath the data

    Figure 06: Using AutoSum by selecting the all the data cells and the blank cell underneath the data

  4. Using AutoSum by selecting the all the data cells and the blank cell underneath the data[/caption]
  5. Click the AutoSum icon. The total is added automatically.Using AutoSum by selecting the all the data cells and the blank cell underneath the data
  6. AutoSum calculates the answer based on the data you selected and puts the total straight into the

    Figure 07: AutoSum calculates the answer based on the data you selected and puts the total straight into the

#3 Use methods #1 or #2 across the worksheet

AutoSum works across the worksheet as well

Figure 08: AutoSum works across the worksheet as well

#4 Select 2 or more blank cells

When you select more than one blank cell going across the sheet, Excel knows that you want to add up columns of data, so it doesn’t ask you and puts the total straight into the cells.

  1. Select 2 or more blank cells underneath the data
  2. When you select more than one blank cell going across the sheet, Excel knows that you want to add up columns of data, so it doesn't ask you and puts the total straight into the cells

    Figure 09: When you select more than one blank cell going across the sheet, Excel knows that you want to add up columns of data, so it doesn't ask you and puts the total straight into the cells

  3. Click the AutoSum icon. The column totals are inserted automatically.
  4. Select 2 or more cells to the right of the data.
  5. And the same thing happens when you select blank cells going down the sheet

    Figure 10: And the same thing happens when you select blank cells going down the sheet

  6. Click the AutoSum icon. The row totals are inserted automatically.
  7. Row totals are inserted automatically

    Figure 10a: Row totals are inserted automatically

#5 Select all the data plus a blank column and/or row

Select all the data, plus a blank row and a blank column to generate all the column totals and row totals in one hit

Figure 11: Select all the data, plus a blank row and a blank column to generate all the column totals and row totals in one hit

And when you click AutoSum, all the totals are generated in one hit. Imagine how much time you could save if you have 50 columns and 1,000 rows

There you go! Imagine how much time you could save if you have 50 columns and 1,000 rows

Figure 12: There you go! Imagine how much time you could save if you have 50 columns and 1,000 rows

Let’s wrap

I hope you have seen how useful, how easy and how versatile the SUM function is.

Knock up a simple spreadsheet, enter some data - whatever you like - and use the AutoSum to generate some totals quickly.

Once you’re confident doing that, try selecting a blank cell and creating a SUM function manually, by typing it directly into the cell.

In part 3, I discuss 4 other basic formula functions that are also useful and easy to use - AVERAGE, MAX, MIN and COUNT

Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.

Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature
Jason Morrell blog signature
Checklist - 7 steps to perfect formulas

FREE CHECKLIST

7 Steps To Perfect Formulas

PLUS, GET A BONUS CHEAT SHEET: 
15 EXCEL FUNCTIONS TO GET YOU STARTED

Office Mastery is the digital arm and official blog of Two Rivers Software Training
Copyright © 2018 Two Rivers Software Training. All Rights Reserved Privacy Terms Contact
Like us on facebook Follow us on Twitter Watch us on YouTube Connect with Jason on LinkedIn
>