Large Excel Cheat Sheet: Formulas, Shortcuts & Tips

Formulas & functions · Updated November 18, 2026

A large Excel cheat sheet is a quick reference for common formulas, keyboard shortcuts, data tools, formatting, and troubleshooting. For Excel for Microsoft 365 on Windows, start with the formulas and workflow below; use the sections on version differences and Mac shortcuts when they apply.

Start with the worksheet basics

Excel stores information in cells arranged by columns and rows. A cell address combines its column letter and row number: B3 is in column B, row 3. A range such as B2:B10 includes every cell from B2 through B10. Formulas begin with an equals sign, and references let a formula use the values in other cells rather than repeating them.

References change when a formula is copied. A2 is a relative reference, so copying it one column right changes it to B2. An absolute reference, such as $A$2, stays fixed when copied. A mixed reference fixes only one part: $A2 locks the column, while A$2 locks the row. In Windows Excel, press F4 while editing a reference to cycle through these forms; on some Mac keyboards, use Fn+F4 depending on keyboard settings.

Keep one type of information in each column, use a clear header row, and avoid blank rows inside a dataset you plan to sort or filter. Dates should be actual Excel date values rather than text that only looks like a date. These small habits make formulas, tables, charts, and PivotTables much more dependable.

Everyday calculation formulas

These functions cover much of routine spreadsheet work. In each example, the stated cell layout is part of the instruction: change the references to match your own sheet.

  • With numbers in B2:B5 equal to 12, 8, 15, and 5, enter =SUM(B2:B5) to total them; the result is 40.
  • With those same four numbers in B2:B5, enter =AVERAGE(B2:B5) to find their arithmetic mean; the result is 10.
  • With numeric entries in B2:B5, enter =COUNT(B2:B5) to count cells containing numbers; the result is 4. COUNT does not count text.
  • With a list in A2:A5 containing three names and one blank cell, enter =COUNTA(A2:A5); the result is 3 because COUNTA counts nonempty cells.
  • With a value of 14.256 in D2, enter =ROUND(D2,2) to round it to two decimal places; the result is 14.26.

For a quick total, AutoSum is on the Home tab and the Formulas tab. Excel usually proposes the adjacent range above or to the left of the active cell, but check the outline before confirming. Formatting a value to show two decimal places changes how it appears; it does not necessarily round the stored value used in later calculations.

Logic, conditions, and error handling

IF chooses between two results based on a test. With a student’s score in C2, where C2 contains 82, enter =IF(C2>=70,"Pass","Review"); it returns Pass. The test is C2 greater than or equal to 70, the second argument is used when the test is true, and the third when it is false.

COUNTIF and SUMIF apply one condition. With status labels in C2:C10 and exactly six cells containing Paid, enter =COUNTIF(C2:C10,"Paid"); it returns 6. For several conditions, use COUNTIFS or SUMIFS. With regions in A2:A6, products in B2:B6, and sales in C2:C6, suppose the rows where region is East and product is Apples have sales of 12, 9, and 14. Enter =SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Apples"); it returns 35.

IFERROR can replace an error with a chosen value, but it can also hide a problem in the data or calculation. With a numerator of 120 in A2 and a denominator of 0 in B2, enter =IFERROR(A2/B2,0); it returns 0 instead of displaying the division-by-zero error. Use this only when zero is a suitable fallback; otherwise, return a clear message or fix the underlying input.

Find matching information with lookups

XLOOKUP searches one range and returns a corresponding value from another. With product codes in A2:A5, product names in C2:C5, and the lookup code A103 in E2, suppose A103 is on the third row of the list and its corresponding name in C4 is Mug. Enter =XLOOKUP(E2,A2:A5,C2:C5,"Not found"); it returns Mug. The fourth argument supplies a message if the code is absent.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. For older Excel versions, VLOOKUP is a common alternative, though it looks for the match in the first column of its table range and returns a value from a column to its right. Exact-match behavior should be made explicit with its final argument set to FALSE. Check the function’s syntax for the specific version you use before adapting a formula.

Lookups depend on consistent keys. A code stored as text will not always match the same-looking number stored as a number, and leading or trailing spaces can prevent a match. If a lookup unexpectedly returns Not found, compare the underlying values and types, not just their displayed appearance.

Text and date tools

Text functions help clean or combine labels. With A2:C2 containing North, Q2, and Draft, enter =TEXTJOIN(", ",TRUE,A2:C2); it returns North, Q2, Draft. The TRUE argument tells TEXTJOIN to ignore empty cells. CONCAT can join values too, while LEFT, RIGHT, and MID extract characters from text. TRIM removes excess ordinary spaces around and between words, which can help tidy imported data.

Dates are stored as serial values, so a date can be subtracted from another date to calculate elapsed days. Use date formatting to display the value in a readable style. TODAY returns the current date and updates when the workbook recalculates; it is not a fixed timestamp. If you need a date that stays fixed, enter it as a value instead of using a changing formula. Newer text functions can make splitting or extracting text easier, but availability depends on the Excel version.

Tables, sorting, filtering, and charts

For a list that grows over time, convert the range to an Excel table. Select a cell in the data and choose Insert > Table, or use Home > Format as Table. Confirm that the range is correct and that My table has headers is selected when appropriate. Tables add filter controls, expand when new rows are entered next to them, and can make formulas easier to read through named columns.

Use Data > Sort to sort by one or more columns, and Data > Filter to show only rows meeting selected criteria. Select the whole dataset before sorting; sorting only one column can separate values from the records they belong to. To summarize a longer list by category, choose Insert > PivotTable and arrange fields into Rows, Columns, Values, or Filters. A PivotTable summarizes data without requiring you to build every total manually, but refresh it after the source data changes.

To visualize a selected dataset, choose Insert > Recommended Charts and review whether the proposed chart fits the question. A column chart works well for comparing categories, while a line chart is often useful for values changing over time. Add a meaningful title and label axes when readers might not know what the values represent. Avoid using a chart simply because it is available: a small, clearly labeled table can be easier to compare precisely.

Formatting and conditional formatting

Use number formats to make values understandable without changing their underlying data. Home > Number includes formats such as currency, percentage, date, and decimal places. A percentage format displays 0.25 as 25%; typing 25 and applying percentage formatting instead displays 2500%. For that reason, check whether a percentage is stored as a decimal fraction before formatting it.

Conditional formatting highlights cells based on rules. Select the relevant range, then choose Home > Conditional Formatting > Highlight Cells Rules to apply options such as greater than, less than, or duplicate values. Color scales can show relative highs and lows, but include a legend or clear labels if the colors carry meaning for other readers. Formatting is a visual cue, not a substitute for a data field that records the actual category or status.

Useful Windows shortcuts and Mac notes

Shortcuts can speed up repetitive work. The following are common Windows Excel shortcuts; on Mac, many Ctrl-based editing shortcuts use Command instead. Keyboard layouts, function-key settings, and Excel preferences can affect some combinations.

  • Ctrl+C, Ctrl+X, and Ctrl+V copy, cut, and paste; on Mac, use Command+C, Command+X, and Command+V.
  • Ctrl+Z undoes an action and Ctrl+S saves; on Mac, use Command+Z and Command+S.
  • Ctrl+F opens Find, and Ctrl+H opens Replace; on Mac, use Command+F and Command+Shift+H in Excel for many installations.
  • Ctrl+1 opens Format Cells on Windows. On Mac, Command+1 is the usual equivalent.
  • Ctrl+T creates a table from a selected range on Windows. If the shortcut differs on your Mac setup, use Insert > Table.
  • Ctrl+Arrow moves to the edge of a data region in that direction on Windows. On Mac, Command+Arrow commonly moves to the edge of the region.
  • Ctrl+; inserts the current date in a cell on Windows. Shortcuts can differ by Mac keyboard and regional setup, so use the date command or enter a fixed date if the combination is unavailable.

To show the ribbon KeyTips on Windows, press Alt and follow the displayed letters; the exact sequence can vary with the current ribbon layout and language. Shortcuts are useful, but menu paths are a reliable fallback when working on a different operating system or keyboard.

Common problems and how to diagnose them

A formula displayed as text may have been entered into a cell formatted as Text, or it may begin with an apostrophe. Change the cell format to General, then re-enter the formula. If Excel displays the formula rather than its result across a worksheet, check whether Show Formulas is enabled on the Formulas tab.

When a formula does not update, check whether calculation is set to Automatic under Formulas > Calculation Options. A #DIV/0! error means a formula divided by zero or an empty denominator; #N/A often means a lookup did not find a match; #VALUE! commonly indicates an unexpected value type. Fix the source or formula where possible rather than hiding every error.

If a total is too small, confirm that the selected range includes all intended rows and that the entries are numbers rather than number-like text. If sorting produces strange order, inspect mixed data types and blanks. For formulas using commas between arguments, some regional settings use semicolons instead; the separator depends on the system’s list-separator settings, not on a different calculation.

Version differences and choosing the right tool

Basic functions such as SUM, IF, COUNTIF, and VLOOKUP work across many Excel releases. Newer dynamic-array functions can reduce the need to copy formulas down a column: XLOOKUP, XMATCH, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Microsoft 365 or Excel 2021 and later. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. A workbook opened in an older release may show compatibility issues or fail to calculate a function it does not support.

Excel for the web and Excel for Mac offer many of the same core features, but ribbon placement, shortcut behavior, and the timing of feature availability can differ. If you share a workbook with people using older software, prefer widely supported functions or confirm their version before building a workflow around a newer function. For a very large or recurring dataset, Power Query, available through Data > Get Data in supported Excel editions, can help import and transform data more repeatably than manual cleanup.

Frequently Asked Questions

What are the most useful Excel formulas to learn first?
Start with SUM, AVERAGE, COUNT, COUNTA, IF, COUNTIF, and SUMIF. Add XLOOKUP if your Excel version supports it, then learn absolute references such as $A$1 so copied formulas behave as intended.

How do I make a formula apply to an entire column?
Convert the dataset to a table using Insert > Table, then enter the formula in that column; Excel commonly fills the calculated column automatically. For an ordinary range, enter the formula and use the fill handle to copy it down, checking that references adjust correctly.

Why is Excel showing a formula instead of its answer?
The cell may be formatted as Text, the entry may start with an apostrophe, or Show Formulas may be on. Change the cell to General and re-enter the formula, or turn off Show Formulas on the Formulas tab.

Can I use this cheat sheet in Google Sheets?
Many basic formulas are shared, but menus, shortcuts, and function availability differ. Check the Google Sheets function list and interface rather than assuming an Excel-specific feature or ribbon path works the same way.

How can I print an Excel cheat sheet?
Copy the sections you need into a worksheet or document, then use File > Print to review the preview, orientation, margins, and scaling. A single page may make text too small, so choose readable sizing over forcing every topic onto one sheet.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.