Excel Cheat Sheet

Basic terms

The most important terms before you get into formulas.

CellA single field, addressed by column and row, for example A1

WorksheetA single table within a file

WorkbookThe whole file, can contain several worksheets

Cell referenceA reference to another cell, for example =A1+A2

Formula basics

Every formula starts with an equals sign.

=A1+A2Adds the values of two cells

Relative referenceA1 automatically adjusts when the formula is copied to other cells

Absolute reference$A$1 stays the same when copied, toggle it with F4

AutoFillDrag the small square at the bottom right of a cell to copy a formula across multiple rows

Common functions

Ready made functions for the most frequent tasks, each with an example you can check yourself.

SUM(A1:A10)Adds up all the values in a range. Example: A1=2, A2=3, A3=5 → SUM(A1:A3) = 10

AVERAGE(A1:A10)Calculates the average of a range. Example: A1=2, A2=4, A3=6 → AVERAGE(A1:A3) = 4

IF(A1>10,"big","small")Checks a condition and returns a different value depending on the result. Example: A1=15 → result "big", A1=5 → result "small"

VLOOKUPLooks up a value in a table and returns a matching value from another column. Example: if A1:B3 contains the row "Apple,1.20", =VLOOKUP("Apple",A1:B3,2,0) returns 1.2

COUNT(A1:A10)Counts how many cells in a range contain a number. Example: A1=5, A2="Text", A3=8 → COUNT(A1:A3) = 2

COUNTIF(A1:A10,">10")Only counts the cells in a range that meet a condition. Example: A1=5, A2=12, A3=20 → COUNTIF(A1:A3,">10") = 2

SUMIF(A1:A10,">10",B1:B10)Only adds up the values whose matching cell meets a condition. Example: A1=5, A2=12, B1=100, B2=200 → SUMIF(A1:A2,">10",B1:B2) = 200

Nested IFSeveral IF functions inside each other, to cover more than two possible outcomes. Example: =IF(A1>=90,"Great",IF(A1>=50,"Passed","Failed")), with A1=70 the result is "Passed"

Formatting

So a spreadsheet actually stays readable.

Format cellsSet number format, currency, date or percentage for a cell

Conditional formattingAutomatically color cells when they meet a certain condition

Merge cellsCombines several cells into one large one, often used for headings

Auto fit column widthDouble click the border between two column headers to fit the width automatically

Charts and sorting

Evaluate data and present it clearly.

Insert a chartSelect a range, then pick a suitable chart type from the menu

SortSort a column ascending or descending without mixing up the rows

FilterOnly show rows that meet a certain condition

Freeze top rowKeeps the header row visible while scrolling

Data validation and protection

Avoid input mistakes and protect important areas.

Data validationOnly allows certain values in a cell, for example a dropdown list to pick from

Protect sheetPrevents important formulas or values from being accidentally overwritten

Insert commentLeaves a note on a cell without changing its actual content

Pivot tables

Quickly summarize and analyze large amounts of data.

Pivot tableAutomatically summarizes large datasets, for example a sum per category

Drag and dropDrag fields into rows, columns or values with the mouse to change the analysis

RefreshAfter changing the source data, the pivot table needs to be refreshed manually

Common error values

What these messages in a cell actually mean.

#DIV/0!Division by zero, usually because a referenced cell is empty or zero

#N/ANot available, a searched value wasn't found, common with VLOOKUP

#NAME?Excel doesn't recognize a function name, often a typo

VLOOKUP in detail

Looks up a value in the first column of a range and returns a matching value from another column.

=VLOOKUP(value,range,column_index,0)The 0 at the end forces an exact instead of an approximate match

Column indexCounts the columns of the search range from 1, not the columns of the whole sheet

SortingWithout the 0, the search range needs to be sorted ascending, otherwise the function returns wrong results

#N/AAppears when the searched value doesn't exist in the search range at all

Percentages

Percentages are just regular numbers internally in Excel, only the display is different.

Percentage formatAutomatically displays 0.2 as 20%, the calculation still uses 0.2

Calculate a share=Part/Total, for example =B1/B2 for the percentage share

Percentage change=(New-Old)/Old, a positive result means an increase, a negative one a decrease

Finding cells

Searching a large spreadsheet by hand wastes time.

Ctrl + FOpens a search for a specific value or text

Ctrl + HOpens find and replace, automatically swaps out found values

Ctrl + GOpens go to, jumps directly to a specific cell address

Keyboard shortcuts

The combinations that save the most time day to day.

Ctrl + C / Ctrl + VCopy and paste

Ctrl + arrow keyJumps to the end of a connected range of data

F4Toggles between relative and absolute cell references

Alt + =Automatically inserts a sum formula for the selected range

Ctrl + ZUndoes the last action