One of the most common formulas you may create in Excel is a formula that finds the sum of a column of numbers. Excel enables you to create AutoSum formulas, which are SUM or other formulas you create quickly, with just a few keystrokes or clicks of the m
- [Instructor] One of the most common formulas you will create in Excel is a formula that finds the sum of a column of numbers. Excel enables you to create auto-sum formulas, which are SUM, or other formulas, you create quickly with just a few key strokes or clicks of the mouse. I'll demonstrate how to create them in this movie. My sample file is the auto-sum workbook, and you can find that in the chapter 5 folder of the exercise files collection. In this workbook, I have yearly revenue by quarter for the years 2016, 2017, and 2018.
Let's say that I want to find the sum of the values for 2016, and those would be here in cells B-5 through B-8. I have the total table in cell A-9, so I will click in cell B-9, which will give me my auto-sum for year 2016, and then I will use the keyboard shortcut COMMAND+SHIFT+T, so COMMAND+SHIFT+T, and I get my auto-sum.
My formula appears to be correct, but remember that I wanted to add up the values in B-5 through B-8, and if we look at the formula bar, we see that in fact, we are summarizing B-4 through B-8. Why did that happen? The reason it happened is that the label in cell B-4, even though it represents a year, is a number. That means that it was included in this calutation. You need to be very careful when you create auto-sum, or other formulas, to make sure that labels are not included with your data.
In this case, it's a relatively small error, but it is an error, nonetheless. Will cell B-9 still selected, I'll press DELETE, to get rid of the formula. To prevent this error from occurring when I create auto-sum formulas, I will edit the values in B-4, C-4, and D-4, so that they're text. So I'll double-click in cell B-4, and I'll type F-Y for fiscal year, then press TAB, and I'll edit the value in C-4, so it says FY 2017, do the same thing for 2018; F-Y ENTER, or RETURN, there we go.
Now if I click in cell B-9, and press COMMAND+SHIFT+T, I get the auto-sum formula, and it adds up correctly, the sum of B-5 through B-8. You can now copy your auto-sum formula using any of the techniques I've described elsewhere in this course. The way that I like to do it is to drag the fill handle, which is the green square at the bottom right corner, move my mouse pointer over the fill handle for cell B-9, drag to the right, and I get my totals.
You can also create auto-sum formulas using functions other than SUM . So I'll click cell B-9, and then on the home tab of the ribbon, I'll go almost all the way to the right, and I'll click the AutoSum button's down arrow. And here you see you can have sum, average, count, maximum, minimum, or more functions. In this case, let's say that I want to find the maximum, so I'll click that. The formula in cell B-9 is changed to MAX(B5:B8).
Press return, and I get the largest value, which is, in fact, for Q1 in cell B-5. Creating auto-sum formulas quickly helps you summarize your data efficiently, and get on with the more demanding tasks facing you in your workbook.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.