This excel cheat sheet lists the formulas people reach for most, grouped by job: totals, logic, lookup, text, dates and references. The function descriptions follow Microsoft's list of Excel functions by category. [1] Examples use cells A1 to C10 as stand-ins for your own ranges. If a formula uses commas between arguments and your Excel expects semicolons, that depends on your regional settings.
How to use the excel cheat sheet
- Type in the search box to filter by any word in a name, a command or its note.
- Choose a topic to see one group instead of the whole list.
- Read the command in the right-hand column and its meaning on the left.
- Press Print for a clean copy of whatever is shown.
What is in each topic
The excel cheat sheet is split into 6 topics. The table shows how many entries each topic holds and which ones they are, so you can jump straight to the right group with the topic box.
| Topic | Entries | What it covers |
|---|---|---|
| Totals and rounding | 6 | add values; average; round; count cells meeting a test; add cells meeting a test; add with a separate range |
| Logic | 4 | if true or false; both true; either true; trap an error |
| Lookup | 5 | look up in the first column; modern lookup; value by position; position of a value; index and match together |
| Text | 4 | join text; format a number as text; left characters; length |
| Dates | 5 | today's date; current date and time; build a date; days between; add months |
| References | 6 | relative reference; absolute reference; fix the column only; fix the row only; a range; another sheet |
Totals, averages and counting
SUM adds its arguments or the values in a range, AVERAGE returns the mean, and ROUND rounds a number to a number of digits. [1] COUNTIF counts the cells in a range that meet one criterion, and SUMIF adds the cells that meet a criterion. [1] So =SUMIF(A1:A10, "x", B1:B10) adds column B only where column A holds x.
IF returns one value when a condition is true and another when it is false. [1] AND, OR and IFERROR are standard companions: AND needs every test to be true, OR needs one, and IFERROR replaces an error with a value you choose. Those three are listed from our own working.
Lookup formulas
VLOOKUP searches the first column of a range and returns a value from the same row in a column you specify; the last argument FALSE asks for an exact match. [1] XLOOKUP searches a range or array for the first match and returns the corresponding item, with an option for approximate matches. [1] It is available in current versions of Excel, so check yours before sharing a file with someone on an older release.
INDEX returns a value from a range based on a position you give, and MATCH returns the position of a value within a range. [1] Used together as INDEX(B1:B10, MATCH(E1, A1:A10, 0)), they form a lookup that can also look to the left of the key column.
Text, dates and references
CONCAT combines text from several cells or strings, and TEXT formats a number and converts it to text. [1] LEFT and LEN take the first characters of a string and count its length. TODAY returns today's date as a serial number, which is why subtracting one date from another gives days. [1] NOW, DATE and EDATE complete the date group in the sheet.
A reference such as A1 is relative and changes when you copy the formula. Put dollar signs, as in $A$1, to freeze it, or freeze only the column ($A1) or only the row (A$1). A colon makes a range, and Sheet2!A1 points to another sheet. Reference rules are listed from our own working.
Who this page helps
Developers use a cheat sheet to look up a command they have not typed for a while, to learn the names of the options and to compare similar commands. Beginners can read down a topic to see what is possible, and experienced users can search for the one flag they forgot.
Tips for getting the most from the excel cheat sheet
- Use a table or named ranges for lookups, so formulas keep working when rows are added.
- Press F4 while editing a reference to cycle through relative and absolute forms.
- Wrap lookups in IFERROR only when a missing value is expected, so real mistakes are not hidden.
Common mistakes
Most problems come from running a command in the wrong place or with the wrong target. Read a command before you press Enter, replace every placeholder with your own value, and try destructive commands on a test copy first. When a command behaves differently from the note, check the version you have installed and the official documentation.
Where the entries come from
Each command was checked against the documentation listed below this article. Where a command is standard but is not shown on the page we read, the article says it is our own working. Options and defaults change between releases, so run the help command for the version you have installed before you rely on a flag.
What this page does not do
It lists the commands most people need and does not try to cover every option. For the complete syntax, error messages and advanced features, open the official documentation linked in the sources, and keep it beside the cheat sheet while you learn.
Printing and checking
Filter by topic before you print if you only need one section, and the printed page will be shorter. The notes are short on purpose, so keep your textbook or the official documentation open for the full detail and for anything that changes between versions.
What next
Use this often? Add Excel Cheat to your Home screen.
Frequently asked questions
What does this excel cheat sheet include?
Formulas for totals, rounding and counting, logic, lookup, text, dates and cell references, with a short note on each.
Should I use VLOOKUP or XLOOKUP?
XLOOKUP is more flexible and can return from either side, but it needs a recent version of Excel. VLOOKUP works in older versions.
What does the dollar sign do in a reference?
It keeps the column, the row or both fixed when you copy the formula, which is called an absolute reference.
Why does my formula use semicolons?
Some regional settings use semicolons between arguments instead of commas. The functions work the same.
Was this tool helpful?
What went wrong?
We only receive the reason you pick, never what you typed into the tool.
Thanks for your feedback.