Getting started with Excel formulas and functions – Part 2

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.

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.
   
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.
   
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.
   
4. You can also verify your formulas by using the Formulas tab > Formula Auditing group > Show Formulas button.
   

What are Excel Functions

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.
   
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.
   
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.
   
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).
   
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:
   

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.
   
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).
   
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:
   

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.
   
2. Select the range G3:G11 and then on the Home tab > Number group, click the Percent Style button to apply the Percent Style.