Formulas, Functions, and AutoFill

Excel lets the user to perform calculations on large sets of data using built-in and user-defined functions.

Operators

Excel has basic algebraic operations, and follows order of operations.

  • All formulas in Excel start with an equals sign (=). This tells Excel you’re entering a calculation. Operators then define the actions within your formula, following the standard algebraic order of operations:
  • Parentheses ( ): Perform operations inside first.
  • Exponentiation (^): Raise a number to a power.
  • Multiplication (*): Calculate the product.
  • Division (/): Calculate the quotient.
  • Addition (+): Calculate the sum.
  • Subtraction (-): Calculate the difference.

Formulas

1. Select the active cell you wish to place your formula. The formula must begin with an equality sign.

2. Insert your expression and press enter.
Example:set A3 equal to the product of 7 and 135. Press enter to calculate the value.

  1. cell A3 is highlighted 
  2. cell A3 has a = in it 
  3. cell A3 has the contents =7*135 
  4. cell A3 has the number 945 (which is the product of 7*135) 

Cell Referencing

Users can reference data from a cell or range of cells for use in calculations.

Each cell in the spreadsheet has a reference, given by its column and row position. In the Formulas section above, A3 is the reference for the value 945 on that spreadsheet. For further calculations, instead of continuously using the original value, A3 can be substituted in its place.

Example:
Repeat the previous example using the values from above. Insert 7 in cell A1 and 135 in cell A2.

  1. cell A1 has the number 7 and cell A2 has the number 135
  2. cell A3 now has =A1*A2

Insert “=A1*A2” to create a formula using cell referencing instead of inserting “=7*135” in A3.

Note: when referencing, the reference points to the cell and not the value inside. A1 tells Excel to look at A1 and pull whatever value it holds. If you update the value of A1 (e.g., from 7 to 9), then it updates all the calculations. A1 does not tell the calculator to look for the value 7.

Example:
Change the value of A2 to 123.

The value updates when the referenced cells are changed.

The value of A3 has changed to 861, but the formula of the cell is still A1*A2.

Functions

Excel can perform a number of built in functions on a range of cells. To access these functions, either:

  1. type in the equals (=) sign followed by the name of the function
  2. click the fx button next to the cell value bar, or
  3. find the sigma (Σ) symbol dropdown arrow in the Editing section of the Home ribbon.

To reference a range of cells, choose your starting cell and your ending cell.

A1:A4 – this continuous range includes cells A1, A2, A3, and A4. 

A1:B3 – this continuous range includes cells A1, A2, A3, B1, B2, and B3 

A1:A3, C1:C3 – this non-continuous range includes cells A1, A2, A3, C1, C2, and C3

  • =SUM(range) finds the sum of a range of values
  • =AVERAGE(range) finds the average of a range of values
  • =MIN(range) finds the smallest value in a range
  • =MAX(range) finds the largest value in a range
  • =COUNT(range) counts the number of cells in a range when the range contains numbers
  • =COUNTA(range) counts the number of cells in a range when the range contains any kind of data

Conditional Functions

Conditional functions perform calculations on a cell or range of cells only if those cells meet a certain condition. These functions test a given range and determine if the condition is true or false before continuing. A condition can be any relational comparison:

Examples:

  • A3>14 if the value of cell A3 is larger than the number 14
  • D5<=2 if the value of cell D5 is less than or equal to the number 2
  • T47=”cheese” if the value of cell T47 is “cheese”

There are six types of conditional functions covered by this course. Each has a specific use and syntax.

AND and OR

=AND(condition1, [condition2], …)
=OR(condition1, [condition2], …)

AND will evaluate to TRUE only if all conditions are true.

For example,

=AND(G2=”BLAH”,A2=”OCHO”)

will evaluate to TRUE if the cell G2 is “BLAH” and the cell A2 is “OCHO”.

OR will evaluate to TRUE if any of the conditions are true.

For example,

=OR(E2>12,F2<54)

evaluates to TRUE if E2>12, or F2<54, or if both of the conditions are true!   

IF

=if(condition,value_if_true,value_if_false)

If the given condition is true, the cell will be set to the “value_if_true.” If the condition is false, the cell will be set to the “value_if_false.” The “value_if_true” and “value_if_false” are optional inputs which default to TRUE and FALSE, respectively.

Example:

=if(A2>3,32,”Number too small”)

Reads: If A2>3 is true, then set the cell to 32. Otherwise, set the cell to “Number too small”

Nested IF Functions

You can nest one “if” function inside of another to test for multiple conditions at once. A single “if” function, without the use of any other functions, can only test for a single condition.

Example:

Test the condition of whether the contents of cell A2 are between 10 and 20.

=if (A2>20,”Number too large”, if (A2<10,”Number too small”, “Just right!”))

If it the value of A2 is larger than 20, the active cell will be set to “Number too large.” If A2 is less than 20, it will be tested to see if the value of A2 is less than 10. If it is, the active cell is set to “Number too small.” Otherwise, we know that the value of A2 must be between 10 and 20, so the active cell is set to “Just right!”

COUNTIF

=countif(range,criteria)

The “countif” function will increment the active cell by one each time the criteria is true for a cell in the given range.

Example:
In column F you have colors, and want to count the number of occurrences of “red”.

=countif (F1:F230, “=red”)

If a cell in F1:F230 satisfies “=red”, then increment the active cell by one.

SUMIF

=sumif(range,criteria,sum_range)

The “sumif” function takes a range and a corresponding sum_range of the same size. Each time the criteria is true, the value in the cell for sum_range gets added to the active cell.  If “sum_range” is not specified, excel will treat “range” as “sum_range” as well.

Example:
You wish to know how many hours you have spent watching television, from a database of your time spent on various activities.

=sumif (D1:D230,”=television”, H1:H230)

If a cell in D1:D230 satisfies “=television”, then increment the active cell by the corresponding cell from H1:H230. If cell D7contains the word “television,” the active cell will be incremented from the corresponding cell from H1:H230, or cell H7.

Autofill and Referencing

The autofill feature can be used to mass copy a formula. However, autofill works slightly differently when using referencing. When autofill is used with a reference, each cell in the autofill range will reference the cell adjacent to the previous reference.

Example:
Enter a value in the A1 cell. In the cell below it, enter the formula: =A1+1.

Use the autofill feature and drag the formula down a few cells. Select cell A3. It now contains the formula: =A2+1

A3 will reference A2, and A4 will reference A3, and so on. Autofill will follow this formatting horizontally as well.

To perform a calculation on a constant value, users will need to signify the constant value. To do this in Excel, place a $ between the column and row reference (=A$1+1).

Autofill again, and select A3. Notice how A3 is now referencing A1.

Note: Autofill can be used in any direction (down, up, left, or right).

Autofill lets you fill cells with data that follows a pattern or are based on data in other cells.

Basic Autofill (Click-and-Drag)

  1. Select a cell with a value or formula (e.g., =A1+1).
  2. Hover over the small square (fill handle) in the bottom-right corner.
  3. Click and drag over the cells you want to fill.
  4. Release your mouse to apply the pattern.

Excel will try to detect the pattern (numbers, weekdays, formulas, etc.) and continue it.

Example: If A2 has =A1+1, dragging down will increment each row (and cell reference).

Tip: Remember that autofill works differently with statically referenced data and cell addressed data. For example, (A1 vs $A$1) affect how formulas are copied.

Double-Click Autofill

If you’re working in a column with adjacent data:

  • Double-click the fill handle instead of dragging.
  • Excel will autofill downward until it reaches an empty row.