Moving Around the Sheet
Jump to last used cellCtrl+End
Jump to A1Ctrl+Home
Move to edge of data blockCtrl+Arrow
Select entire columnCtrl+Space
Select entire rowShift+Space
Go To specific cellCtrl+G
Name Box jumpCtrl+F3
Next/previous sheetCtrl+PgUp/Dn
Select data block (Ctrl+A on data)Ctrl+Shift+8
Freeze Panes & Split View
1
View → Freeze Panes → Freeze Top Row keeps headers visible while scrolling long lists
2
Select a cell first, then Freeze Panes to lock everything above/left of it
3
Use Split (View tab) instead of Freeze to scroll two areas of the same sheet independently
4
New Window (View tab) opens the same workbook twice so you can view two sheets side by side
✏️ Data Entry & Editing
Entry Shortcuts
Confirm & move downEnter
Confirm & move rightTab
Edit cell directlyF2
Fill Down / RightCtrl+D/R
Insert today's dateCtrl+;
Insert current timeCtrl+Shift+;
Insert row/columnCtrl++
Delete row/columnCtrl+-
AutoSumAlt+=
Repeat last actionF4
Edit cell commentShift+F2
Flash Fill & AutoFill
Flash FillCtrl+E
1
Type the result you want once next to source data (e.g., extract a first name), Excel detects the pattern and fills the rest
2
Drag the fill handle (bottom-right square of a cell) to copy a value or continue a series (1, 2, 3...)
3
Double-click the fill handle to auto-extend a formula down to match an adjacent column's length
Paste Special
Open Paste SpecialCtrl+Alt+V
1
Paste Values Only strips formulas, leaving just the calculated result — great before sharing/sending
2
Transpose flips rows into columns and vice versa
3
Paste Formats Only copies just cell styling without touching the content
ƒ Essential Formulas
Core Math & Logic Functions
| Formula | What it does |
|---|---|
=SUM(A1:A10) | Adds all values in a range |
=AVERAGE(A1:A10) | Calculates the mean value |
=MIN(A1:A10) / =MAX(A1:A10) | Smallest / largest value in a range |
=COUNT(A1:A10) | Counts cells containing numbers |
=COUNTA(A1:A10) | Counts non-empty cells (any type) |
=COUNTBLANK(A1:A10) | Counts empty cells |
=IF(A1>100,"Yes","No") | Conditional logic — returns one value or another |
=IFS(A1>90,"A",A1>80,"B",TRUE,"C") | Multiple conditions without nesting IFs |
=AND(A1>0,B1>0) / =OR(...) | Combine multiple TRUE/FALSE conditions |
=IFERROR(A1/B1,"Error") | Shows a custom message instead of #DIV/0! etc. |
=SUMIF(range,criteria,sum_range) | Sums values that meet one condition |
=SUMIFS(sum_range,crit_rng1,crit1,...) | Sums values that meet multiple conditions |
=COUNTIF(range,criteria) | Counts cells matching one condition |
=COUNTIFS(range1,crit1,range2,crit2) | Counts cells matching multiple conditions |
=AVERAGEIF(range,criteria,avg_range) | Averages values that meet a condition |
=ROUND(A1,2) | Rounds to a set number of decimal places |
=ABS(A1) | Absolute (always positive) value |
=SUBTOTAL(9,A1:A10) | Sum that ignores filtered-out rows |
💡 Press F4 right after typing a cell reference inside a formula to cycle through absolute/relative reference types: A1 → $A$1 → A$1 → $A1.
🔍 Lookup Functions
VLOOKUP, XLOOKUP & INDEX/MATCH
| Formula | What it does |
|---|---|
=VLOOKUP(val,table,col_num,FALSE) | Looks up a value in the leftmost column, returns a value from a column to the right. Use FALSE for exact match. |
=HLOOKUP(val,table,row_num,FALSE) | Same as VLOOKUP but searches horizontally across the top row |
=XLOOKUP(val,lookup_arr,return_arr) | Modern replacement for VLOOKUP — searches in any direction, no column counting needed |
=INDEX(range,row,col) | Returns the value at a given row/column position in a range |
=MATCH(val,range,0) | Returns the position of a value within a range |
=INDEX(B:B,MATCH(A1,C:C,0)) | INDEX+MATCH combo — more flexible than VLOOKUP, can look left |
⚠️ VLOOKUP can only search left-to-right and breaks if columns are inserted/reordered. XLOOKUP (Excel 365/2021+) or INDEX+MATCH avoid both problems — prefer them in new spreadsheets.
🔤 Text Functions
Combining & Splitting Text
| Formula | What it does |
|---|---|
=A1&" "&B1 or =CONCAT(A1," ",B1) | Joins text from multiple cells |
=TEXTJOIN(", ",TRUE,A1:A10) | Joins a range with a delimiter, ignoring blanks |
=LEFT(A1,3) / =RIGHT(A1,3) | First/last N characters of text |
=MID(A1,2,5) | 5 characters starting at position 2 |
=LEN(A1) | Counts the number of characters |
=TRIM(A1) | Removes extra spaces from text |
=UPPER(A1) / LOWER(A1) / PROPER(A1) | Changes text case |
=SUBSTITUTE(A1,"old","new") | Replaces specific text within a string |
=TEXT(A1,"0.00%") | Formats a number as text in a custom format |
=VALUE(A1) | Converts text that looks like a number into an actual number |
📅 Date & Time Functions
Working with Dates
| Formula | What it does |
|---|---|
=TODAY() | Current date (updates automatically) |
=NOW() | Current date and time |
=DATE(2026,6,19) | Builds a date from year/month/day numbers |
=YEAR(A1) / MONTH(A1) / DAY(A1) | Extracts the year, month, or day from a date |
=DATEDIF(A1,B1,"d") | Days between two dates ("m"=months, "y"=years) |
=WORKDAY(A1,10) | Date 10 business days from a start date |
=NETWORKDAYS(A1,B1) | Counts business days between two dates |
=WEEKDAY(A1) | Returns the day of week as a number |
=EOMONTH(A1,0) | Last day of the month for a given date |
▦ Tables & Named Ranges
Excel Tables (Ctrl+T)
Convert range to TableCtrl+T
1
Formulas auto-fill down to new rows automatically as you add data
2
Filter dropdowns and banded row styling are built in immediately
3
Use structured references like
=Table1[Sales] instead of cell ranges — much more readable4
Charts and PivotTables based on a Table auto-expand when new rows are added
Named Ranges
1
Select a range → type a name directly in the Name Box (top-left) → Enter to create it
2
Use named ranges in formulas:
=SUM(TaxRate) is far clearer than =SUM(B2:B40)3
Manage all names via Formulas tab → Name Manager (Ctrl+F3)
🎨 Conditional Formatting
Visual Formatting Rules
1
Color Scales create an instant heatmap — great for visualizing high/low values across a range
2
Data Bars show an in-cell bar chart proportional to each value
3
Icon Sets (arrows, traffic lights, stars) flag status at a glance
4
Use "Use a formula to determine which cells to format" for custom logic, e.g.
=A1<TODAY() to flag overdue dates5
Manage and reorder rules in Home → Conditional Formatting → Manage Rules
✅ Data Validation
Dropdowns & Input Restrictions
Select the target cellsWhere you want the dropdown or restriction to apply
Open Data ValidationData tab → Data Validation
Choose criteria"List" for a dropdown (type values or reference a range), or Whole Number/Decimal/Date for numeric limits
Add input/error messagesSet a helper tooltip and a custom error alert for invalid entries
💡 Use this for status columns (Open/In Progress/Done), approval forms, or any spreadsheet shared with people who shouldn't type free text.
📈 PivotTables & Charts
Building a PivotTable
Select your dataClick anywhere inside a clean table with headers (or use a named Table)
Insert → PivotTableChoose New Worksheet to keep raw data separate from the summary
Drag fieldsRows = categories to group by; Values = numbers to summarize; Filters = optional slicers
Add a SlicerInsert → Slicer for a clickable, visual filter button panel
Refresh after data changesRight-click the PivotTable → Refresh (or Data → Refresh All)
Charts & Sparklines
New chart on new sheetF11
New chart on same sheetAlt+F1
1
Recommended Charts (Insert tab) lets Excel suggest the best chart type for your selected data
2
Sparklines (Insert → Sparklines) embed a tiny trend chart inside a single cell
3
Right-click a chart → Change Chart Type to swap between bar, line, pie, etc. without rebuilding
🔄 Power Query
Import & Clean Data Automatically
1
Data tab → Get Data → connect to CSV, another workbook, a database, a website, or a folder of files
2
Power Query opens an editor where you can remove columns, filter rows, split text, and merge tables without formulas
3
Every transformation step is recorded — click Close & Load to bring the cleaned result into your sheet
4
The biggest advantage: click Refresh and the entire cleanup process re-runs automatically on updated source data
5
Use Merge Queries to do a VLOOKUP-style join between two data sources visually
💡 Power Query is the best tool for recurring reports — build the cleanup steps once, then just hit Refresh every week/month instead of redoing manual work.
🤖 Macros & VBA Basics
Recording Your First Macro
Enable the Developer tabFile → Options → Customize Ribbon → check "Developer"
Record MacroDeveloper tab → Record Macro → name it and assign a shortcut key
Perform your actionsEverything you click and type is recorded as VBA code behind the scenes
Stop RecordingDeveloper tab → Stop Recording when done
Run it anytimePress your assigned shortcut, or Developer → Macros → Run
⚠️ Save macro-enabled files as .xlsm (not .xlsx) or the macro code will be discarded. Macros from unknown sources can carry malware — only enable macros in files you trust.
VBA Editor Basics
Open VBA editorAlt+F11
Run current macroF5
Step through code line by lineF8
1
Recorded macros land in Modules in the left-hand Project Explorer — edit the code directly here
2
A simple example:
Sub HelloWorld() MsgBox "Hello!" End Sub — pops up a message box🔒 Sharing & Protection
Protecting Sheets & Cells
1
Protect Sheet (Review tab) locks cells from editing — by default ALL cells are locked once protection is on
2
To leave specific cells editable, first select them → Format Cells → Protection tab → uncheck "Locked" before protecting the sheet
3
Protect Workbook Structure prevents others from adding, deleting, or renaming sheets
4
File → Info → Encrypt with Password requires a password just to open the file
Real-Time Co-Authoring
1
Save to OneDrive/SharePoint, then click Share (top-right) to invite others to edit simultaneously
2
You'll see colored cursors and cell highlights showing what others are editing in real time
3
Set link permissions to "Can view" for read-only sharing, or "Can edit" for collaboration
🛠️ Troubleshooting
Common Errors Explained
| Error | Cause & Fix |
|---|---|
#DIV/0! | Dividing by zero or an empty cell — wrap with IFERROR or check the denominator |
#N/A | Lookup value not found — check spelling/spacing, or the lookup range is wrong |
#REF! | Formula refers to a cell that was deleted — re-point the reference |
#VALUE! | Wrong data type (e.g., adding text to a number) — check cell contents |
#NAME? | Misspelled function name or missing quotes around text |
##### | Column too narrow to display the value — widen the column |
Circular Reference | A formula refers to its own cell, directly or indirectly — Excel will flag this on save |
Speeding Up Slow Workbooks
1
Switch to Manual Calculation (Formulas → Calculation Options) on huge workbooks, then press F9 to recalculate on demand
2
Avoid whole-column references like
A:A in formulas — they force Excel to scan a million rows3
Replace volatile functions (
NOW(), TODAY(), OFFSET()) where possible — they recalculate on every change4
Press Ctrl+End to check the "true" last cell — delete stray formatting beyond your real data to shrink file size