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.
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
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
Brackets surrounding an argument in function syntax (e.x., [argument1]) indicate that the argument is optional.
| 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) |
| 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]) |
| 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) |
| 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,…) |
| 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]) |
| 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], ...) |
| 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],...) |
| 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) |
| Scenario | Syntax |
|---|---|
| Dynamically calculate a date 3 months from the current date. | =DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())) |
| Scenario | Syntax |
|---|---|
| Dynamically return the first day of the current month. | =EOMONTH(TODAY(),-1)+1 |
| 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.") |
| 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") |
| 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")) |
| 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") |
| 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)) |
| Scenario | Syntax |
|---|---|
| Randomly select a name from a list of 10 names in cells A1:A10. | =INDEX(A1:A10,RANDBETWEEN(1,10)) |
| Scenario | Syntax |
|---|---|
| Return the name of the current day of the week. | =TEXT(TODAY(),"dddd") |
| Scenario | Syntax |
|---|---|
| Return the first name from a person's name stored in cell A1, regardless of length. | =LEFT(A1,FIND(" ",A1)-1) |
| Scenario | Syntax |
|---|---|
| Remove all periods and commas from a text string in cell A1. | =SUBSTITUTE(SUBSTITUTE(A1,".",""),",","") |
| 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. |