Midlands Microsoft Office Provider

4.4 Practice Ex 8; Use Formulas with AutoFill & AutoSum Features – Intro to Excel 2016

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).
     FIGURE 1
How did the number 80 appear in this cell? To really know how it got there, look at the formula bar contents (FIGURE 2).
                           FIGURE 2
Does the formula bar contain 80? The number 80 was typed into the cell.
                          FIGURE 3
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:

  1. To enter any formula, you must always begin by entering the equals symbol =
  2. 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.
  3. Enter the open parenthesis symbol (
  4. 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.
  5. Enter a colon :
  6. 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
  7. Enter the closed parenthesis symbol )
  8. Press the enter key to execute the formula.
  9. 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.

FIGURE 4 (Before pressing the Enter key)
FIGURE 5 (After pressing the Enter key)
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.

​Remember cell alignment rules: After entering text, the text will be left-aligned in the cell; numbers will be right-aligned in the cell. ​
                                    FIGURE 6
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)

SUMMING NON-ADJACENT CELLS
To select non-adjacent cells to be summed, you can hold down the CTRL key while 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)

Exercise 8 continues in Section 4.4.1
FIGURE 7
FIGURE 8
FIGURE 9
Section completed. Go to Module 4 Section 4.4.1 Practice Exercise 8 continues there with the AutoFill Feature. (We will continue to use this data for Exercise 9).