# Excel Formulas Cheat Sheet

Learn the basics of Excel with our quick and easy cheat sheet. Have the basics of formulas, operators, math functions and more at your fingertips.
Jan 2023  · 18 min read

Welcome to our Excel Basics Cheatsheet! Whether you're new to the program or just need a quick refresher, this guide will help you master the essential skills needed to navigate and utilize the powerful features of Excel efficiently. From working with cells and formulas to flow control and conditional computation, our cheat sheet provides easy-to-follow instructions and examples to help you become a pro at using this popular spreadsheet software. Whether you're a student, professional, or just someone looking to improve your Excel data analysis skills, this cheat sheet is the perfect resource for anyone looking to improve their Excel proficiency. Have this cheat sheet at your fingertips

## Definitions

This cheat sheet describes the behavior of the Microsoft 365 version of Excel, and slight differences exist between Excel versions.

Spreadsheet: An application, like Microsoft Excel, where you can store data, perform calculations, and organize information.
Workbook: A file containing a collection of one or more worksheets.
Worksheet: A single page in a workbook. It is a grid of cells arranged in rows and columns.
Cell: A rectangular box in a worksheet that can store a data value, a formula, or other content.
Formula: A piece of code to perform a calculation. Formulas start with an equals sign (=), and contain functions, mathematical operators, values, and cell references.
Cell reference: The location of a cell. The column is described with letters and the row is described with numbers. For example, the cell in the 4th column, 7th row would be denoted D7.
Cell range: A group of adjacent cells in a worksheet. A cell range is typically referred to by its upper-left and lower-right cells, such as A1:C3, referring to the cells in columns A, B, and C and rows 1 through 3. You can use cell ranges to perform calculations on multiple cells at once or to apply formatting to a group of cells.

 A B C 1 Cell A1 Cell B1 Cell C1 2 Cell A2 Cell B2 Cell C2 3 Cell A3 Cell B3 Cell C3

## Getting help

### You can get help by accessing the help menu

1. Open Microsoft Excel.
2. Click on the "Help" menu at the top of the screen.
3. In the Help menu, you will see various options for getting help, including a search bar where you can enter keywords to search for specific topics.
4. You can also click on the "Help" button to open the Help pane, where you can browse through various topics and find answers to common questions.

### How to add a comment to a cell

1. Click on the cell where you want to add a comment.
2. Right-click or CTRL+click on the cell and select the "New Comment" option from the context menu. You can also click on the Insert menu then "New Comment".
3. This will open a small text box next to the cell, where you can type your comment.
4. Once you have entered your comment, click the green arrow button to save it.

## Cells and ranges

### Specifying cell locations with column letter, row number format

`=B2` Here we refer to the cell in column B, row 2.

### Specifying absolute cell references with \$ prefixes

The \$ symbol before the column letter and/or row number tells Excel that the reference is absolute and should not change when the formula is copied or moved to another cell. The following examples all specify column B, row 2.

`=\$B\$2` Column and row references are both absolute
`=\$B2` Column reference is absolute, row reference is relative
`=B\$2` Column reference is relative, row reference is absolute

### Specifying ranges with the start:end format

The start:end format is a convenient way to specify a range of cells in a formula.
Here is an example of start:end format when using the `SUM()` formula:
`=SUM(B2:B5)`

## Example dataset

Throughout most of this cheat sheet, we’ll be using this dummy dataset of 5 columns and 6 rows.

 A B C D E 1 1 2 6 World 1/1/2023 2 3 3 21 Solar System 1/2/2023 3 6 5 28 Milky Way 1/3/2023 4 10 7 301 Group 1/4/2023 5 11 325 Laniakea Supercluster 1/5/2023 6 21 13 496 Universe 1/6/2023

## Operators

### Arithmetic operators

`=A2 + A3` Add two values with +. This example returns 3 + 6 = 9
`=A4 - B4` Subtract a value from another with -.This example returns 10 - 7 = 3
`=A6 * B1` Multiply two values with *. This example returns 21 * 2 = 42
`=C3 / B4` Divide two values with /. This example returns 28 / 7 = 4
`=C5%` Convert a value to a percentage with %. This example returns 3.25
`=B1 ^ C1` Raise a value to power with ^. This example returns 2 ^ 6 = 64

### Numeric comparison operators

 Test for equality with = `=A1 = B1` Returns 1 = 2 which is FALSE `=A2 = B2` Returns 3 = 3 which is TRUE Test for inequality with <> `=A1 <> B1` Returns 1 <> 2 which is TRUE `=A2 <> B2` Returns 3 <> 3 which is FALSE Test greater than with > `=A3 > B3` Returns 6 > 5 which is TRUE `=A2 > B2` Returns 3 > 3 which is FALSE Test greater than or equal to with >= `=A3 >= B3` Returns 6 >= 5 which is TRUE `=A2 > B2` Returns 3 >= 3 which is TRUE Test less than with < `=A1 < B1` Returns 1 < 2 which is TRUE `=A2 < B2` Returns 3 < 3 which is FALSE Test less than or equal to with <= `=A1 <= B1` Returns 1 <= 2 which is TRUE `=A2 <= B2` Returns 3 <= 3 which is TRUE

## Logical functions

 Logical NOT with NOT() `=NOT(A1 = B1)` Returns NOT(1 = 2) which is TRUE `=NOT(A1 = B1)` Returns NOT(2=2) which is FALSE Logical AND with AND() `=AND(A1 > 10, B1 < 20)` Returns AND(1 > 10, 2 < 20) which is FALSE `=AND(A1 < 2, B1 < 20)` Returns AND(1 < 2, 2 < 20) which is TRUE Logical OR with OR() `=OR(A1 > 10, B1 < 20)` Returns OR(1 > 10, 2 < 20) which is TRUE `=OR(A1 < 2, B1 < 20)` Returns OR(1 < 2, 2 < 20) which is TRUE Logical XOR with XOR() `=XOR(A1 > 10, B1 < 20)` Returns XOR(1 > 10, 2 < 20) which is TRUE `=XOR(A1 > 10, B1 > 20)` Returns XOR(1 > 2, 2 > 20) which is FALSE

## Data types

• `=ISNUMBER(A1)` Checks if a cell is a number. This will return TRUE
• `=ISTEXT(D1)` Checks if a cell is a text. This will return TRUE
• `=ISLOGICAL(A1)` Checks if a cell is a boolean. This will return FALSE
• `=ISLOGICAL(A1=A1)` Checks if a cell is a boolean. This will return TRUE
• `=N(E1)` Converts to number. This will return 44927: the serial date - the date as a number, counting Dec 31st 1899 as 1
• `=N(D1)` Converts to number. This will return an error, since it’s not a number
• `=VALUETOTEXT(A1)` Convert to text. This will return "1"
• `=TEXT(C6, "0.00E+0")` Convert to formatted text — This will return "4.96E+2"
• `=DATEVALUE("1/1/2022")` Convert text to serial. This will returns 44927: the serial date

## Counting data

• `=COUNT(A5:E5)` Returns 3: the number of cells in the range containing numbers, dates and currencies.
• `=COUNTA(A5:E5)` Returns 4: the number of cells in the range that aren't empty
• `=COUNTBLANK(A5:E5)` Returns 1: the number of cells that are empty or contain the empty string ("")

## Math functions

• `=LOG(100, 10)` Returns 2: the base 10 logarithm of 100
• `=EXP(2)` Returns e ^ 2 = 7.398
• `=MAX(A1:A6, C1:C3, 12) `Returns 28: the largest value in all cell ranges or values inputted
• `=MIN(A1:A6, C1:C3, 12)` Returns 1: the smallest value in all cell ranges or values inputted =MAXA(A1:A6, C1:C3, FALSE) Returns same as MAX(), except TRUE is valued at 1 and FALSE is valued at 0
• `=MINA(A1:A6, C1:C3, FALSE)` Returns same as MIN(), except TRUE is valued at 1 and FALSE is valued at 0
• `=SUM(A1:A6, C1:C3, 12)` Returns 108: the total of all cell ranges or values inputted
• `=AVERAGE(A1:A6, C1:C3, 12)` Returns 12: the mean of all cell ranges or values inputted
• `=MEDIAN(A1:A6, C1:C3, 12)` Returns 10: the median of all cell ranges or values inputted
• `=PERCENTILE.INC(C1:C6, 0.25)` Returns 22.75: the 25th percentile of the cell range
• `=ROUND(PI(), 2)` Returns 3.14: pi rounded to 2 decimal places
• `=CEILING(PI(), 0.1)` Returns 3.2: pi rounded upwards to the nearest 0.1
• `=FLOOR(PI(), 0.1)`  Returns 3.1: pi rounded downwards to the nearest 0.1
• `=VAR.S(B1:B6) `Returns 19.37: sample variance of the cell range
• `=STDEV.S(B1:B6) `Returns 4.40: sample standard deviation of the cell range

## Flow control

### Use a logical condition to determine the return value with `IF(x, yes, no)`

`=IF(cond, return_if_true, return_if_false)`

`=IF(ISBLANK(A5), "A5 is blank", "A5 is not blank") ` Returns "A5 is blank"

Takes a logical condition, cond, as its first argument. If cond is TRUE, IF() returns the value specified in the second argument (return_if_true); if cond is FALSE, IF() returns the value specified in the third argument (return_if_false).

### Use a logical condition to determine the return value with `IF(x, yes, no)`

`=IFS(cond1, return1, cond2, return2) `

`=IFS(A1 > B1, "1st", A2 > B2, "2nd", A3 > B3, "3rd")` Returns "3rd"

Similar to IF(), but allowing multiple pairs of logical conditions and return values. If the first condition, cond1, is TRUE then the function returns the first return value, return1. If the second condition is TRUE, the function returns the second return value; and so on.

### Provide a default value in case of errors with `IFERROR()`

`=IFERROR(value, value_if_error)`

`=IFERROR(A5 / A5, 1)` Returns 1

If the first input does not result in an error then it is returned. If it does result in an error, the second input is returned.

### Choose a return value based on a table of inputs with `SWITCH()`

`=SWITCH(value, choice1, return1, choice2, return2, ...)`

`=SWITCH(MID(D3, 1, 5), "World", "planet", "Solar", "planetary system", "Milky", "galaxy", "Local", "galaxy group")` Returns "galaxy"

Takes a value as its first argument, followed by pairs of choices and return values. If the value matches the first choice, the function returns the first return value; if the value matches the second choice, the function returns the second return value; and so on. If no values match, the function returns an error.

## Conditional computation

### Get the number of cells that meet a condition with `COUNTIF()`

`=COUNTIF(A1:A6, ">5")` Returns 3: the number of cells greater than 5, ignoring blanks

`=COUNTIF(D1:D6, "Milky Way")` Returns 1: the number of cells equal to “Milky Way”

### Calculate the total of cells meeting conditions with `SUMIF()` and `SUMIFS()`

`=SUMIF(A1:A6, ">5")` Returns 37: the sum of elements in A1 to A6 filtered with values greater than 5

`=SUMIF(A1:A6, ">5", B1:B6)` Returns 25: the sum of elements in B1 to B6 corresponding to values in A1 to A6 that are greater than 5

`=SUMIFS(B1:B6, A1:A6, ">5", D1:D6, "<>Local Group")` Returns 18: the sum of B1:B6 where A1:A6 is greater than 5 and D1:D6 is not equal to "Local Group"

### Calculate the mean of cells meeting conditions with `AVERAGEIF()` and `AVERAGEIFS()`

`=AVERAGEIF(A1:A6, ">5")` Returns 12.33: the mean of elements in A1 to A6 filtered with values greater than 8

`=AVERAGEIF(A1:A6, ">5", B1:B6)` Returns 8.33: the mean of elements in B1 to B6 corresponding to values in A1 to A6 that are greater than 5

`=AVERAGEIFS(B1:B6, A1:A6, ">5", D1:D6, "<>Local Group")` Returns 9: the mean of B1:B6 where A1:A6 is greater than 5 and D1:D6 is not equal to "Local Group"

## Text functions and operators

### Basics

=LEN(D5) Returns the length of a string in characters. This example returns 28.

### Combining and splitting strings

• `="Hello " & D1 & "!"` Returns "Hello World!"
• `=REPT(D6, 3)` Repeats text. This example returns "UniverseUniverseUniverse"
• `=TEXTSPLIT(D4, "o") `Splits a string on a delimiter. This example returns "L", "cal Gr", "up" in 3 cells: "Local Group" split on the letter "o"
• `=TEXTSPLIT(D5, {"a","u"})` Splits a string on a delimiter. This example returns "L", "ni", "ke", "S", "percl", "ster" in 6 cells: "Laniakea Supercluster" split on the letter "a" or the letter "u".

### Mutating strings

• `=MID(text, start, [length])` Extracts a substring starting at the position specified in the second argument and with the length specified in the third argument. For example =MID(D6, 4, 5) Returns "verse"
• `=UPPER(text)` Converts the text to uppercase. For example =UPPER(D3) Returns "MILKY WAY"
• `=LOWER(text)` Converts the text to lowercase. For example =LOWER(D3) Returns "milky way"
• `=PROPER(text)` Converts the text to title case. For example =PROPER("milky way") Returns "Milky Way"

## Data manipulation

• `=FILTER(A1:B6, C1:C6>100)` Gets a subset of the cell range in the first input that meets the condition in the second input.
• `=SORT(A1:E6, 4)` Returns the dataset with rows in alphabetical order of the fourth column. Sorts the rows of the data according to values in specified columns.
• `=SORTBY(A1:E6, D1:D6)` Returns the same as the SORT() example. Alternate, more flexible, syntax for sorting. Rather than specifying the column number, you specify an array to sort by.
• `=UNIQUE(A1:A6)` Gets a list of unique values from the specified data.
• `=SEQUENCE(5, 1, 3, 2)` Returns 5 rows and 1 column containing the values 3, 5, 7, 9, 11. Generates a sequence of numbers, starting at the specified start value and with the specified step size
Related  ### What is Data Analysis? An Expert Guide With Examples

Explore the world of data analysis with our comprehensive guide. Learn about its importance, process, types, techniques, tools, and top careers in 2023

Matt Crabtree

15 min

### What is Microsoft Fabric?

Discover how Microsoft Fabric revolutionizes data analytics and learn about how its core features empower businesses to make data-driven decisions.  ### Do Spreadsheets Need a Rethink? With Hjalmar Gislason, CEO of GRID

Richie and Hjalmar Gislason explore the integral role of spreadsheets in today's data-driven world, the limitations of traditional Business Intelligence tools, and the transformative potential of generative AI in the realm of spreadsheets.

### How to Pass the PL-300 Power BI Certification

Learn how to pass the PL-300 certification exam for Power BI—one of the world’s most popular business intelligence tools—in this convenient cheat sheet!

### How to Analyze Data in Google Sheets With Python: A Step-By-Step Guide

Boost your data analysis skills with our step-by-step guide on how to analyze, manipulate and write back data in Google Sheets using Python.

### Performance and Scalability Unleashed: Mastering Single Table Database Design with DynamoDB

One table to rule them all: simplify, scale, and supercharge your NoSQL database!
See MoreSee More