Excel cheat sheet

01

Keyboard shortcuts

Excel for Microsoft 365 on Windows. The shortcuts you will use most often.

CtrlC: CopyCopy the selected cells
CtrlV: PastePaste the copied content
CtrlZ: UndoUndo the last action
CtrlY: RedoRedo the action you undid
CtrlS: SaveSave the workbook
Ctrl→: Jump to edgeMove to the last filled cell in the arrow direction. Also works with ↑ ↓ ←.
CtrlShiftL: FilterTurn filters on or off for the data range
Alt=: AutoSumInsert a SUM formula for the numbers above or to the left
F2: Edit cellEdit the cell contents without retyping them

F4: Reference typeWhile editing a formula: A1 → $A$1 → A$1 → $A1
CtrlShift+: InsertInsert cells, rows, or columns. Select entire rows or columns first to insert them directly.
Ctrl-: DeleteDelete the selected cells, rows, or columns
CtrlHome: Go to startMove to A1, or to the first cell below and right of frozen panes
CtrlEnd: Go to last cellMove to the lowest used row in the rightmost used column
Ctrl1: Format CellsOpen the Format Cells dialog
CtrlT: Create TableConvert a range into an Excel table
AltHOI: AutoFit Column WidthFit column width to the data
AltHOA: AutoFit Row HeightFit row height to the contents

Shortcuts that start with Alt (for example Alt, H, O, I) are pressed one key after another, not together. Press Alt to show KeyTips, the small letters on each ribbon button. AutoFit is Alt, H, O, I or a double-click on the right border of the column heading. To fit every column at once, press Ctrl+A first, then Alt, H, O, I.

02

Data types

Excel treats each type of data differently. The wrong type stops formulas from calculating.

Text

Letters, words, or numbers stored as text. Left-aligned by default. Example: Customer Name. A leading apostrophe forces text: '001234 keeps the leading zeros.

Number

Values you can calculate with. Right-aligned by default. Examples: 1234.56, -50, 0.25.

Date and time

Stored as a serial number and displayed as a date. January 1, 1900 is 1. The display order (DD/MM/YYYY or MM/DD/YYYY) follows your Windows regional settings.

Formula

Starts with =. The cell shows the result, and the formula appears in the formula bar. Examples: =A1+B1, =SUM(A1:A10), =IF(B2>100,"OK","Low").

Numbers stored as text are ignored by SUM. Signs: the number is left-aligned and a small green triangle appears in the cell corner. Fix: select the cells, click the warning icon, then Convert to Number. Alternative: Data → Text to Columns → Finish.

03

Number formats

Ctrl+1 opens every option in Format Cells. These shortcuts apply the most common formats.

CtrlShift4: CurrencyFor example RM1,234.50, two decimal places. The currency symbol follows your Windows regional settings. For sales, revenue, and expenses.
CtrlShift5: PercentageNo decimal places: 0.255 shows as 26%. For growth and margins.
CtrlShift1: NumberFor example 1,234.50, with a thousands separator and two decimal places. For quantities and large counts.
CtrlShift3: DateFor example 12-May-26 (day, month, year). For transaction dates and due dates.

CtrlShift2: TimeFor example 2:30 PM (hour and minute). For timestamps and working hours.
CtrlShift~: GeneralReset the number format to General. Font, fill, and borders stay the same.
AltHFP: Format PainterCopy the format of one cell, then click another cell to apply it

To apply a format several times, double-click the Format Painter button on the Home tab. It stays on until you press Esc.

04

Workbooks and worksheets

Dashboard data is usually spread across several sheets. Understand the structure first.

Workbook

One Excel file (.xlsx). A workbook can contain many worksheets.

Worksheet

One tab at the bottom of the window. Double-click a tab to rename it.

Refer to another sheet

Write the sheet name, an exclamation mark !, then the cell address: =Sales!B5. Sheet names with spaces need single quotes: ='Jan Sales'!B5.

Total across sheets

=SUM(Jan!B2:B10)+SUM(Feb!B2:B10) adds the same range from two sheets.

Switch sheets

Ctrl+PgDn moves to the next sheet. Ctrl+PgUp moves to the previous sheet.

New sheet

Shift+F11 inserts a new worksheet.

05

Cells, rows, columns, and ranges

How to read a cell address. You need this before writing formulas.

Cell: Written asA1; MeaningColumn A, row 1. One box.

Range: Written asA1:B10; MeaningFrom A1 to B10: 2 columns × 10 rows = 20 cells.

Entire column: Written asA:A; MeaningEvery row in column A. Many formulas with full-column references can slow down a large workbook.

Entire row: Written as5:5; MeaningEvery column in row 5.

Several ranges: Written as=SUM(A1:A10,C1:C10); MeaningSeparate ranges with a comma to add two separate ranges.

Term	Written as	Meaning
Cell	`A1`	Column A, row 1. One box.
Range	`A1:B10`	From A1 to B10: 2 columns × 10 rows = 20 cells.
Entire column	`A:A`	Every row in column A. Many formulas with full-column references can slow down a large workbook.
Entire row	`5:5`	Every column in row 5.
Several ranges	`=SUM(A1:A10,C1:C10)`	Separate ranges with a comma to add two separate ranges.

In some regional settings, the argument separator is a semicolon (;) instead of a comma.

06

Cell references and the $ sign

The $ sign locks the column letter or row number so it does not change when you copy a formula.

A1: TypeRelative; When the formula is copiedColumn and row both change; Example=A1+10 filled down becomes =A2+10, =A3+10

$A$1: TypeAbsolute; When the formula is copiedColumn and row both stay; Example=$A$1+10 filled down stays =$A$1+10

$A1: TypeMixed, column locked; When the formula is copiedColumn stays A, row changes; ExampleFilled right it stays $A1. Filled down it becomes $A2.

A$1: TypeMixed, row locked; When the formula is copiedRow stays 1, column changes; ExampleFilled right it becomes B$1. Filled down it stays A$1.

Reference	Type	When the formula is copied	Example
`A1`	Relative	Column and row both change	`=A1+10` filled down becomes `=A2+10`, `=A3+10`
`$A$1`	Absolute	Column and row both stay	`=$A$1+10` filled down stays `=$A$1+10`
`$A1`	Mixed, column locked	Column stays A, row changes	Filled right it stays `$A1`. Filled down it becomes `$A2`.
`A$1`	Mixed, row locked	Row stays 1, column changes	Filled right it becomes `B$1`. Filled down it stays `A$1`.

The $ sign locks the part that follows it. Mixed references are useful in two-way tables, such as a multiplication table.

07

Switch reference types with F4

The quickest way to add or remove $ signs.

How

While editing a formula, place the cursor in a cell reference or select it. Then press F4.

Cycle

A1 → $A$1 → A$1 → $A1 → back to A1. Each press moves to the next type.

Example: tax rate

The tax rate is in B1. In C4, type =A4*$B$1 and fill down. The A4 reference follows each row, while B1 stays fixed.

On many laptops you need to hold the Fn key with F4. Outside edit mode, F4 repeats your last action.

08

Math operators

Excel uses keyboard symbols, not handwritten math symbols.

+: OperationAddition; Example=5+3; Result8

-: OperationSubtraction; Example=10-4; Result6

*: OperationMultiplication (asterisk); Example=6*7; Result42

/: OperationDivision (forward slash); Example=20/4; Result5

^: OperationExponent; Example=5^2; Result25

%: OperationPercent; Example=200*10%; Result20

&: OperationJoin text; Example=A1&" "&B1; ResultFirst name, space, last name

Symbol	Operation	Example	Result
`+`	Addition	`=5+3`	8
`-`	Subtraction	`=10-4`	6
`*`	Multiplication (asterisk)	`=6*7`	42
`/`	Division (forward slash)	`=20/4`	5
`^`	Exponent	`=5^2`	25
`%`	Percent	`=200*10%`	20
`&`	Join text	`=A1&" "&B1`	First name, space, last name

Common beginner mistakes: typing the letter x to multiply (=5x3) or the ÷ symbol to divide. Neither is valid. Use * and /. Every formula must start with =. Without it, Excel stores the entry as text.

09

Order of operations (BODMAS)

Excel calculates in this order, from top to bottom. Operators on the same level are calculated from left to right.

1: Operator( ); MeaningParentheses. Anything inside is calculated first.

2: Operator-; MeaningNegation, for example -5

3: Operator%; MeaningPercent

4: Operator^; MeaningExponent

5: Operator* /; MeaningMultiplication and division

6: Operator+ -; MeaningAddition and subtraction

7: Operator&; MeaningJoin text

8: Operator= < > <= >= <>; MeaningComparison

Order	Operator	Meaning
1	`( )`	Parentheses. Anything inside is calculated first.
2	`-`	Negation, for example `-5`
3	`%`	Percent
4	`^`	Exponent
5	`*` `/`	Multiplication and division
6	`+` `-`	Addition and subtraction
7	`&`	Join text
8	`=` `<` `>` `<=` `>=` `<>`	Comparison

=2+3*4 returns 14 because multiplication comes first (3×4 = 12, then 2+12). =(2+3)*4 returns 20 because parentheses come first. Watch out for =-2^2: the result is 4, not -4, because negation is applied before the exponent. Write =-(2^2) to get -4.

10

Comparison operators

The result is TRUE or FALSE. Used in IF, COUNTIF, SUMIF, and other conditional functions.

=: MeaningEqual to; Example (A1 = 80)=A1=80; ResultTRUE

<>: MeaningNot equal to; Example (A1 = 80)=A1<>80; ResultFALSE

>: MeaningGreater than; Example (A1 = 80)=A1>90; ResultFALSE

<: MeaningLess than; Example (A1 = 80)=A1<90; ResultTRUE

>=: MeaningGreater than or equal to; Example (A1 = 80)=A1>=80; ResultTRUE

<=: MeaningLess than or equal to; Example (A1 = 80)=A1<=79; ResultFALSE

Operator	Meaning	Example (A1 = 80)	Result
`=`	Equal to	`=A1=80`	TRUE
`<>`	Not equal to	`=A1<>80`	FALSE
`>`	Greater than	`=A1>90`	FALSE
`<`	Less than	`=A1<90`	TRUE
`>=`	Greater than or equal to	`=A1>=80`	TRUE
`<=`	Less than or equal to	`=A1<=79`	FALSE

Excel uses a single = for equality, not ==. In COUNTIF and SUMIF, put the comparison criteria in quotes: =COUNTIF(B2:B20,">=80").

11

Typing comparison operators

Key positions for a US keyboard layout.

Greater than or equal to

Press Shift + period (.) for >, then press =. The result is >=.

Less than or equal to

Press Shift + comma (,) for <, then press =. The result is <=.

Two characters, not one symbol

>= and <= are two characters typed in sequence. Excel does not accept the math symbols ≥ or ≤ as operators.

Character order

The = sign always comes second. >= and <= are valid. => and =< are not valid operators.

Example in IF

=IF(A1>=80,"Pass","Fail") shows Pass when A1 is 80 or more, and Fail when it is below 80.

12

Common mistakes

Avoid these so your dashboards and reports show the right numbers.

ProblemFix

Hard-coded numbers in formulas

For example =A1*0.06 for tax. When the rate changes, you have to edit every formula. Put 0.06 in one cell (B1) and write =A1*$B$1.

Merged cells in data

Merged cells interfere with sorting, filtering, and lookup functions. Do not merge cells in raw data. For headings, use Format Cells → Alignment → Horizontal → Center Across Selection.

Blank rows inside data

Excel treats the first blank row as the end of the data, so sort, filter, and Ctrl+T cover only part of it. Remove blank rows. Separate data with another sheet or an Excel table (Ctrl+T).

Forgetting $ when copying formulas

=A1*B1 filled down becomes =A2*B2, even though B1 should stay fixed. Write $B$1 or press F4.

Dates stored as text

A left-aligned 1/5/2026 is text, not a date, so date calculations fail. Changing the cell format alone does not fix it. Retype the date in the order your regional settings expect, or use Data → Text to Columns and choose Date.

Trusting VLOOKUP without the last argument

By default VLOOKUP looks for an approximate match (TRUE) and can return the wrong answer. Always set FALSE as the last argument: =VLOOKUP(A1,range,2,FALSE). XLOOKUP uses an exact match by default.

Good practice

Convert ranges into Excel tables (Ctrl+T). Tables expand when you add data, have a name, and support structured references.
Keep constants such as tax rates and exchange rates on one dedicated sheet, for example "Inputs".
Use a consistent color code: input cells yellow, formula cells white, output cells green.
Explain complex formulas with a comment or note on the cell.

13

Importing data

Bring data into Excel from files and other sources.

PDF: HowData → Get Data → From File → From PDF; NotesExcel detects tables in the PDF. Microsoft 365.

Word: HowCopy the table in Word, then paste it into Excel (Ctrl+V); NotesThe simplest method. If the data comes out jumbled, save the Word document as .txt first and import it as text.

CSV / TXT: HowOpen the file directly, or Data → From Text/CSV; NotesChoose the delimiter (comma, tab, semicolon) and the encoding. Use UTF-8 for special characters.

Web: HowData → From Web, then paste the URL; NotesExcel reads tables on the web page. Refresh to update the data.

Picture: HowData → From Picture, from a file or the clipboard; NotesExcel reads table data from an image. Check the result before you use it. Microsoft 365.

Database: HowData → Get Data → From Database (SQL Server, Access, and others); NotesFor large data sets. The connection can be refreshed without copying the data again.

Source	How	Notes
PDF	Data → Get Data → From File → From PDF	Excel detects tables in the PDF. Microsoft 365.
Word	Copy the table in Word, then paste it into Excel (Ctrl+V)	The simplest method. If the data comes out jumbled, save the Word document as `.txt` first and import it as text.
CSV / TXT	Open the file directly, or Data → From Text/CSV	Choose the delimiter (comma, tab, semicolon) and the encoding. Use UTF-8 for special characters.
Web	Data → From Web, then paste the URL	Excel reads tables on the web page. Refresh to update the data.
Picture	Data → From Picture, from a file or the clipboard	Excel reads table data from an image. Check the result before you use it. Microsoft 365.
Database	Data → Get Data → From Database (SQL Server, Access, and others)	For large data sets. The connection can be refreshed without copying the data again.

Dates and number formats sometimes change when you import from CSV or text. Check a few rows after importing.

14

Saving and exporting

Save a workbook in another format to share or report it.

.xlsx: HowSave (Ctrl+S), the default format; When to useEveryday work. Formulas, charts, and formatting are kept.

.csv: HowFile → Save As → CSV UTF-8 (Comma delimited) or CSV (Comma delimited); When to useShare raw data or import it into databases and other systems. Choose UTF-8 if there are special characters.

.pdf: HowFile → Export → Create PDF/XPS Document; When to useFinal reports for clients or managers. Not easy to edit again.

.xls: HowFile → Save As → Excel 97-2003 Workbook; When to useFor recipients still on old versions of Excel. Rarely needed.

.xlsb: HowFile → Save As → Excel Binary Workbook; When to useLarge files with a lot of data. Smaller file size and faster to open.

Print: HowCtrl+P; When to usePaper copies. Set the Print Area first (Page Layout → Print Area → Set Print Area).

Format	How	When to use
`.xlsx`	Save (Ctrl+S), the default format	Everyday work. Formulas, charts, and formatting are kept.
`.csv`	File → Save As → CSV UTF-8 (Comma delimited) or CSV (Comma delimited)	Share raw data or import it into databases and other systems. Choose UTF-8 if there are special characters.
`.pdf`	File → Export → Create PDF/XPS Document	Final reports for clients or managers. Not easy to edit again.
`.xls`	File → Save As → Excel 97-2003 Workbook	For recipients still on old versions of Excel. Rarely needed.
`.xlsb`	File → Save As → Excel Binary Workbook	Large files with a lot of data. Smaller file size and faster to open.
Print	Ctrl+P	Paper copies. Set the Print Area first (Page Layout → Print Area → Set Print Area).

CSV saves only the values of the active sheet. Formulas, formatting, charts, and other sheets are lost, so save an .xlsx copy first. PDF follows the Print Area and Page Setup. Set both before exporting so tables are not split across many pages. A PDF cannot be converted back to Excel cleanly, so always keep the original .xlsx file. To share formulas, send the .xlsx. To share data only, a .csv opens in almost any software.