In the previous lesson, you learned about cell references.  Cell references identify individual cells or cell ranges in columns and rows. Cell references tell Excel where to look for values to use in a formula. Excel uses a cell reference style such as A1, which refers to columns with letters and to rows with numbers. The numbers and letters are called row and column headings.

There are two types of cell references, relative and absolute.  Relative references automatically change as they’re copied down a column or across a row.  References are relative by default and so copying cells is easy using AutoFill as long as you want the formula in the cells to change.  But what if you don’t want to change the cells when the formula is copied?  That is where absolute cell references come in.

Note:  I am recommending some textbooks and software I have personally used to in my classroom for many years. (In addition to being a longtime Amazon customer, as an Amazon Associate I earn from qualifying purchases. This won’t cost you anything but it helps me to offset the costs of running officeskills.org. Thanks for your support! 

What are Absolute Cell References?

Absolute cell references refer to cells that you don’t want to change as the formula is copied.  In order to make this work, you have to type dollar signs, as shown below to change the reference type to absolute.

Absolute references are fixed which means they don’t change if you copy a formula from one cell to another. Absolute references have dollar signs ($) like this: $F$11.

There may be times when you need to use a mixed reference.  The mixed reference has either an absolute column and a relative row, or an absolute row and a relative column.  When a mixed reference is copied from one cell to another, the absolute reference stays the same but the relative reference changes.   In the example below, when the formula =F3/$F$11 is copied from row to row, the relative reference of F3 changes but the absolute cell reference remains as $F$11.

  1. Click in cell G3 and then type =F3/F11 and press the ENTER key on your keyboard.
    relative reference cell in Excel
  2. Double click in cell G3 and add a dollar sign ($) before the F and the 11 to make this an mixed cell reference and then press ENTER on your keyboard.  Your formula should look this =F3/$F$11.
    Mixed cell references in Excel
  3. Autofill the formula in cell G3 down through cell G10.  The results should look like the following.  Click in cell F9 and verify in the formula bar that the Absolute formula stayed the same.  It should look like =F9/$F$11.
    Absolute cell references in Excel
  4. You can also verify your formulas by using the Formulas tab > Formula Auditing group > Show Formulas button.
    Verify formulas in Excel

What are Excel Functions

Recommended Text

Excel functions are pre-written formulas that simplify the process of entering calculations.  By using functions, you can easily and quickly create formulas that might otherwise be difficult to build for yourself.

We’ve already looked at the SUM function and it is just one of the many Excel functions available to help make your job easier.  For example, The MAX function finds the largest number in a range, and the MIN function finds the smallest number in a range.

Find an Average

In our example, we will use the AVERAGE function to find the mean average cost of all revenue for each quarter.

  1. Click in cell B13 and type the = sign to begin a formula and from the Functions Bar, choose Average.  The Functions Arguments dialog box will appear.
    Average function in Excel 2016
  2. With the Function Arguments dialog box open, click in cell B3 and select the range B3:B10.  (Do not add the Total Revenue row).  The correct range will be reflected in the Functions Arguments dialog box next to Number1.  Click the OK button and then verify the range in the formula bar.
    Average function in Excel 2016
  3. Use the Autofill feature to autofill the formula to the right through column F.  Your results should look like the following:

Find the Largest or Smallest Number (Max or Min)

The MAX function finds the largest number in a range, and the MIN function finds the smallest number in a range.

Find the smallest value (Min Function)

  1. Click in cell B14 and then click the Home tab> Editing Group and then click the AutoSum arrow and choose MIN from the list of functions.
    MIN function in Excel 2016
  2. In cell B14 and with the insertion point in the function argument, click in cell B3 and then drag down to select the argument range B3:B10.  (Do not include the total row).
    MIN function in Excel 2016
  3. Click the ENTER button on your keyboard to display the result then use the Autofill feature to copy the formula to the right through column F. Your results should look like the following:
    MIN function in Excel

Find the largest value (MAX Value)

  1. Click in cell B15 and then click the Home tab> Editing Group and click the AutoSum arrow and from the list of functions choose MAX.
    MAX function in Excel 2018
  2. With the insertion point in the function argument, click in cell B3 and then drag down to select the argument range B3:B10.  (Do not include the total row).
    MAX function in Excel 2016
  3. Click the ENTER button on your keyboard to display the result then use the Autofill feature to copy the formula to the right through column F. Your results should look like the following:
    MAX function in Excel 2016

Format Numbers

Amazon As Excel default formatting doesn’t display commas, trailing zeros or decimal points, you need to manually perform this task.

  1. Click the range B3:E15 and then on the Home tab > Number group, click the Increase Decimal button two times to add two decimal points.
    Increase decimal in Excel
  2. Select the range G3:G11 and then on the Home tab > Number group, click the Percent Style button to apply the Percent Style.
    Percent style in Excel

Get our Latest Blog Posts, New Class Notifications,
Industry Trends

We don’t spam! Read our [link]privacy policy[/link] for more info.

 

Tags: , , , , , , , , , , , , , , , , , , ,