Excel Formulas and Functions

Learn how to use Excel Formulas and Functions.

A formula can contain any or all the following – functions, references, operators, and constants.  Constants are values that are not calculated as they always remain the same.  Below are the step by step instructions for using various Excel Formulas and Functions Features.

Excel Formulas and Functions — Using Calculation Operators

Operators specify the type of calculation you want to perform on the elements of the formula.  There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.

Arithmetic Operators

To perform basic mathematical operations, use the following arithmetic operators.

Arithmetic operator Meaning Example
+ (plus sign) Addition 2+2
– (minus sign) Subtraction 2-2
* (asterisk) Multiplication 2*2
/ (forward slash) Division 2/2
% (percent sign) Percent 2%
^ (caret) Exponentiation 2^2
Comparison Operators

You can compare two values with the following operators.  When two values are compared by using operators, the result is a logical value – either TRUE or FALSE.

Comparison operator Meaning Example
= (equal sign) Equal to A1=B1
> (greater than sign) Greater than A1>B1
< (less than sign) Less than A1<B1
>= (greater than or equal to sign) Greater than or equal to A1>=B1
<= (less than or equal to sign) Less than or equal to A1<=B1
<> (not equal to sign) Not equal to A1<>B1
Text Concatenation Operator

The & (ampersand) is used to concatenate (join) one or more text strings to produce a single piece of text.

Text operator Meaning Example
& (ampersand) Joins, or concatenates, two values to produce one continuous text value “North”&”West” results in “NorthWest”
Reference Operators

The following reference operators are used to combine ranges of cells for calculations.

Reference operator Meaning Example
: (colon) Range operator, which produces one reference to all the cells between two references, including the two references A1:A5
, (comma) Union operator, which combines multiple references into one reference SUM(A1:A5,B1:B5)
 (space) Intersection operator, which produces one reference to cells common to the two references (A1:C5 B1:D5)
Calculation Order

Formulas always begin with an equal sign (=) and calculate in a specific order.  Excel interprets the characters that follow the equal sign as a formula.  Excel calculates the formula from left to right, according to a specific order for each operator in the formula, but can be changed by using parentheses.  The part of the formula enclosed in parentheses is calculated first.  If there are multiple parts enclosed in parentheses, those parts are calculated first, from left to rightExample:  =(A1+B1)/(C2-D1) – The results of A1+B1 is divided by the results of C2-D1.

Operator Order of Precedence Description
: (colon)

(single space)

, (comma)

Reference operators
– (minus sign) Negative
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
+

<

>

<=

>=

<>

Comparison

Excel Formulas and Functions — Entering Formulas

  1. Click the cell to contain the formula.
  2. To denote a formula will be used in the cell, type = (equal) followed by the formula.
  3. Press Enter when finished typing the formula. Excel Formulas and Functions

Excel Formulas and Functions — Using Cell References in a Formula

A cell reference refers to a cell or a range of cells on a worksheet.  When you create either a simple formula or one that uses a function, you can refer to the data in cells by using cell references in the formula arguments.  Example:  A1 refers to the cell at the intersection of column A and row 1 Cell references can be used to refer to the following –

  • Data from one or more contiguous cells on a worksheet
  • Data contained in different areas of a worksheet
  • Data on other worksheets in the same workbook
To refer to Use
The cell in column A and row 1 A1
The range of cells in column A and rows 1 through 20 A1:A20
The range of cells in row 1 and columns A through C A1:C1
All cells in row 1 1:1
All cells in rows 1 through 20 1:20
All cells in column A A:A
All cells in columns A through C A:C
The range of cells in columns A through C and rows 1 through 10 A1:C10
Creating a Cell Reference
  1. Click the cell where you need to enter the formula.
  2. In the formula bar, type equal sign (=).
  3. Do one of the following, select the cell that contains the value you want or type its cell reference. You can refer to a single cell, range of cells, location in another worksheet, or location in another workbook.  When selecting a range of cells, you drag the border of the cell selection to move the selection or drag the corner of the border to expand the selection.
    • The first reference in this example is B2. The color is blue and the border is blue with square corners
    • The second reference in this example is the range B3toB4. The color is red and the border is red with square corners.

Note:  If there are no square corners on a color-coded border, the reference is to a named range. Excel Formulas and Functions

  1. Press Enter to complete the formula.

Excel Formulas and Functions — Defining and Using Names in Formulas

Using names in formulas can make them much easier to understand and work with.  You can define a name for a cell range, formula, constant, or table.  Example:  =SUM(FirstQtrInv)

Note:  By default, names use absolute cell references. 

You can define names by using the following:

  • Name box on the formula bar – This works best for creating a workbook level name for a selected range. Excel Formulas and Functions
  • Create a name from a range – You can create a name from an existing row and column labels by using a selection of cells in the worksheet. Excel Formulas and Functions
  • New Name dialog box – The works best when you want more flexibility in creating names.
  1. Highlight the cell or range of cells to be defined.
  2. Click the Formulas tab on the ribbon.
  3. Click Define Name option in the Defined Names group. Excel Formulas and Functions
  4. Enter the following information in the Define Name dialog box –
    • Name – Name of the defined range
    • Scope – Workbook or Worksheets
    • Comment – Can be added to explain the range
    • Refers to – Range for the new name (Verify it is the range of the cells you selected or set the range here.) Excel Formulas and Functions
  5. Click OK to accept the name.
Deleting or Editing Range Names
  1. Click the Formulas tab on the ribbon.
  2. Click Name Manager option in the Defined Names group.
  3. The Name Manager dialog box will open. Excel Formulas and Functions
  4. Click on the name you want to delete or edit.
  • To delete the name, click the Delete button.
  • To edit the name, click the Edit button to open the Edit Name dialog box. Make the necessary changes and click OK Note:  The Refers to can be changed by highlighting the existing range in the dialog box and the selecting the new range in the worksheet to replace it.
  1. Click Close to accept the changes.

Excel Formulas and Functions — Using the Insert Function Command

  1. Click the cell to contain the formula.
  2. On the Formula Bar, click the Insert Function icon.Excel Formulas and Functions
  3. Click the needed function and click OK.
  4. Select the needed range of cells. Excel Formulas and Functions
  5. Click OK when finished.

Using AutoFill to Copy Formulas

  1. Click in the cell containing the formula to be copied.
  2. Place the cursor on the drag fill handle, which is located at the bottom right corner of the cell.Excel Formulas and Functions
  3. Once the crosshair appears, drag through the range of cells to include the formula. In this example, it is dragged to the right. Excel Formulas and Functions
  4. Once the mouse is released, the formula is copied and the results are listed. The AutoFill dialog box appears, giving you the options to Copy Cells (default), Fill Formatting Only, or Fill Without Formatting.  Since we want to copy the formula and list the results the same as what we are copying, we will leave Copy Cells selected. Excel Formulas and Functions

Excel Formulas and Functions — Switching Between Relative and Absolute References

When you create a formula that refers to another cell or range, that cell reference can either be absolute or relative.  An absolute cell reference does not change when the formula is moved.  A relative cell reference does change to its new location with the formation is moved or copied.

  1. Click the cell containing the cell reference you need to change.
  2. In the formula bar, click the cell reference that you want to change. Excel Formulas and Functions
  3. Press Fn + F4 to toggle though the combinations or manually type in the dollar sign ($) in the appropriate spot.
Cell Reference Description
$B$6 Absolute column and row
B$6 Relative column and absolute row
$B6 Absolute column and relative row
B6 Relative column and relative row
  1. Press Enter to accept the change. Excel Formulas and Functions

Leave a Reply

Your email address will not be published. Required fields are marked *