Preview
Use Formulas with AutoFill & AutoSum Features
Interpreting Data in a Cell
As we have seen thus far, text that is viewed in any cell is also shown in the formula bar. This is not always the case with data in a cell. If there is a number in a cell, the formula bar may show the same number as viewed in the cell. However, if the number in the cell is the result of a formula, we will not see that number in the formula bar.
In the graphic below, was the number 80 in this cell typed in or the result of a formula? How can you tell? Look at the formula bar contents to see how the number 80 was generated. Does it have an 80 in it? If so, the number was typed into that cell. If all you see is a formula, the number 80 is the result of the formula you’re viewing.
Which brings us to our second point about entering formulas into a cell:
If a formula is entered in a cell and you press the enter key, the only data you will see in that cell is the result of the formula, not the formula itself. The formula will be seen only in the formula bar (FIGURE 1).
How did the number 80 appear in this cell? To really know how it got there, look at the formula bar contents, Does the formula bar contain 80? The number 80 was typed into the cell (FIGURE 2)
Does the formula bar contain a formula? The number 80 in the cell was the result of a SUM formula which can be viewed in the formula bar. Does the formula bar contain a formula? The number 80 in the cell was the result of a SUM formula which can be viewed in the formula bar (FIGURE 3).
Entering Excel Formulas
There are over 400 formulas in Excel 2016. Many of them use the same type of format in terms of how they are entered. For the ones we are learning in this course, they all are entered in a cell the same way, as shown in the steps below.
Steps to Entering a Formula:
- To enter any formula, you must always begin by entering the equals symbol =
- Enter the formula name. For example, if you wanted to sum (add) three numbers of a range or table, you would enter SUM or sum; you can use upper or lower case.
- Enter the open parenthesis symbol (
- Enter the beginning cell address of the range of data to be summed. If the first address to be summed is cell address A1, then you would enter A1.
- Enter a colon :
- Enter the ending cell address of the range or table to be summed. If the ending address to be summed is cell address A3, then you would enter A3
- Enter the closed parenthesis symbol )
- Press the enter key to execute the formula.
- The result of performing steps 1 through 7 are shown below in FIGURES 4 and 5. FIGURE 4 shows what it looks like before Step 8; FIGURE 5 shows what it looks like after Step 8.
MOUSE METHOD: You can also perform steps 4, 5, and 6 automatically by selecting the first cell address in the range with your mouse, then drag the mouse to the last cell address in the range. Excel will automatically insert a colon between the cell addresses selected.
Remember: The result of any formula always appears in the cell after the enter key is pressed and the formula disappears from the cell. The formula can only be seen by looking at the formula bar contents.
There are three ways to select cells to be used in a formula. The formula procedure steps shown above is only one of the three ways we can enter formula data. We will learn all three ways in the upcoming practice exercise.
Practice Exercise 8 Use SUM, AVERAGE, MAX, MIN formulas with AutoFill & AutoSum features
STEP 1: (FIGURE 6) We are going to create a table of data and total it using the formula SUM.
Starting with cell address A1, enter the following data as shown in FIGURE 6.
After we are finished entering the data, we will use the SUM formula to sum Salesperson James sales for January, February, and March. Once we have his total, we will use a feature known as Autofill to sum the remaining salespersons.
We will also do the same process described above for formulas with: Average, Max, and Min.
Remember cell alignment rules: After entering text, the text will be left-aligned in the cell; numbers will be right-aligned in the cell.
STEP 2A: (FIGURE 7) Make cell B5 the active cell.
STEP 2B: (FIGURE 7) Enter the following into cell B5: =SUM(
THERE ARE 2 WAYS TO SELECT CELLS TO BE SUMMED:
OPTION 1:
STEP 2C: (FIGURE 7) Use the mouse to select the data to be summed. Move mouse to B2, click and drag mouse down through B4 (FIGURE 7).
OPTION 2:
STEP 2D: (FIGURE 7) Enter the cell addresses manually. Manually type: B2:B4
STEP 2E: (FIGURE 7) Enter the closed parenthesis in cell B5
Cell B5 and the Formula Bar now shows: =SUM(B2:B4)
STEP 2F: (FIGURE 8) Press Enter. James total will be in cell B5. James total should be 68.
STEP 2G: (FIGURE 9) Make cell B5 the active cell. Notice that although there is no longer a formula in cell B5, we can view the formula in the formula bar. In the Formula Bar, the formula we see is =SUM(B2,B4)
NOTE ON SUMMING NON-ADJACENT CELLS
To select non-adjacent cells to be summed, you can hold down the CTRL keywhile selecting them with the mouse.
OR
You can enter the cells to be summed manually. If we only wanted to sum cell B2 and B4 (they are not adjacent), instead of entering (B2:B4) enter (B2, B4) (FIGURE 9)
STEP 3A: Move mouse cursor down to lower right corner of cell B5. You should see the mouse cursor change its symbol to a thin black cross (FIGURE 10).
The AutoFill symbol is a thin black cross. It is referred to as the “fill handle.”
STEP 3B: The graphic shows this thin cross bigger than it actually appears.
Click and drag the fill handle to the right through cell E5 (top-right graphic) and then release the mouse button.
STEP 4: (FIGURE 11) Cells C5, D5, and E5 will automatically fill with totals.
Now we see the Salesperson Total for the other three salespersons: Ed, Robert, and Susan.
Tip: Always use the AutoFill feature for faster totaling of multiple columns.
STEP 5A: (FIGURE 12) Make cell F2 the active cell.
STEP 5B: (FIGURE 13) Move the mouse up to the Ribbon, Editing section. Locate the icon AutoSum. If you point at the icon with the mouse, it will state “Sum”. You will see a description of what AutoSum does. AutoSum is also found in Formulas toolbar, Function Library section.
STEP 6: (FIGURE 14) The AutoSum default is SUM. (More choices are available if you select the downward-pointing arrow to the right)
Notice that we can AutoSum numbers using other functions such as Average, Max, and Min.
To view this drop-down menu, you must move the mouse up to the downward pointing arrowhead and click the mouse.
STEP 7: (FIGURE 15) Go up to the Ribbon, in the Editing section, and select the AutoSum icon with your mouse. Make sure you don’t click on the downward pointing arrow.
STEP 8: (FIGURE 16) Upon clicking on the AutoSum icon with the mouse, you will the see the automatic summing of the data you want selected.
STEP 9A: (FIGURE 17) Press the Enter key. The AutoSum will execute, summing the numbers and outputting the result in cell F2.
STEP 9B: (FIGURE 18) We can use AutoFill to get the totals for February through March.
Make cell F2 the active cell.
Move mouse down to bottom-right corner to get the small black cross symbol and drag down through cell F5.
STEP 10A: (FIGURE 19) Using the AVERAGE formula
We’re changing to a new formula; AVERAGE.
Make cell A6 the active cell.
Enter “Salesperson Average” into cell A6.
STEP 10B: Make cell B6 the active cell.
Enter the formula: =AVERAGE(B2:B4)
Press the Enter key.
STEP 11: (FIGURE 20) Answer is 34. We will AutoFill the remaining cells. Make cell B6 the active cell.
Move mouse down to lower right corner of cell and get the small black cross.
Hold down the mouse button and drag across through cell E6 and release mouse button.
Cells C6 through E6 populate with data.
STEP 12A: (FIGURE 21) Using the MAX formula (finds largest value)
We’re changing to a new formula; MAX.
Make cell A7 the active cell.
Enter “Salesperson Max” into cell A7.
STEP 12B: Make cell B7 the active cell.
Enter the formula: =MAX(B2:B4)
Press the Enter key.
STEP 13: (FIGURE 22) Answer is 24. Make cell B7 the active cell. AutoFill the remaining cells. Move mouse down to lower right corner of cell and get the small black cross.
Hold down mouse button and drag across through cell E7 and release mouse button. Cells C7 through E7 populate with data.
STEP 14A: (FIGURE 23) Using the MIN formula (finds smallest value)
We’re changing to a new formula; MIN
Make cell A8 the active cell.
Enter “Salesperson Min” into cell A8.
STEP 14B: Make cell B8 the active cell.
Enter the formula: =MIN(B2:B4)
Press the Enter key.
STEP 15: (FIGURE 24) Answer is 21. Make cell B8 the active cell. AutoFill the remaining cells. Move mouse down to lower right corner of cell and get the small black cross and drag across through cell E8. Cells C8 through E8 populate.
Hold down mouse button and drag across through cell E8 and release mouse button. Cells C8 through E8 populate with data.
END OF LESSON
Leave a Reply