Excel Cheat Sheet

We created this Excel Cheat Sheet for students of our Excel Bootcamp and Business Intelligence with Excel courses. But we're now sharing it with anyone that wants to learn and remember some of the key formulas, functions, and keyboard shortcuts in Excel, and have a quick reference guide when using Excel.

Want to download a PDF version of this Excel Cheat Sheet?

Enter your email below and we'll send it to you 👇

Unsubscribe anytime.

If you’ve stumbled across this cheatsheet and are just starting to learn Excel, you've made a great choice! Excel is wildly popular amongst developers and companies alike and is a great skill to learn if you're interested in working in a professional environment.

However, if you're stuck in an endless cycle of YouTube tutorials and want to start building real world projects, become a professional, have fun and actually get hired, then come join the Zero To Mastery Academy.

You'll learn Excel from actual industry professionals alongside thousands of students in our private Discord community.

You'll not only learn to become a top 10% Excel user by learning advanced topics most courses don't cover. But you'll also build an interactive, user-driven personal budget and expense tracker that lets you compare your income and expenses, plan your spending with precision, determine what percentage of your income you'd like to save, analyze your spending habits, and more.

Just want the cheatsheet? No problem! Please enjoy and if you'd like to submit any suggestions, feel free to email us at support@zerotomastery.io

Keyboard Shortcuts

Excel for Windows Excel for Mac Shortcut Description
Ctrl+Home Fn+Ctrl+Left Arrow Navigates to cell A1 of the given worksheet.
Ctrl+Right Arrow Ctrl+Right Arrow Navigates to the right edge of the current row, in the current data region.
Ctrl+Left Arrow Ctrl+Left Arrow Navigates to the left edge of the current row, in the current data region.
Ctrl+Up Arrow Ctrl+Up Arrow Navigates to the top edge of the current column, in the current data region.
Ctrl+Down Arrow Ctrl+Down Arrow Navigates to the bottom edge of the current column, in the current data region.
Ctrl+End Ctrl+End Navigates to the last cell used in the current worksheet.

Selecting Cells

Excel for Windows Excel for Mac Shortcut Description
Ctrl+Spacebar Ctrl+Spacebar Selects an entire worksheet column.
Shift+Spacebar Shift+Spacebar Selects an entire worksheet row.
Ctrl+Shift+Up Arrow Ctrl+Shift+Up Arrow Extends the selection of cells to the last cell at the top edge of the current data region.
Ctrl+Shift+Down Arrow Ctrl+Shift+Down Arrow Extends the selection of cells to the last cell at the bottom edge of the current data region.
Ctrl+Shift+Right Arrow Ctrl+Shift+Right Arrow Extends the selection of cells to the last cell at the right edge of the current data region.
Ctrl+Shift+Left Arrow Ctrl+Shift+Left Arrow Extends the selection of cells to the last cell at the left edge of the current data region.
Ctrl+Shift+Home Fn+Ctrl+Right Arrow Extends the selection of cells up and to the left, to cell A1.
Ctrl+Shift+End Fn+Ctrl+Shift+Right Arrow Extends the selection of cells down and to the right, to the last used cell in the worksheet.
Ctrl+A Command+A Selects all the cells in the current data region of the worksheet.

Editing Cells

Excel for Windows Excel for Mac Shortcut Description
Ctrl+C Command+C Copies the selected cells or content.
Ctrl+V Command+V Pastes the copied cells or content.
Ctrl+Alt+V Command+Ctrl+V Displays the Paste Special dialog box; available only after something has been copied to the clipboard.
Ctrl+X Command+X Cuts the selected cells or content.
Ctrl+F Command+F Displays the Find and Replace dialog, with the Find tab selected.
Ctrl+H Ctrl+H Displays the Find and Replace dialog, with the Replace tab selected.

Formatting Cells

Excel for Windows Excel for Mac Shortcut Description
Ctrl+1 Ctrl+1 Displays the format cells dialog box.
Ctrl+B Command+B Applies or removes bold formatting.
Ctrl+U Command+U Applies or removes underline formatting.
Ctrl+I Command+I Applies or removes italic formatting.

Editing Data & Formulas

Excel for Windows Excel for Mac Shortcut Description
Alt+Enter Ctrl+Option+Return Moves the cursor to a new line in the cell being edited.
Shift+Right Arrow Shift+Right Arrow Selects a character to the right of cursor.
Shift+Left Arrow Shift+Left Arrow Selects a character to the left of cursor.
F2 Control+U Edits (places the cursor in) the active cell.
F9 Fn+F9 Calculates all worksheets in all open workbooks.
F4 F4 Cycles through combinations of absolute and relative references for the selected cell reference.

Working With Worksheets & Workbooks

Excel for Windows Excel for Mac Shortcut Description
Ctrl+O Command+O Displays the menu for opening a workbook.
Ctrl+N Command+N Creates a new workbook.
Ctrl+W Command+W Closes the active workbook window.
Ctrl+S Command+S Saves the current workbook.
Shift+F11 Fn+Shift+F11 Inserts a new worksheet.

Miscellaneous

Excel for Windows Excel for Mac Shortcut Description
Ctrl+Z Command+Z Undo last action.
Ctrl+Y Command+Y Redo last action.
Ctrl+Shift+L Command+Shift +F Adds or removes Autofilters from the current data region.
Ctrl+T Ctrl+T Inserts a table based on either the current selection or the current data region.
Alt+F11 Fn+Option+F11 Displays the VBA Editor.
F1 F1 Displays the Excel Help task pane.
F7 F7 Displays the Spellcheck dialog box.

The 10% of Excel Functions You’ll Use 99% of the Time

Brackets surrounding an argument in function syntax (e.x., [argument1]) indicate that the argument is optional.

Date & Time Functions

Function Description Syntax
DATE Returns a date based on inputs of year, month, and day. DATE(year,month,day)
DATEDIF Calculates the number of days, months, or years between two dates. DATEDIF(start_date,end_date,unit)
DAY Converts a date value to a day of the month. DAY(serial_number)
EOMONTH Returns the date value of the last day of the month before or after a specified number of months. EOMONTH(start_date, months)
MONTH Converts a date value to a month. MONTH(serial_number)
NETWORKDAYS Returns the number of whole workdays between two dates. NETWORKDAYS(start_date, end_date, [holidays])
NOW Returns the current date and time. NOW() - The NOW function syntax has no arguments.
TODAY Returns today's date. TODAY() - The TODAY function syntax has no arguments.
WEEKDAY Converts a date value to a day of the week. WEEKDAY(serial_number,[return_type])
YEAR Converts a date value to a year. YEAR(date_value)

Financial Functions

Function Description Syntax
FV Returns the future value of an investment based on periodic, constant payments and a constant interest rate. FV(rate,num_periods,payment,[present_value],[type])
PMT Calculates the payment on a loan based on constant payments and a constant interest rate. PMT(rate, num_periods, present_value, [future_value], [type])

Information Functions

Function Description Syntax
ISBLANK Checks whether a value is blank, and returns TRUE or FALSE. ISBLANK(value)
ISERROR Checks whether a value is an error, and returns TRUE or FALSE. ISERROR(value)
ISNUMBER Checks whether a value is a number, and returns TRUE or FALSE. ISNUMBER(value)

Logical Functions

Function Description Syntax
AND Tests whether all arguments are TRUE, and returns TRUE if so, FALSE if not. AND(logical1,logical2,…)
IF Returns one value if a specified logical condition is met, and an alternate value if it is not. IF(logical_test,value_if_true,value_if_false)
IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. IFERROR(value, value_if_error)
NOT Changes FALSE to TRUE, and TRUE to FALSE. NOT(logical)
OR Tests whether any arguments are TRUE, and returns TRUE if so, FALSE if not. OR(logical1,logical2,…)

Lookup Functions

Function Description Syntax
HLOOKUP Searches for a lookup value in the top row of an range; if a match is found, HLOOKUP returns the value of a cell in the same column, but offset a specified number of rows down. HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
INDEX Uses an index to choose a value from a reference or array. INDEX(array, row_num, [column_num])
MATCH Looks up values in a reference or array, and returns their position. MATCH(lookup_value, lookup_array, [match_type])
VLOOKUP Searches for a lookup value in the first column of a range; if a match is found, VLOOKUP returns the value of a cell in the same row, but offset a specified number of columns to the right. VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Mathematical Functions

Function Description Syntax
ABS Returns the absolute value of a number. ABS(number)
MOD Returns the remainder after a number is divided by another number. MOD(number, divisor)
ROUND Rounds a number to a specified number of digits. ROUND(number, num_digits)
ROUNDDOWN Rounds a number down, toward zero. ROUNDDOWN(number, num_digits)
ROUNDUP Rounds a number up, away from zero. ROUNDUP(number, num_digits)
RAND Returns a random real number between 0 and 1. The RAND function syntax has no arguments.
RANDBETWEEN Returns a random integer between two integers you specify. RANDBETWEEN(bottom, top)
SUM Adds all the numbers in a range of cells. SUM(number1,[number2],...)
SUMIF Sums the values in a range that meet criteria that you specify. SUMIF(range, criteria, [sum_range])
SUMIFS Adds all of its arguments that meet multiple criteria. SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays. SUMPRODUCT(array1, [array2], [array3], ...)

Statistical Functions

Function Description Syntax
AVERAGE Returns the average (arithmetic mean) of the arguments. AVERAGE(number1, [number2], ...)
AVERAGEIF Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. AVERAGEIF(range, criteria, [average_range])
AVERAGEIFS Returns the average (arithmetic mean) of all cells that meet multiple criteria. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
COUNT Counts how many numbers are in the list of arguments. COUNT(value1, [value2], ...)
COUNTA Counts how many values (numeric and non-numeric) are in the list of arguments. COUNTA(value1, [value2], ...)
COUNTBLANK Count the number of empty cells in a range of cells. COUNTBLANK(range)
COUNTIF Counts the number of cells within a range that meet the given criteria. COUNTIF(range, criteria)
COUNTIFS Counts the number of cells within a range that meet multiple criteria. COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
MAX Returns the maximum value in a list of arguments. MAX(number1, [number2], ...)
MEDIAN Returns the median of the given numbers. MEDIAN(number1, [number2], ...)
MIN Returns the minimum value in a list of arguments. MIN(number1, [number2], ...)
STDEV.P Calculates standard deviation based on the entire population, given as arguments. STDEV.P(number1,[number2],...)

Text Functions

Function Description Syntax
CONCATENATE Joins two or more text strings into one string. CONCATENATE(text1, [text2], ...)
FIND Returns the starting position of one text string within another text string (case sensitive). FIND(find_text, within_text, [start_num])
LEFT Returns the first character or characters in a text string, based on the number of characters you specify. LEFT(text, [num_chars])
LEN Returns the number of characters in a text string. LEN(text)
LOWER Converts text to lowercase. LOWER(text)
MID Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. MID(text, start_num, num_chars)
PROPER Capitalizes the first letter in each word of a text string. PROPER(text)
RIGHT Returns the rightmost characters from a text string. RIGHT(text)
SUBSTITUTE Substitutes new text for old text in a text string. SUBSTITUTE(text, old_text, new_text, [instance_num])
TEXT Changes the way a number appears by applying formatting to it with format codes. TEXT(value, text_format)
TRIM Removes all spaces from text except for single spaces between words. TRIM(text)
UPPER Converts text to uppercase. UPPER(text)

Handy Excel Formula & Function “Recipes”

Offset date values with the DATE function

Scenario Syntax
Dynamically calculate a date 3 months from the current date. =DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))

Use EOMONTH to return the first day of the month

Scenario Syntax
Dynamically return the first day of the current month. =EOMONTH(TODAY(),-1)+1

Error-proof lookups with IFERROR and VLOOKUP

Scenario Syntax
Return a customized error message if VLOOKUP can't find a value. =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Value not found.")

Classify a lookup value as "found" or "not found" in a list with ISERROR and MATCH

Scenario Syntax
Return "Found" if the keyword "Excel" is found in a list of names in column A, and "Not found" otherwise. =IF(ISERROR(MATCH("Excel",A:A,0)),"Not found","Found")

Create a multi-level classification with nested IF functions

Scenario Syntax
Classify a product price in cell A1 as "High" (> $1,000), "Medium" (>= $200), or "Low" (< $200) =IF(A1>1000,"High",IF(A1>=200,"Medium","Low"))

Apply complex logical conditions with IF + AND

Scenario Syntax
Calculate whether a salesperson qualified for a bonus by testing whether they exceeded their sales goal of $1,000,000 and their new accounts goal of 20. The value for sales is in cell A1, while the value for new accounts is in cell B1. =IF(AND(A1>1000000,B1>20),"Yes","No")

Combine INDEX and MATCH for two-way lookups

Scenario Syntax
Return a grade from a two-way matrix (column AND row headers) of student names and class names in cells A1:J10, at the intersection of "Excel" (rows) and "Travis" (columns). =INDEX(A1:J10,MATCH("Excel",A1:A10,0),MATCH("Travis",A1:J1,0))

Randomly sample data with RANDBETWEEN and INDEX

Scenario Syntax
Randomly select a name from a list of 10 names in cells A1:A10. =INDEX(A1:A10,RANDBETWEEN(1,10))

Use TEXT to return the name of the current day of the week

Scenario Syntax
Return the name of the current day of the week. =TEXT(TODAY(),"dddd")

Dynamically extract a person's first name with FIND and LEFT

Scenario Syntax
Return the first name from a person's name stored in cell A1, regardless of length. =LEFT(A1,FIND(" ",A1)-1)

Remove multiple characters from a text string with nested SUBSTITUTE functions

Scenario Syntax
Remove all periods and commas from a text string in cell A1. =SUBSTITUTE(SUBSTITUTE(A1,".",""),",","")

Common Formula Errors

Common Formula Errors

Error Description
#DIV/0 The formula attempts to divide a number by zero.
#NAME? Some part of the formula references a name (for example, a function name) that Excel doesn't recognize.
#VALUE! One or more function arguments have been supplied with data that is incompatible with the argument.
#REF! The formula references a cell that no longer exists.
###### The value is too wide to fit within its column.

Back To Top