Skip to content

Learn About Spreadsheet Formulas

What is a Formula?

A formula is an expression or mathematical statement which performs calculations to determine the value of a cell.

For example, cell A3 below contains a formula that adds the value of cell A2 to the value of cell A1 to determine a final value of 5.

This final value is displayed in cell A3.

 

Also, all formulas in a spreadsheet must start with an equal sign (=).

Enter a Formula

To enter a formula, follow the steps below.

  1. Select a cell
  2. Type in an equal sign (=)
  3. Type in the rest of the formula

As an example, you can type the following formula into cell A3.

=A1*A2

This formula will multiply the values in cells A1 and A2 and display the result in cell A3.

 

Also, instead of typing in “A1” and “A2”, you may select cells A1 and A2 with your mouse. Just be sure that you first typed in the equal sign (=).

Important Note

Both Excel and Google Sheets recalculate the result of a formula when it changes.

For example, if you change the value of cell A1 from 5 to 4, the formula in cell A3 will recalculate and display a new result of 12.

Edit a Formula

To edit a formula, select the cell that contains the formula. The spreadsheet will display the formula of the selected cell in the formula bar.

To edit the formula, click the formula bar and change the formula.

When you finish making changes, press the Enter key.

You can also edit a formula by double-clicking on the cell and changing it within the cell itself.

The Parts of a Formula

A formula may contain any or all of the following:

  • Functions – Built-in formulas you can use, such as SUM()
  • References – The name of another range or cell, such as A2
  • Constants – Numbers or text values
  • Operators – The plus sign (+), the minus sign (-), etc.

A formula can be simple and only contain one of the above items

 

Or a formula can be complex and contain many or all of the above items

Operators

Operators specify the type of calculation that you want to perform on the elements of a formula. For example, the multiplication operator (*) will multiply the two values on either side.

Operator Precedence

Operator precedence refers to the order in which calculations occur within a formula. If your formula has more than one operator, the precedence determines which operator will calculate first, which will calculate second, etc.

The basic order of precedence is:

  • Any part of the formula in parentheses will calculate first
  • Multiplication and division operators go next
  • Addition and subtraction are performed last

An example is shown below.

Copy and Pasting a Formula

When you copy a cell that contains a formula and paste it elsewhere, the formula is copied too.

However, the cell references within the formula are adjusted according to the new cell’s location.

See the example below.

Look at the formula in cell A4.

 

Cell A4 is copied.

 

The cell is pasted into B4 and the new formula’s references have been adjusted accordingly.

Important Note

Instead of using copy and paste, you can drag the formula to cell B4 to get the same result.

Select cell A4 and click on the fill handle in the lower right corner.

 

Drag across to cell B4 and the formula will be copied and updated accordingly.

 

This is a much easier and faster way to copy the formula to an adjacent cell.

Of course, this is only scratching the surface when it comes to formulas, references, and functions. However, this is a good summary of the basics of spreadsheet formulas and how they work.

This Post Has 0 Comments

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top