Software

​Unleash the power of Excel's AutoSum tool

You know AutoSum is a helpful tool, but it's more flexible and valuable than you might realize. This walk-through introduces a variety of timesaving tricks.

dragonimagesistock-497353650.jpg

Image: iStockphoto.com/DragonImages

Most users quickly learn how to use Excel's AutoSum to total a row or column of values. For better or worse, some users never move beyond that basic task; they never learn that AutoSum can enter functions for an entire data set with a quick selection and a click, let alone evaluate noncontiguous ranges! In this article, I'll review the basic AutoSum task. Then, I'll show you how to put this feature to work by entering multiple functions with almost no effort.

I'm using Excel 2016 on a Windows 10 64-bit system, but most of these techniques will work in earlier versions. You can download the demonstration .xlsx or .xls file or work with your own data. AutoSum is available in 365's browser version, but it's limited because you can't build multiple-range selections.

A single row or column

Most everyone knows that you can click a blank cell adjacent to a row or column of values and that AutoSum will total those contiguous values. For example, if you select G3 (Figure A) and then click AutoSum and press Enter, Excel will enter a SUM() function that references C3:F3—January's values. (You can also press [Alt]+==.)

Figure A

excelautosuma.jpg
Sum a single row or column of contiguous values.

Excel knows that the value in B3 is text and stops looking for values to evaluate. The feature works similarly on columns of contiguous values. To the right in Figure A, AutoSum entered a SUM() function that references the values in column C—Smith's values. In both cases, there's no need to manually type the function or the cell references; AutoSum does everything.

SEE: How to use built-in Excel features to find duplicates

You can also select a row or column of values and the adjacent blank cell. For example, selecting C3:G3 and clicking AutoSum will insert the same SUM() function, as shown in Figure B. Similarly, if you select C3:C15, Excel will insert the appropriate SUM() function for Smith in C15. Again, Excel stops including cells when it encounters a non-numeric value, and it knows to insert the function in the adjacent blank cell. Selecting the values and a blank adjacent cell has the advantage of letting you choose the values you want to evaluate when you don't want to include every contiguous value. For instance, you might select C12:C15 to return the total for the last quarter of the year.

Figure B

excelautosumb-2.jpg
You can select values and an adjacent blank cell.

Usually, you work with more than a single row or column, and fortunately, AutoSum adapts well to multiple rows and columns. Simply select an adjacent range of blank cells. Using our example data set, you could select G3:G14 and click AutoSum. The result is a column of monthly totals in column G. Similarly, if you select C15:G15, AutoSum inserts a row of personnel totals, as shown in Figure C. G15 is the grand total.

Figure C

excelautosumc.jpg
Sum a range of rows or columns.

This is the extent to which most users apply AutoSum:

  • Selecting an adjacent cell or a range of adjacent cells to sum contiguous values.
  • Selecting a row or a column of values plus the appropriate adjacent cells to sum the selected values.

There's more to AutoSum than this though!

Expanding on range

To sum monthly and personnel values using the above methods requires at least two clicks to AutoSum. You can produce the same results with one click as follows:

  1. Select G3:G14.
  2. Hold down the [Ctrl] key. (Don't release it.)
  3. Select C15:G15 to create a multiple-range selection (Figure D).
  4. Click AutoSum to enter monthly and personnel totals with one click to AutoSum.

Figure D

excelautosumd.jpg
AutoSum enters both sets of SUM() functions with one AutoSum click.

As efficient as that is, there's still a quicker way to accomplish this—perhaps you've thought of it already? You can select the entire data set plus the appropriate adjacent blank cells. Using our example data set, you'd do the following:

  1. Select C3:G15.
  2. Click AutoSum to enter monthly and personnel totals, as shown in Figure E.

Figure E

excelautosume.jpg
A one-task select can produce monthly and personnel totals.

The results for both selection methods are the same, but selecting a contiguous range plus the blank cells is more efficient. Losing a click here or there isn't a big deal; knowing how to specify exactly what you want to evaluate is the real prize, as you'll see in the next section.

SEE: How to create an effective, user-friendly slicer in Excel

Multiple noncontiguous ranges

Our last two examples produce two sets of calculations (monthly and personnel) by evaluating a contiguous data set. If you thought that was flexible, you're in for a treat. Figure F shows several noncontiguous ranges, and we'll expand on what you've learned to return quarterly and monthly subtotals at once. (Please forgive me for the somewhat contrived example.)

Figure F

excelautosumf.jpg
A multiple noncontiguous data set.

We want quarterly totals for each person (columns F, J, N, and R) and monthly totals and quarterly totals in row 7. That's a lot of work, but not for AutoSum. The secret is to start with noncontiguous selections as follows:

  1. Select F3:F6.
  2. Hold down the [Ctrl] key and don't release it.
  3. Select J3:J6, N3:N6, and R3:R6, all while holding down the [Ctrl] key—but don't release [Ctrl] yet!
  4. Select C7:R7 (Figure G).
  5. Click AutoSum to see the resulting totals shown in Figure H.

Figure G

excelautosumg.jpg
Select a multiple noncontiguous range.

Figure H

excelautosumh.jpg
AutoSum handles the selection fine.

At this point, you should be seeing a trend—by selecting the right range or ranges, you can reduce the number of times you click AutoSum and include only the values you want to evaluate.

One more trick

Even though we've stretched AutoSum quite a bit, we're not done. We have quarterly totals and subtotals by the month and by personnel. Now let's use AutoSum to return the overall average sale. Ordinarily, this process would be complicated by the noncontiguous ranges. Fortunately, you can take advantage of AutoSum's ability to evaluate multiple noncontiguous ranges.

First, select the cell where you want to display the average—C9 for our example. Then, click the AutoSum option's dropdown and choose Average (Figure I).

Figure I

excelautosumi.jpg
Choose Average from AutoSum.

Next, select C3:E6 (the first quarter's values) and hold down the [Ctrl] key. (Don't release it.) Add G3:I6, K3:M6, and O3:Q6 (Figure J) to the noncontiguous selection. Press [Enter] to commit the function and view the results, shown in Figure K.

Figure J

excelautosumj.jpg
Create a multiple noncontiguous selection of the values you want to evaluate.

Figure K

excelautosumk.jpg
The AVERAGE() function evaluates all of the selected values even though they're in noncontiguous ranges.

Summing it up

AutoSum is extremely flexible; it can handle a variety of selection scenarios. All you have to do is put them to use.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read...

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks