Excel Cheat Sheet: Formulas, Shortcuts & Quick Reference

What Is an Excel Cheat Sheet?

An Excel cheat sheet is a condensed quick-reference guide that puts the most-used formulas, keyboard shortcuts, and feature commands in one place. Whether you open Excel daily for data analysis or only occasionally for reporting, a solid cheat sheet cuts the time you spend searching help menus and trying to remember function syntax.

Excel holds over 400 built-in functions, hundreds of keyboard shortcuts, and dozens of data tools. No one memorizes all of them. What matters is knowing the 20% that handles 80% of real tasks — and having a fast way to look up the rest.

This guide organizes the essential Excel cheat sheet into six categories: core formulas, keyboard shortcuts, pivot tables, conditional formatting, data tools, and exam prep tips for the Microsoft Office Specialist certification. Each section gives you syntax you can paste immediately and notes on when each feature applies.

Coverage includes Excel 2019, Excel 2021, Microsoft 365, and Excel Online. Most formulas and shortcuts work across all versions; where behavior differs, the guide notes it. The Microsoft 365 subscription version has the most features, including newer dynamic array functions covered later in the formulas section.

One more thing to keep in mind: a cheat sheet is most effective when you build it yourself. Use this guide as the foundation, then add the formulas and shortcuts you personally use most. Annotating it with your own examples — the datasets you actually work with, the reports your team runs weekly — turns a generic reference into something genuinely useful that you will reach for automatically rather than leaving in a bookmark you forget to open.

Core Excel Formulas Cheat Sheet

The formulas below cover the functions that appear most often in professional spreadsheets. Each entry shows the syntax, a plain-English description, and a quick example you can paste directly into a cell.

SUM — =SUM(A1:A10) — Adds all values in a range. The most-used function in Excel. Use SUM over manual addition for any range larger than three cells because it auto-updates when rows are inserted or deleted.

AVERAGE — =AVERAGE(B2:B20) — Returns the arithmetic mean of a range. For a median use =MEDIAN(B2:B20). To average while ignoring zeros use =AVERAGEIF(B2:B20,"<>0").

IF — =IF(A2>100,"Over","Under") — Returns one value if a condition is TRUE and another if FALSE. Nest multiple IFs for branching logic: =IF(A2>90,"A",IF(A2>80,"B","C")). In Excel 365 and 2019, the cleaner alternative is IFS() which evaluates multiple conditions without nesting.

VLOOKUP — =VLOOKUP(lookup_value,table_array,col_index,[range_lookup]) — Searches the first column of a range and returns a value from a specified column in the same row. The fourth argument should almost always be FALSE for exact matches. A full breakdown of how to use it and when to switch to XLOOKUP is in the Excel VLOOKUP guide.

INDEX/MATCH — =INDEX(return_range,MATCH(lookup_value,lookup_range,0)) — The professional alternative to VLOOKUP. Unlike VLOOKUP, it can look left, handles column insertions without breaking, and runs faster on large datasets. MATCH finds the row position; INDEX returns the value at that position in any column you choose.

COUNTIF — =COUNTIF(A1:A100,"Completed") — Counts cells that match a single condition. For multiple conditions use COUNTIFS: =COUNTIFS(A1:A100,"Open",B1:B100,">500"). A practical walkthrough of both is in the dedicated Excel COUNTIF guide.

SUMIF / SUMIFS — =SUMIF(A:A,"East",C:C) — Adds values in column C where the matching cell in column A equals East. SUMIFS extends this to multiple criteria columns simultaneously.

Text functions — =LEN(A1) returns character count. =TRIM(A1) removes extra spaces. =LEFT(A1,5) extracts the first five characters. =A1&" "&B1 joins two text strings — the modern alternative to CONCATENATE.

Date functions — =TODAY() inserts today's date. =NOW() adds current date and time. =DATEDIF(start,end,"Y") calculates complete years between two dates — used for age calculations and tenure reports. For deeper coverage of nested examples and error-handling patterns, the Excel formulas guide walks through each function with real-world use cases.

Excel Keyboard Shortcuts Cheat Sheet

Keyboard shortcuts are the single fastest way to speed up Excel work. A user who navigates entirely by mouse spends roughly three times longer on the same tasks as someone who has 20 shortcuts memorized. This section organizes the highest-value shortcuts by category.

Navigation shortcuts — Ctrl+Home jumps to cell A1. Ctrl+End jumps to the last used cell in the worksheet. Ctrl+Arrow keys jump to the last non-empty cell in a direction — the fastest way to move to the edge of a data block without scrolling. Ctrl+G (or F5) opens the Go To dialog for jumping to named ranges or specific cell addresses.

Selection shortcuts — Ctrl+Shift+Arrow extends a selection to the last non-empty cell in a direction. Ctrl+Shift+End selects from the current cell to the last used cell on the sheet. Ctrl+A selects the entire current data region; press again to select the full worksheet. Ctrl+Space selects an entire column; Shift+Space selects an entire row.

Formatting shortcuts — Ctrl+B bolds. Ctrl+I italicizes. Ctrl+U underlines. Ctrl+1 opens the Format Cells dialog for full control over number format, alignment, borders, and fill color. Ctrl+Shift+$ applies Currency format. Ctrl+Shift+% applies Percentage format. Alt+H+O+I auto-fits column width to the widest value in that column.

Formula entry shortcuts — F2 enters edit mode on the active cell. Ctrl+` (grave accent) toggles between showing formula values and formula text — essential for auditing a sheet you did not build. F4 cycles through absolute and relative reference types ($A$1, A$1, $A1, A1) while editing a formula. Ctrl+Enter fills the same formula into all selected cells simultaneously, which is faster than copying and pasting when filling a non-contiguous range.

Undo, redo, and file shortcuts — Ctrl+Z undoes the last action. Ctrl+Y redoes. Ctrl+S saves the current workbook. Ctrl+W closes the current workbook without closing Excel. Ctrl+N opens a new blank workbook. Ctrl+P opens the Print dialog. Ctrl+F opens Find; Ctrl+H opens Find and Replace.

The full printable list including Mac equivalents is in the Excel shortcuts guide. A condensed one-page reference covering the 80 shortcuts that matter most for the MOS exam is in the Excel shortcuts cheat sheet.

Pivot Tables and Conditional Formatting Quick Reference

Pivot tables are Excel's most powerful summary tool and one of the most avoided features — many users find the setup intimidating. Once you learn the three-step pattern, pivot tables become the default approach for any summary task that would otherwise require SUMIFS across multiple columns.

Creating a pivot table — Click anywhere inside your data range. Go to Insert then PivotTable. Accept the defaults which place the pivot on a new worksheet. Drag a text field (like Region or Category) to the Rows area to group by that field. Drag a numeric field to the Values area — Excel defaults to SUM. Right-click the Values heading to change the calculation to COUNT, AVERAGE, MAX, or MIN. A complete step-by-step guide including grouped dates and calculated fields is in the Excel pivot table guide.

Refreshing a pivot table — Pivot tables do not update automatically when source data changes. Right-click anywhere in the pivot table and select Refresh, or press Alt+F5. If new rows were added beyond the original data range, go to PivotTable Analyze, then Change Data Source, and expand the range before refreshing. Using an Excel Table (Ctrl+T) as the source solves the range problem permanently — Tables expand automatically as rows are added.

Conditional formatting overview — Conditional formatting applies visual rules to cells based on their values, making patterns visible at a glance without reading every number. The four most-used rule types are: Highlight Cell Rules (greater than, less than, equal to, duplicate values), Top/Bottom Rules (top 10, above average), Data Bars (in-cell bar charts proportional to value), and Color Scales (gradient fill based on relative value in the range).

Formula-based conditional formatting — For the most powerful rules, select New Rule then choose Use a formula to determine which cells to format. A formula like =$C2>100 can highlight entire rows based on a single column value — one of the most professional techniques in the tool. The dollar sign before C locks the column reference while the row number stays relative, so every row evaluates its own column C. Full examples with multi-condition rules and real-world scenarios are in the conditional formatting guide.

Managing and editing rules — Go to Home, then Conditional Formatting, then Manage Rules to see all rules applied to the current selection or the full sheet. Rules are evaluated top-to-bottom. The Stop If True checkbox prevents lower-priority rules from firing once a higher-priority rule matches. Dragging rows in the Manage Rules dialog reorders priority — useful when highlight rules conflict with each other on the same range.

Data Tools: Sort, Filter, and Dropdown Lists

Excel's data tools handle the messy parts of real-world data — filtering large tables, standardizing input through validation rules, removing duplicates, and splitting imported text. These tools appear less often in tutorials but come up constantly in any role that touches spreadsheets regularly.

Sort — Click any cell in the column to sort by, then use the sort buttons in the Data ribbon. For multi-column sorting, go to Data then Sort and add levels — for example, sort by Region ascending, then by Sales descending within each region. Always verify whether your data has a header row before applying a sort, because Excel sometimes misidentifies the first row. Checking the My data has headers box in the Sort dialog prevents header text from being sorted into the data rows.

Filter — Ctrl+Shift+L toggles AutoFilter on the selected row. Click the dropdown arrow in any header cell to filter by specific values, by color, or by condition such as contains, begins with, or greater than. For extracting matching rows to a different location, use Data then Advanced Filter. In Excel 365, the FILTER function does the same thing dynamically: =FILTER(A2:C100,B2:B100="East") returns only rows where column B equals East and updates automatically when source data changes.

Dropdown lists and data validation — A dropdown list restricts a cell to a predefined set of values, which prevents typos in data entry and forces consistent input across a team. Select the target cells, go to Data then Data Validation, set Allow to List, and enter the values comma-separated or point to a named range.

For a dynamic dropdown that updates automatically when the source list changes, format the source as an Excel Table first. Creating a dependent dropdown — where choices in one cell depend on the selection in another — is one of the most commonly asked Excel interview questions. A step-by-step walkthrough is in the excel drop down list guide.

Remove duplicates — Select the data range. Go to Data then Remove Duplicates. Check which columns should be compared to define a duplicate. Click OK and Excel reports how many rows were removed. This operation has no undo after the dialog closes, so save the file first or work on a copy of the data.

Text to columns — Data pasted from external systems often arrives as a single column that needs splitting — for example Last,First format or dates like 20260507. Select the column, go to Data then Text to Columns, choose Delimited with comma, space, or a custom character, or choose Fixed Width for column positions. Set data types in the final step to prevent Excel from misformatting dates and leading zeros.

Using This Cheat Sheet for MOS Exam Prep

The Microsoft Office Specialist Excel exam — MO-201 for Excel 2019 or MO-365 for Microsoft 365 — is a performance-based test that requires completing real tasks in a live Excel environment rather than answering multiple-choice questions. There is no partial credit, so understanding the exact steps for each procedure matters more than general familiarity with where features live.

The exam covers five main domains: Managing Workbooks and Worksheets (about 15% of the exam), Managing Data Cells and Ranges (about 20%), Creating Tables (about 15%), Applying Formulas and Functions (about 25%), and Managing Charts (about 25%). Formulas and charts together make up roughly half the exam weight, which means the formulas section of this cheat sheet is the highest-priority study area.

For formulas specifically, the MOS exam tests writing correct syntax and interpreting what an existing formula does. Common formula tasks include writing nested IF statements, using VLOOKUP with both exact and approximate match modes, applying SUMIF and SUMIFS, and using financial functions like PMT for loan payments and FV for future value calculations. Practicing these from the formula section of this cheat sheet until you can write each from memory is the right preparation approach.

For data tools, expect tasks involving multi-level sorting, applying conditional formatting using formula conditions (not just presets), creating pivot tables with specific row and column groupings, and setting up data validation dropdown lists. These are exactly the areas most candidates underestimate — they study formulas but skip the data tools section, then run out of time on the exam's second half.

The Microsoft Office Specialist Excel certification guide has the full exam breakdown by domain weight, the 25 most commonly tested tasks, and a practice test set designed to mirror the live exam environment. Using that resource alongside this cheat sheet covers everything tested on the current exam version and gives you a repeatable study framework rather than hoping memorization sticks.