Using Simple Functions in Microsoft Excel

/ Uncategorized / By

Functions are built-in formulas that you can use to make your worksheet computations easier. For example, the SUM function adds all cells within a given range. For instance, if you wanted to add cells B8, B9, B10, B11, B12, B13, and B14, you wouldn’t need to enter cell references and operators by typing or pointing; you would just insert the name of the function and the range of cells you want to add. In other words, instead of entering a long string, such as
           =B8+B9+B10+B11+B12+B13+B14,
you would enter
           =SUM(B8:B14)
where B8:B14 is the reference for the range of cells to include in the sum.

 Using the SUM Function

The SUM function is one of the most frequently used functions. It totals the numeric value of all cells in the ranges it references. Cells in the referenced range that contain text or error values are ignored. You can enter the range references by typing or pointing.

To use the SUM function:

  1. Select the cell where you want the SUM function result to appear.
  2. Type =
  3. Type SUM(
  4. Type or select the appropriate range reference.
  5. Type )
  6. Click the Enter button.

or

  1. Press Enter

In the following exercise, you will using the SUM function to total data in column D.

1.If necessary, select cell D12 
2.Type = 
3.Type SUM( 
4.Using the mouse, select the range D6:D11As you select the range, the range reference appears in the formula bar and in cell D12. The range is surrounded by a moving dashed border.
5.Type )The moving dashed border disappears.
6.Press EnterThe result is displayed in D12.
7.Select cell B12 
8.Type =SUM(B6:B11) 
9.Click the Enter buttonThe result is displayed in B12.
10.Save and close January 

 Using the AutoSum Button

The most efficient way to sum a contiguous range of cells is by clicking the AutoSum button on the Standard toolbar. AutoSum automatically enters the SUM function and inserts the cell references that Excel assumes you want to add, which is usually the column above or the row to the left of the selected cell. If the selected cells are not correct, you can edit them by typing or pointing, or you can start again.

Using AutoSum

The AutoSum button is placed on the EDIT section of the HOME ribbon.

To use the AutoSum button:

  1. Select the cell in which you want the sum to appear. The chosen cell will need to have numeric data cells adjacent and above it (it will also work with other ranges that it can guess you want to SUM, as stated above).
  2. Click the AutoSum button on the Home ribbon or Formulas tab.
  3. Verify or select the cells to be totaled. Press Enter

or

  1. Click the Enter button.

 Using the Formula Palette

In Excel you can enter functions using the formula palette. The formula palette is activated by typing the equals (=) sign in a cell. Depending on the function you select from the drop-down list, the expanded formula palette will contain different features. In addition to allowing you to view the cell and range references pertaining to the function, the expanded formula palette also lets you preview the result and explains what the entry boxes mean.

All built-in functions in Excel consist of a function name, such as SUM, and a set of arguments. Arguments appear in parentheses after the function name and consist of cell or range references, text, values, names, labels, and other functions.

To use the formula palette:

  1. Select the cell in which you want the formula to appear.
  2. On the Formula bar, click the Edit Formula button.
  3. In the Function Box, click the drop‑down arrow, and then select a function.
  4. In the expanded formula palette, examine the argument boxes.
  5. If necessary, type or select different arguments.
  6. Choose OK.

In the following exercise, you will use the formula palette to make a calculation.

1.Select cell B12 
2.On the Formula bar, click the Edit Formula buttonThe formula palette appears.
3.In the Function box, click the drop‑down arrow, and then select the SUM functionThe expanded formula palette appears, specific to the SUM function.
4.In the Number 1 argument box, make sure B6:B11 appears 
5.Choose OKThe result and formula appear.
6.Use the same steps in cell B23 to calculate the sum of the Boxes of Fresh Products sold 

Learn more about Microsoft Excel on our regular training sessions in Glasgow and Edinburgh.