Whether you're crunching numbers for work, tracking a budget, or building your first financial model, Excel remains the backbone of everyday data work. This ch…
Whether you're crunching numbers for work, tracking a budget, or building your first financial model, Excel remains the backbone of everyday data work. This cheat sheet covers the shortcuts, formulas, and features you'll actually use — bookmark it for quick reference.
Table of Contents
- Essential Keyboard Shortcuts
- Navigation & Selection
- Formatting Shortcuts
- Core Formulas Every User Needs
- Lookup & Reference Formulas
- Text Formulas
- Date & Time Formulas
- Logical Formulas
- PivotTables in 5 Steps
- Data Tools
- Charts Quick Guide
- Common Errors & What They Mean
- Pro Tips
Essential Keyboard Shortcuts
| Action |
Windows |
Mac |
| New workbook |
Ctrl + N |
Cmd + N |
| Open |
Ctrl + O |
Cmd + O |
| Save |
Ctrl + S |
Cmd + S |
| Save As |
F12 |
Cmd + Shift + S |
| Undo |
Ctrl + Z |
Cmd + Z |
| Redo |
Ctrl + Y |
Cmd + Y |
| Copy |
Ctrl + C |
Cmd + C |
| Paste |
Ctrl + V |
Cmd + V |
| Paste Special |
Ctrl + Alt + V |
Cmd + Ctrl + V |
| Cut |
Ctrl + X |
Cmd + X |
| Find |
Ctrl + F |
Cmd + F |
| Find & Replace |
Ctrl + H |
Cmd + Shift + H |
| Fill Down |
Ctrl + D |
Cmd + D |
| Fill Right |
Ctrl + R |
Cmd + R |
| Insert row/column |
Ctrl + + |
Cmd + + |
| Delete row/column |
Ctrl + - |
Cmd + - |
| Autosum |
Alt + = |
Cmd + Shift + T |
| Insert new sheet |
Shift + F11 |
Shift + Fn + F11 |
| Toggle formula view |
`Ctrl + `` |
`Cmd + `` |
| Repeat last action |
F4 |
Cmd + Y |
| Insert current date |
Ctrl + ; |
Cmd + ; |
| Insert current time |
Ctrl + Shift + ; |
Cmd + Shift + ; |
Navigation & Selection
| Action |
Shortcut |
| Jump to edge of data region |
Ctrl + Arrow key |
| Select to edge of data region |
Ctrl + Shift + Arrow key |
| Select entire row |
Shift + Space |
| Select entire column |
Ctrl + Space |
| Select entire worksheet |
Ctrl + A |
| Go to a specific cell |
Ctrl + G or F5 |
| Move to next worksheet |
Ctrl + Page Down |
| Move to previous worksheet |
Ctrl + Page Up |
| Jump to cell A1 |
Ctrl + Home |
| Jump to last used cell |
Ctrl + End |
Formatting Shortcuts
| Action |
Shortcut |
| Bold |
Ctrl + B |
| Italic |
Ctrl + I |
| Underline |
Ctrl + U |
| Add border |
Ctrl + Shift + 7 |
| Remove border |
Ctrl + Shift + Underscore |
| Currency format |
Ctrl + Shift + 4 |
| Percentage format |
Ctrl + Shift + 5 |
| Number format (2 decimals) |
Ctrl + Shift + 1 |
| Open Format Cells dialog |
Ctrl + 1 |
| Wrap text |
Home tab → Wrap Text |
| Merge & Center |
Home tab → Merge & Center |
Core Formulas Every User Needs
=SUM(A1:A10) → Adds a range of numbers
=AVERAGE(A1:A10) → Calculates the mean
=MIN(A1:A10) → Smallest value
=MAX(A1:A10) → Largest value
=COUNT(A1:A10) → Counts numeric cells
=COUNTA(A1:A10) → Counts non-empty cells
=COUNTIF(A1:A10,">100") → Counts cells matching a condition
=SUMIF(A1:A10,">100") → Sums cells matching a condition
=SUMIFS(A1:A10,B1:B10,"Kenya",C1:C10,">500") → Sums with multiple conditions
=ROUND(A1,2) → Rounds to 2 decimal places
=ABS(A1) → Absolute value
Lookup & Reference Formulas
=VLOOKUP(lookup_value, table_array, col_index, FALSE)
→ Classic vertical lookup (FALSE = exact match)
=XLOOKUP(lookup_value, lookup_array, return_array)
→ The modern replacement for VLOOKUP/HLOOKUP — searches left or right,
handles errors gracefully, and is far more flexible
=INDEX(array, row_num, col_num)
=MATCH(lookup_value, lookup_array, 0)
→ INDEX + MATCH together is a powerful, flexible alternative to VLOOKUP
=HLOOKUP(lookup_value, table_array, row_index, FALSE)
→ Horizontal version of VLOOKUP
Tip: If you're still using VLOOKUP, switching to XLOOKUP will save you headaches — no more counting columns, and it works whether your answer is to the left or right of your lookup value.
Text Formulas
=CONCAT(A1," ",B1) → Joins text together
=TEXTJOIN(", ",TRUE,A1:A5) → Joins a range with a delimiter, ignoring blanks
=LEFT(A1,3) → First 3 characters
=RIGHT(A1,3) → Last 3 characters
=MID(A1,2,5) → 5 characters starting at position 2
=TRIM(A1) → Removes extra spaces
=UPPER(A1) / =LOWER(A1) → Changes case
=PROPER(A1) → Capitalizes each word
=LEN(A1) → Counts characters
=SUBSTITUTE(A1,"old","new") → Replaces text
Date & Time Formulas
=TODAY() → Current date
=NOW() → Current date and time
=DATEDIF(start,end,"D") → Days between two dates
=NETWORKDAYS(start,end) → Working days between two dates
=EOMONTH(date,0) → Last day of the month
=YEAR(A1) / MONTH(A1) / DAY(A1) → Extracts date parts
=WEEKDAY(A1) → Day of the week as a number
Logical Formulas
=IF(A1>100,"High","Low")
→ Basic conditional logic
=IFS(A1>90,"A",A1>80,"B",A1>70,"C",TRUE,"F")
→ Multiple conditions without nested IFs
=AND(A1>0,B1>0)
=OR(A1>0,B1>0)
→ Combine conditions
=IFERROR(A1/B1,"N/A")
→ Returns a fallback value instead of an error
PivotTables in 5 Steps
- Click anywhere inside your data range.
- Go to Insert → PivotTable.
- Choose where to place it (new worksheet is usually best).
- Drag fields into Rows, Columns, Values, and Filters.
- Right-click any value to change summary type (Sum, Count, Average, etc.).
Pro tip: Convert your source data into an Excel Table (Ctrl + T) before building a PivotTable — it automatically expands the range when new rows are added.
Data Tools
| Feature |
Where to find it |
What it does |
| Sort |
Data tab |
Order rows by one or more columns |
| Filter |
Data tab |
Show only rows matching criteria |
| Conditional Formatting |
Home tab |
Highlight cells based on rules |
| Data Validation |
Data tab |
Restrict what can be entered in a cell |
| Remove Duplicates |
Data tab |
Deletes duplicate rows |
| Text to Columns |
Data tab |
Splits one column into several |
| Flash Fill |
Ctrl + E |
Auto-fills based on a detected pattern |
| Freeze Panes |
View tab |
Keeps headers visible while scrolling |
Charts Quick Guide
- Column/Bar chart — comparing categories
- Line chart — trends over time
- Pie chart — parts of a whole (use sparingly, max 5–6 slices)
- Scatter chart — relationship between two numeric variables
- Combo chart — mixing chart types (e.g., bars + a trend line)
Quick insert: select your data → Alt + F1 (Windows) inserts a default chart instantly.
Common Errors & What They Mean
| Error |
Meaning |
#DIV/0! |
Division by zero |
#N/A |
Lookup value not found |
#NAME? |
Excel doesn't recognize the formula name (typo) |
#REF! |
A cell reference is invalid (often after deleting cells) |
#VALUE! |
Wrong type of argument (e.g., text where a number is expected) |
#NUM! |
Invalid numeric value in a formula |
#NULL! |
Incorrect range reference (usually a missing comma) |
Pro Tips
- Use named ranges (Formulas → Define Name) instead of cell references to make formulas easier to read.
- Lock formulas while dragging with absolute references:
$A$1 instead of A1.
- Use Ctrl + ` to instantly toggle between values and formulas — great for auditing a sheet.
- Combine XLOOKUP + IFERROR for lookups that fail gracefully instead of throwing errors.
- Set up Data Validation dropdown lists to reduce data-entry mistakes in shared sheets.
- Protect finished sheets: Review → Protect Sheet to prevent accidental edits.
Got a favorite Excel trick that didn't make this list? Let us know in the comments below.