Excel Cheat Sheet

treyb

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:

Open Microsoft Excel.
Click on the "Help" menu at the top of the screen.
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.
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

Click on the cell where you want to add a comment.
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".
This will open a small text box next to the cell, where you can type your comment.
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	Local Group	1/4/2023
5		11	325	Laniakea	1/5/2023
				Supercluster
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 = B2) 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 > 10, 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 return 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 IFS(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.

Credit: Datacamp.com