A spreadsheet formula is an instruction that tells Microsoft Excel or Google Sheets how to calculate a result. To create one, select a cell, type an equal sign (=), enter a calculation or function, and press Enter.

For example:

=B2+C2

This adds the values in cells B2 and C2. Formulas can calculate totals, averages, percentages, dates and conditional results. Excel and Google Sheets both use formulas that begin with an equal sign.

Spreadsheet Formulas at a Glance

Task Example Formula What It Does
Add two cells =B2+C2 Adds B2 and C2
Multiply values =B2*C2 Multiplies B2 by C2
Add a range =SUM(B2:B10) Totals cells B2 through B10
Find an average =AVERAGE(B2:B10) Calculates the mean
Count numbers =COUNT(B2:B10) Counts cells containing numbers
Apply a condition =IF(B2>=50,"Pass","Fail") Returns one result when the condition is true and another when it is false
Calculate a percentage =B2*C2 Multiplies a value by a percentage

How to Create a Basic Spreadsheet Formula

  1. Select the cell where you want the result.
  2. Type =.
  3. Select the cells you want to use, or enter their references.
  4. Add an operator such as +, -, * or /.
  5. Press Enter.

Suppose B2 contains 10 and C2 contains 5. Enter this formula in D2:

=B2+C2

D2 will display 15.

You can also calculate directly with numbers:

=10+5

Cell references are more useful for most spreadsheets because the result changes when the source values change. Microsoft describes a cell reference as an instruction that tells Excel where to find a value in a worksheet.

What the Main Spreadsheet Operators Mean

Operator Meaning Example Result
+ Addition =10+5 15
- Subtraction =10-5 5
* Multiplication =10*5 50
/ Division =10/5 2
^ Power =10^2 100
> Greater than =B2>10 TRUE or FALSE
< Less than =B2<10 TRUE or FALSE
= Equal to =B2=10 TRUE or FALSE

Use parentheses when you need to control the calculation order:

=(B2+C2)*D2

Without parentheses, Excel and Google Sheets follow the standard order of operations. Multiplication and division take place before addition and subtraction.

How to Use Cell Ranges

A range is a group of cells. The colon identifies the first and last cell:

B2:B10

This refers to every cell from B2 through B10.

You can use ranges with functions such as:

=SUM(B2:B10)
=AVERAGE(B2:B10)
=MAX(B2:B10)
=MIN(B2:B10)

For example:

=SUM(C2:C20)

This adds the values in cells C2 through C20.

A range can also cover multiple columns and rows:

=SUM(B2:D10)

This includes every cell from columns B through D and rows 2 through 10.

How to Use Common Spreadsheet Functions

A function is a built-in formula for a particular calculation. It usually contains a function name followed by parentheses and one or more arguments.

=SUM(B2:B10)

In this example, SUM is the function and B2:B10 is the range it calculates. Excel provides function suggestions and argument guidance while you type. Google Sheets also displays function help and syntax information during formula entry.

SUM: Add Values

=SUM(B2:B10)

SUM adds all numeric values in a range. It is easier to maintain than adding each cell separately:

=B2+B3+B4+B5

The range version is shorter:

=SUM(B2:B5)

AVERAGE: Calculate the Mean

=AVERAGE(B2:B10)

This function adds the numeric values and divides the total by the number of included values.

COUNT and COUNTA: Count Cells

=COUNT(B2:B10)

COUNT counts cells that contain numbers.

=COUNTA(A2:A10)

COUNTA counts cells containing any value, including text.

MAX and MIN: Find the Highest or Lowest Value

=MAX(C2:C20)
=MIN(C2:C20)

MAX returns the largest numeric value in the range. MIN returns the smallest.

IF: Return Different Results Based on a Condition

=IF(B2>=50,"Pass","Fail")

This checks whether B2 is at least 50.

  • If B2 is 50 or higher, the formula returns Pass.
  • If B2 is below 50, it returns Fail.

You can also compare text:

=IF(C2="Paid","Complete","Outstanding")

Text values inside a formula must be enclosed in quotation marks.

COUNTIF: Count Cells That Meet a Condition

=COUNTIF(C2:C100,"Paid")

This counts the cells in C2:C100 that contain Paid.

To count values above 100:

=COUNTIF(B2:B100,">100")

SUMIF: Add Values That Meet a Condition

=SUMIF(A2:A100,"North",B2:B100)

This adds values in B2:B100 when the corresponding cell in A2:A100 contains North.

How to Calculate Percentages

To calculate 20% of the value in B2:

=B2*20%

To calculate what percentage B2 represents of C2:

=B2/C2

Format the result as a percentage.

If B2 is 25 and C2 is 100, the formula returns 0.25. Percentage formatting displays the result as 25%.

To calculate a percentage increase:

=(C2-B2)/B2

This compares the original value in B2 with the new value in C2.

How to Copy a Formula to Other Cells

You can copy a formula across a row or down a column.

  1. Select the cell containing the formula.
  2. Drag the small square in the lower-right corner of the cell.
  3. Move it across the cells where you want the formula copied.

If D2 contains:

=B2*C2

copying it to D3 changes the formula to:

=B3*C3

This happens because spreadsheet formulas use relative cell references by default.

Relative and Absolute Cell References

A relative reference changes when you copy a formula:

=B2*C2

An absolute reference stays fixed because it uses dollar signs:

=B2*$F$1

Here, B2 changes when the formula is copied, but $F$1 always points to cell F1.

Absolute references are useful for a fixed tax rate, discount rate or exchange rate:

=B2*(1+$F$1)

If F1 contains 8%, this increases the value in B2 by 8%.

You can lock only a column or a row:

=$B2*C$1
  • $B2 locks column B but allows the row to change.
  • C$1 locks row 1 but allows the column to change.
  • $B$2 locks both the column and row.

How to Combine Spreadsheet Functions

A function can be placed inside another function. This is called a nested formula.

=IF(SUM(B2:B5)>=100,"Target met","Target not met")

The spreadsheet first calculates SUM(B2:B5). It then checks whether the result is at least 100.

Google Sheets calculates the innermost function first when functions are combined.

You can also use IFERROR to handle a possible division-by-zero error:

=IFERROR(B2/C2,0)

This returns 0 when the calculation produces an error.

How to Use Formulas With a Sales Table

Suppose your spreadsheet contains this table:

A B C D
Product Units Price Revenue
Notebook 3 4.50
Pen 10 1.25
Folder 5 3.00

To calculate revenue for the first product, enter this formula in D2:

=B2*C2

Copy the formula down to D3 and D4.

To calculate total revenue, enter:

=SUM(D2:D4)

To calculate the average product price, enter:

=AVERAGE(C2:C4)

To label products with revenue of at least $10, enter:

=IF(D2>=10,"High","Low")

Keeping the source data separate from the calculations makes the table easier to check and update.

Common Spreadsheet Formula Errors

#DIV/0!

The formula is dividing by zero or by an empty cell.

For example:

=B2/C2

If C2 is zero, use:

=IFERROR(B2/C2,0)

#VALUE!

The formula contains an incompatible type of data. One example is trying to multiply text by a number.

Check the referenced cells for unexpected text, spaces or symbols.

#NAME?

The spreadsheet does not recognise a function or named range. Check the spelling and punctuation.

For example, Excel will return this error for:

=SUME(A1:A10)

The correct function is:

=SUM(A1:A10)

#REF!

The formula refers to a cell that no longer exists. This often happens after someone deletes a referenced row or column.

Incorrect Result After Copying

Check whether a reference needs to stay fixed. Change:

=B2*F1

to:

=B2*$F$1

when F1 must remain the same as you copy the formula.

A Practical Way to Learn Spreadsheet Formulas

Use this sequence:

  1. Create calculations with +, -, * and /.
  2. Try SUM, AVERAGE, COUNT, MIN and MAX.
  3. Add conditions with IF, COUNTIF and SUMIF.
  4. Practise ranges and absolute references.
  5. Use IFERROR for predictable errors.
  6. Copy formulas down a column and check that the references change as expected.

Reference cells instead of typing fixed numbers directly into formulas. That way, the calculations update when the underlying data changes.