Basics
Your first formulas
Learn that a formula begins with an equals sign and uses cell references to calculate a result.
Lessons
Continue with Your first formulas .
Clear all saved lesson progress?
Basics
References that move when filled
See how a reference such as A1 shifts to A2, A3, and beyond when a formula is filled down.
Basics
Locking a cell with dollar signs
Use a reference such as $A$1 so that every copy of a formula keeps pointing at the same cell.
Math
Adding up a block of cells
Give SUM a range such as B2:B5 to total a whole column of numbers in one step.
Logical
Making a cell decide
Write an IF formula that compares a value and shows one of two results.
Math
How many, and how much on average
Read the same range with COUNT and AVERAGE to learn its size and its mean.
Lookup
Finding a row by its key
Use VLOOKUP with FALSE to fetch a value from the row whose key matches exactly.
Lookup
A two-step lookup
Combine MATCH to find a position and INDEX to return the value at that position.
Math
How many digits to keep
Give ROUND a number and a digit count to round it to those digits.
Math
How many cells match one test
Give COUNTIF a range and one criterion to count the cells that match.
Math
Adding amounts that match one test
Give SUMIF a criterion range, a match value, and a number column to add the matching amounts.
Math
Adding amounts that pass two tests
Give SUMIFS a number column and two tests so only rows that pass both are added.
Text
Taking the middle of a code
Give MID a short code, a start position, and a length to pull the characters in the middle.
Text
Finding where a letter sits
Give FIND a letter and a word to learn which character position that letter occupies.
Logical
When both tests must pass
Write an AND formula with two comparisons so the cell is true only when both pass.
Logical
When either test may pass
Write an OR formula with two comparisons so the cell is true when either one passes.
Date
Building a date from three numbers
Give DATE a year, a month, and a day in separate cells to build one date.
Date
Reading the year from a date
Give YEAR a date cell to read the four-digit year stored in that date.
Math
Averaging numbers in one group
Give AVERAGEIF a group label and a number column to average only the matching rows.
Math
Counting rows that pass two tests
Give COUNTIFS two tests so only rows that pass both are counted.
Text
Making a label all capitals
Give UPPER a short label to turn every letter into a capital.
Text
Making a label all lowercase
Give LOWER a short label to turn every letter into a small letter.
Logical
Flipping a true or false test
Give NOT a comparison so the cell shows the opposite of that test.
Logical
Catching an error from a divide
Wrap a divide in IFERROR so a zero divisor shows a fallback instead of an error.
Date
Reading the day of the month
Give DAY a date cell to read which day of the month that date falls on.
Date
Reading the day of the week
Give WEEKDAY a date cell to learn the weekday number for that date.
Math
How many cells are filled
Give COUNTA a range to count every cell that holds a number or text.
Math
How many cells are empty
Give COUNTBLANK a range to count the cells that have nothing in them.
Text
Removing extra spaces from text
Give TRIM a messy label to drop spare spaces from the ends and the middle.
Text
Replacing one word with another
Give SUBSTITUTE a phrase, an old word, and a new word to swap every match.
Logical
Picking a result from several tests
Write an IFS formula with several tests so the first passing test picks the result.
Logical
Checking whether a cell is empty
Give ISBLANK a cell to test whether that cell is still empty.
Date
Measuring a span between dates
Give DATEDIF a start date, an end date, and a unit code to measure the span.
Date
Counting days between two dates
Give DAYS an end date and a start date to count the days that lie between them.
Math
Rounding a price up
Give ROUNDUP a price and 0 digits to push the price up to the next whole number.
Math
Rounding a price down
Give ROUNDDOWN a price and 0 digits to drop the price down to the whole number below.
Text
Joining cells into one list
Give TEXTJOIN a delimiter and a row of cells to join them into one list.
Text
Showing a number as formatted text
Give TEXT a number and a format code to show that number as padded text.
Logical
Checking whether a cell is a number
Give ISNUMBER a cell to test whether that cell holds a number.
Logical
Flagging a lookup that missed
Give ISERROR a lookup that cannot find a key so the cell flags the miss.
Date
Counting weekdays between two dates
Give NETWORKDAYS a start date and an end date to count the weekdays between them.
Date
Shifting a date by weekdays
Give WORKDAY a start date and a weekday count to land on a later work date.
Math
Taking the gap between two numbers
Give ABS the difference of two cells so the gap is always a positive number.
Math
Finding the remainder of a divide
Give MOD a number and a divisor to read the remainder of that divide.
Text
Turning digit text into a number
Give VALUE a cell of digits stored as text to turn it into a number.
Text
Checking two labels letter for letter
Give EXACT two labels to test whether they match including letter case.
Date
Building a time from three numbers
Give TIME an hour, a minute, and a second in separate cells to build one time.
Date
Reading the hour from a time
Give HOUR a time cell to read the hour stored in that time.
Math
Raising a number to an exponent
Give POWER a base and an exponent to raise the base to that power.
Math
Finding the side of a square
Give SQRT a square area to read the length of one side.
Lookup
Finding a value under a header
Give HLOOKUP a header key and a table so it returns the value from a lower row.
Lookup
Finding a value by its key
Give XLOOKUP a key and two ranges to return the matching value from the result range.
Math
Finding the middle of a list
Give MEDIAN a short list of numbers to return the middle value after they are ordered.
Math
Ranking a score in a list
Give RANK a score and a list so the cell shows where that score stands.
Lookup
Keeping rows that pass a test
Give FILTER a value column and a test column so only passing rows remain.
Lookup
Dropping repeats from a list
Give UNIQUE a column of labels to keep only the first copy of each label.
Math
The smallest amount in one group
Give MINIFS a group label and a number column to find the smallest matching amount.
Math
The largest amount in one group
Give MAXIFS a group label and a number column to find the largest matching amount.
Lookup
Reading a cell from a text address
Give INDIRECT a cell that holds an address as text to pull the value at that address.
Lookup
Putting a list in order
Give SORT a column of numbers to return those numbers in order.
Lookup
Reading which row a cell sits in
Give ROW a marked cell to report the row number of that cell.
Lookup
Reading which column a cell sits in
Give COLUMN a marked cell to report the column number of that cell.
Lookup
How many rows a block covers
Give ROWS a tall range to count how many rows that block covers.
Lookup
How many columns a block covers
Give COLUMNS a wide range to count how many columns that block covers.
Math
Finding the lowest number in a range
Give MIN a short column of numbers to pick out the lowest value.
Math
Finding the highest number in a range
Give MAX a short column of numbers to pick out the highest value.
Text
Taking a prefix from a word
Give LEFT a word and a length to pull the opening letters as a prefix.
Text
Taking a suffix from a word
Give RIGHT a word and a length to pull the ending letters as a suffix.
Text
Counting letters in a name
Give LEN a short name to count how many characters that name uses.
Date
Putting the current date in a cell
Write TODAY with empty parentheses so the cell shows the date from the clock.
Math
Finding the lowest price
Give MIN a column of prices to find the cheapest amount.
Math
Finding the highest score
Give MAX a column of scores to find the top mark.
Text
Taking the first three characters
Give LEFT a word and 3 to keep only the first three characters.
Text
Taking the last three characters
Give RIGHT a word and 3 to keep only the last three characters.
Text
Counting characters in a code
Give LEN a short code to count every character, including the hyphen.
Math
Adding two separate ranges
Give SUM two ranges so their numbers are added together in one formula.
Math
Adding numbers down a column
Give SUM a column such as D2:D4 to place the total at the foot of that column.
Math
Adding a list of prices
Give SUM a price column to total every listed price.
Math
Averaging a column of numbers
Give AVERAGE a column of numbers to find their mean in one cell.
Math
Averaging a list of scores
Give AVERAGE a column of scores to find the typical mark.
Math
Counting only the numeric cells
Give COUNT a mixed list so only the cells that hold numbers are counted.
Math
Counting numbers down a column
Give COUNT a column with a text gap to skip the gap and count the numbers.
Math
Rounding to a whole number
Give ROUND a decimal and 0 digits to round it to the nearest whole number.
Math
Rounding to two decimal places
Give ROUND a decimal and 2 digits to keep two places after the point.
Math
Counting cells that hold one word
Give COUNTIF a list of words and one label to count how many cells hold that word.
Math
Counting numbers above a cutoff
Give COUNTIF a number column and a greater-than test to count values above a cutoff.
Logical
Testing a number against a cutoff
Write an IF test that checks whether a number clears a cutoff and returns a flag.
Logical
Showing pass or fail from a score
Write an IF formula that labels a score as pass or fail.
Lookup
Looking up a price by product key
Give VLOOKUP a product key and a two-column table to return the matching price.
Lookup
Looking up a city by code
Give VLOOKUP a place code and a two-column table to return the matching city.
Lookup
Picking a cell by row number
Give INDEX a column and a row number to return the value in that row.
Lookup
Picking a cell from one row
Give INDEX a single row and a column number to return the value in that slot.
Lookup
Finding a name in a list
Give MATCH a name and a list of names to return the position of that name.
Lookup
Finding a color in a list
Give MATCH a color and a list of colors to return which slot that color occupies.
Logical
Choosing a word from a text test
Write an IF formula that compares a text label and returns one of two words.