Excel Complete Reference — Data Cleanup, Formulas & Fixes

Getting Started — Excel from Zero

Interface, entering data, formulas, formatting, files, sheets — everything a total beginner needs first

What is Excel? A spreadsheet where data lives in cells at the intersection of a column (A, B, C...) and a row (1, 2, 3...). Cell address = column+row e.g. A1. Cells hold text, numbers, or formulas that calculate automatically. File = workbook; workbooks have multiple sheets.

Interface — what is where

Quick Access Toolbar (QAT)Top-left strip (Save, Undo, Redo). Right-click any ribbon button to add it here

RibbonBig toolbar with tabs (Home, Insert, Page Layout...). Click a tab to see its commands

Name BoxBox above column A showing the active cell address (e.g. B7). Click it, type any address, Enter to jump there

Formula BarWide bar next to Name Box — shows full content of selected cell. Click here to edit

Column headers (A, B, C...)Click to select entire column. Drag right edge to resize

Row headers (1, 2, 3...)Click to select entire row. Drag bottom edge to resize

Cell gridMain working area. Click any cell to select it. Active cell has a green border

Sheet tabsBottom — Sheet1, Sheet2, etc. Click to switch sheets. Right-click for rename, colour, hide, copy options

Status barBottom strip — shows Sum, Average, Count of selected numbers automatically. Right-click to add more stats

Zoom sliderBottom-right. Drag right = bigger cells (zoom in), left = see more cells (zoom out)

Files — create, open, save

New blank workbookCtrl+N

Open existing fileCtrl+O

Save (overwrite current)Ctrl+S

Save As (new name/location/format)F12

Close workbookCtrl+W

Quit ExcelAlt+F4

File formats.xlsx = standard | .xlsm = has macros | .csv = plain text, one sheet | .pdf = read-only | .xls = old format (pre-2007)

AutoSave toggle (top-left)Saves to OneDrive every few seconds. Turn on immediately — protects against data loss

Recover unsaved fileFile → Info → Manage Workbook → Recover Unsaved Workbooks

Undo (up to 100 steps)Ctrl+Z

Redo (reverse the undo)Ctrl+Y

Navigating

Move one cell in any directionArrow keys

Jump to edge of data blockCtrl+Arrow

Go to cell A1Ctrl+Home

Go to last used cellCtrl+End

Jump to any cell by addressCtrl+G then type

Next / previous sheetCtrl+PgDn / PgUp

Snap view back to active cellCtrl+Backspace

Freeze top row (headers stay visible)View → Freeze Panes → Freeze Top Row

Freeze first columnView → Freeze Panes → Freeze First Column

Freeze rows AND columnsClick cell just below and right of freeze area → View → Freeze Panes → Freeze Panes

UnfreezeView → Freeze Panes → Unfreeze Panes

Entering and editing data

Type into a cellClick cell → type → Enter moves down, Tab moves right

Edit without erasingF2

Cancel an editEscape

Confirm and stay in same cellCtrl+Enter

Delete cell contentsDelete key

Delete contents AND formattingHome → Clear → Clear All

Line break inside a cellAlt+Enter

Force text storage (stop auto-conversion)Type apostrophe ' first: '007 stores as text "007" not 7. Apostrophe invisible in cell

Insert today's date (static value)Ctrl+;

Insert current time (static value)Ctrl+Shift+;

AutoFill a seriesType two values → select both → drag fill handle (small square at bottom-right of selection)

Flash Fill — auto-complete a patternCtrl+E

Selecting cells

Select one cellClick it

Select a range (A1:C10)Click A1 → hold Shift → click C10. Or click A1 and drag

Select non-adjacent cellsCtrl+Click each one

Select entire columnCtrl+Space

Select entire rowShift+Space

Select entire sheetCtrl+A

Extend selectionShift+Arrow

Extend to end of dataCtrl+Shift+Arrow

Select only visible cells (skip hidden rows)Alt+;

Copy, cut and paste

CopyCtrl+C

CutCtrl+X

PasteCtrl+V

Paste Values only (strip formulas and formatting)The most important paste — gives results without the formulaCtrl+Alt+V → V → Enter

Paste Special dialog (all options)Ctrl+Alt+V

Paste and transpose (rows to columns)Ctrl+Alt+V → check Transpose → OK

Format Painter — copy formatting onlyHome → Format Painter (paintbrush) → click target. Double-click to paint multiple cells

Fill Down (copy cell above into selection)Ctrl+D

Fill RightCtrl+R

Your first formula

Every formula starts with =Type = and Excel knows it is a formula. Shows calculated result, not the text

Arithmetic operators+ add - subtract * multiply / divide ^ power () group. Order: () then ^ then */ then +-

Simple math=2+2

Reference a cell=A1+B1

Sum a range=SUM(A1:A10)

Average=AVERAGE(A1:A10)

Count cells with numbers=COUNT(A1:A10)

Count non-empty cells=COUNTA(A1:A10)

Maximum / Minimum=MAX(A1:A10)

AutoSum (Excel writes SUM for you)Alt+=

Copy formula down columnEnter formula → select cell → double-click fill handle to fill down to last adjacent row

Absolute reference — locks cell when copyingAdd $ before column and/or row: $A$1 stays fixed, A1 shifts. Press F4 to cycle types

Basic formatting

Bold / Italic / UnderlineCtrl+B / I / U

Format Cells dialog (all options)Ctrl+1

Background (fill) colourHome → Fill Color (paint bucket) → choose colour

Font colourHome → Font Color (A with colour bar) → choose

Add borderHome → Borders dropdown → choose style

Align left / centre / rightCtrl+L / E / R

Wrap text (show on multiple lines)Home → Wrap Text

Merge and center cellsSelect range → Home → Merge & Center

AutoFit column to contentDouble-click the right edge of the column header letter

Format as number / currency / percentageCtrl+Shift+! / $ / %

Clear all formatting (keep values)Home → Clear → Clear Formats

Managing sheets

Insert new sheetShift+F11

Rename sheetDouble-click the sheet tab → type new name → Enter

Delete sheetRight-click tab → Delete (permanent, cannot undo!)

Move sheetDrag the tab left or right to reorder

Copy sheet with Ctrl+dragHold Ctrl while dragging a tab — releases a copy at that position

Copy to another workbookRight-click tab → Move or Copy → choose workbook → Create a copy

Colour code a tabRight-click tab → Tab Color

Hide a sheetRight-click tab → Hide

Unhide a sheetRight-click any tab → Unhide → choose sheet

Reference another sheet in formula=Sheet2!B5

3D reference — same cell across multiple sheets=SUM(Sheet1:Sheet12!B5)

Number Formatting — Complete Guide

Format numbers, dates, currencies, percentages — all custom format codes explained

Formatting changes how a value looks, not its actual value. =A1+B1 always uses the real numbers. Apply via Ctrl+1 → Number tab → Custom.

Built-in number formats

General (default, no specific format)General

Integer (no decimals)0

Two decimal places0.00

Thousands separator#,##0

Comma + 2 decimals#,##0.00

Currency USD$#,##0.00

Currency INR (Rupee)₹#,##0.00

Currency EUR€#,##0.00

Percentage (0.25 displays as 25%)0%

Percentage with 2 decimals0.00%

Scientific notation0.00E+00

Fraction# ?/?

Text (display value as text)@

Custom format code syntax

Four sections: Positive ; Negative ; Zero ; TextSections separated by semicolons. Trailing sections optional

0 — always show digit (even if zero)0.00 shows 5 as "5.00" | 000 shows 5 as "005"000

# — show digit, suppress insignificant zeros#.##

? — digit or space (aligns decimals in column)??0.0??

, — thousands separator; at end divides by 1000#,##0,

"text" — literal text inside the format0.0" kg"

@ — placeholder for the cell's text value@" Ltd."

[Color] — colour the number (Red, Blue, Green...)[Red]-0;[Blue]0

[>=n] — conditional formatting in number format[>=1000]#,##0,"K";0

Date and time format codes

Short date (14/03/2025)dd/mm/yyyy

Long date (14 March 2025)dd mmmm yyyy

With day name (Mon 14 Mar 2025)ddd dd mmm yyyy

ISO format (2025-03-14)yyyy-mm-dd

Day name only (Monday)dddd

Short day name (Mon)ddd

Month name (March)mmmm

Short month (Mar)mmm

Month as 2 digits (03)mm

4-digit year (2025)yyyy

2-digit year (25)yy

Time 24-hour (14:30)hh:mm

Time with seconds (14:30:45)hh:mm:ss

12-hour with AM/PMh:mm AM/PM

Elapsed time over 24 hours (e.g. 36:00)[h]:mm:ss

Date and time combineddd/mm/yyyy hh:mm

Custom format recipes

Show in thousands (K)0.0,"K"

Show in millions (M)0.0,,"M"

Show in billions (B)0.0,,,"B"

Auto K/M based on magnitude[>=1000000]0.0,,"M";[>=1000]0,"K";0

Leading zeros to 5 digits00000

Phone number format000"-"000"-"0000

Green positive / Red negative[Green]#,##0;[Red]-#,##0;0

Show + sign for positives+#,##0;-#,##0;0

Hide zeros (show blank instead)0;-0;;@

Hide all values (blank cell);;;

Indian lakh/crore system[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

Append unit to number0.0" km"

Append text to text cell value@" (pending)"

Conditional Formatting — Complete

Auto-colour cells by value — highlight rules, heat maps, data bars, icon sets, formula-based rules

Quick highlight rules

Open Conditional FormattingHome → Conditional Formatting

Highlight cells greater than a valueHighlight Cell Rules → Greater Than → value + colour

Highlight cells less than a valueHighlight Cell Rules → Less Than

Highlight cells between two valuesHighlight Cell Rules → Between

Highlight duplicate valuesHighlight Cell Rules → Duplicate Values → Duplicate

Highlight unique valuesHighlight Cell Rules → Duplicate Values → Unique

Highlight text containing a wordHighlight Cell Rules → Text That Contains

Highlight dates (today, this week, next month)Highlight Cell Rules → A Date Occurring → choose period

Highlight top 10 valuesTop/Bottom Rules → Top 10 Items

Highlight top 10%Top/Bottom Rules → Top 10%

Highlight above averageTop/Bottom Rules → Above Average

Highlight below averageTop/Bottom Rules → Below Average

Data Bars, Color Scales, Icon Sets

Data Bars — bar in cell proportional to valueConditional Formatting → Data Bars → gradient or solidinstant visual

Color Scale — heat map (red-yellow-green etc.)Conditional Formatting → Color Scales → 2-color or 3-color

Icon Sets — arrows, traffic lights, stars, flagsConditional Formatting → Icon Sets → choose set

Show only icons, hide numbersManage Rules → Edit Rule → Show Icon Only

Reverse icon order (high = red)Edit Rule → Reverse Icon Order

Custom icon thresholdsEdit Rule → Type: Number/Percent/Formula → set value per icon

Negative value bars in different colourData Bars → More Rules → Negative Value and Axis

Formula-based rules (most powerful)

How to create a formula ruleSelect range → New Rule → "Use a formula" → enter formula → Format. Formula must return TRUE to apply. Lock column not row: =$C2

Highlight entire row where Status = "Done"=$C2="Done"

Highlight row where date is overdue=$D2<TODAY()

Zebra stripe — every other row shaded=MOD(ROW(),2)=0

Highlight row if any cell in it is blank=COUNTBLANK($A2:$F2)>0

Highlight if value exists in another list=COUNTIF($G:$G,A2)>0

Highlight maximum value in column=A1=MAX($A$1:$A$100)

Highlight formula cells (not constants)=ISFORMULA(A1)

Highlight weekend dates=WEEKDAY(A1,2)>5

Highlight top 3 in column=$B2>=LARGE($B$2:$B$100,3)

Managing rules

View all rules on current sheetConditional Formatting → Manage Rules → This Worksheet

Rule priority — top rule winsDrag rules up/down in Manage Rules to change priority

Stop If True — skip lower-priority rulesManage Rules → check "Stop If True" on the rule

Edit an existing ruleManage Rules → select rule → Edit Rule

Delete one ruleManage Rules → select rule → Delete Rule

Clear CF from selected cellsConditional Formatting → Clear Rules → Clear from Selected Cells

Clear CF from entire sheetConditional Formatting → Clear Rules → Clear from Entire Sheet

Copy CF rules to another rangeCopy cell → Paste Special → Formats (Ctrl+Alt+V → T)

Avoid whole-column CF (A:A) — it slows fileUse exact ranges like A2:A10000. Formula-based rules on A:A are especially slowperf

Sparklines and Form Controls

Mini in-cell charts, interactive dropdowns, checkboxes, spin buttons, scroll bars

Sparklines — mini charts inside cells

What are sparklinesTiny charts living inside a single cell. Each one visualises a row of data. Perfect for dashboard tables — trend at a glance

Insert Line sparklinesInsert → Sparklines → Line → Data Range: your data rows → Location Range: destination cells

Insert Column sparklines (mini bar chart)Insert → Sparklines → Column

Win/Loss sparklines (+/- blocks, good for P&L)Insert → Sparklines → Win/Loss

Highlight High PointSparkline tab → Show → High Point → choose colour

Highlight Low PointSparkline tab → Show → Low Point

Show markers on all data pointsSparkline tab → Show → Markers

Same Y-axis scale across all sparklinesEssential for fair comparison — by default each scales independentlySparkline tab → Axis → Min/Max → Same for All

Change sparkline colour and weightSparkline tab → Sparkline Color / Weight

Delete sparklinesSparkline tab → Clear Selected Sparklines

Form Controls — interactive sheet elements

Enable Developer tab firstFile → Options → Customize Ribbon → check Developer → OK

Access Form ControlsDeveloper → Insert → Form Controls (top section — NOT ActiveX)

Checkbox (true/false toggle)Draw it → right-click → Format Control → Cell Link: pick a cell (stores TRUE/FALSE). Use that cell in IF formulas to drive logic

Combo Box (dropdown list)Draw it → right-click → Format Control → Input Range: your list → Cell Link: a cell (stores selected item index 1, 2, 3...)

Get selected text from Combo Box linked to D1=INDEX(A1:A10,D1)

Option Buttons (radio — only one selectable)Draw multiple → group in a Group Box → Cell Link stores which number is selected

Spin Button (click up/down arrows to change number)Right-click → Format Control → Min/Max/Step → Cell Link. Use that cell in formulas to drive charts or calculations

Scroll Bar (slider over a wider range)Same as Spin Button but draggable. Great for interactive dashboards

Button to run a macroDeveloper → Insert → Button → draw → assign macro name when prompted

Link controls to drive a chart dynamicallySpin Button cell link → INDEX formula → chart data source = fully interactive chartdashboard

Excel Versions and Compatibility

365 vs 2021 vs 2019 vs 2016 — which features work where and how to handle older users

Excel 365 — exclusive features

Dynamic Arrays + Spill operator #FILTER, SORT, UNIQUE, SEQUENCE, SORTBY, RANDARRAY. Formulas spill results into multiple cells automatically365 only

XLOOKUP — modern replacement for VLOOKUP365 + 2021

XMATCH — modern replacement for MATCH365 + 2021

LET — name variables inside a formula365 + 2021

LAMBDA and helpers (MAP, BYROW, BYCOL, REDUCE, SCAN)365 only

TEXTBEFORE, TEXTAFTER, TEXTSPLIT365 only

TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, WRAPROWS365 only

IFS, SWITCH, MAXIFS, MINIFS2019+

UNIQUE function (standalone)365 + 2021

Power Query (Get and Transform)Available since 2016, greatly improved in 3652016+

Copilot in Excel (AI assistant)365 only

Python in Excel365 only

Real-time co-authoring365 + OneDrive

Compatibility gotchas

365 file opened by Excel 2019 userDynamic array formulas become static values. Spill results show as legacy array {}. XLOOKUP/LET show #NAME?breaks

Check compatibility before sharingFile → Info → Check for Issues → Check Compatibility — lists every incompatible function by version

Safe XLOOKUP fallback for older Excel=IFERROR(INDEX(B:B,MATCH(A2,A:A,0)),"Not found")

Safe UNIQUE fallbackUse Remove Duplicates (Data tab) or a Pivot Table to get unique values without the function

Excel Online (browser) limitationsMost formulas work. No VBA/macros, no Add-ins (Solver), limited chart customisation, no Power Pivot

Excel for Mac differencesNo VBA macros by default (use Office Scripts). Some Windows add-ins unavailable. Some shortcut differences

Google Sheets compatibilityVLOOKUP, SUMIF, IF, COUNTIF all work. No Power Query. No XLOOKUP (Sheets has XMATCH). CF differs

Old .xls format limitsMax 65,536 rows and 256 columns vs .xlsx = 1,048,576 rows and 16,384 columnsold format

Duplicates

Find, flag, count, and safely remove duplicate rows across single or multiple columns

Find duplicates

Highlight duplicates visuallyHome → Conditional Formatting → Highlight Cell Rules → Duplicate Values

Count occurrences of a value=COUNTIF(A:A,A2)

Flag as "Dup" or "Unique"=IF(COUNTIF(A:A,A2)>1,"Dup","Unique")

Mark only 2nd+ occurrences as dupExpanding range makes first occurrence always unique=IF(COUNTIF($A$1:A2,A2)>1,"Dup","First")

Count unique values in range=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

Count unique (with blanks safe)=SUMPRODUCT((1/COUNTIF(A2:A100,A2:A100))*(A2:A100<>""))

List unique values (365/2021)=UNIQUE(A2:A100)

Unique across multiple columns (365)=UNIQUE(A2:C100,FALSE,FALSE)

Return only values that appear onceThird arg TRUE = rows that appear exactly once=UNIQUE(A2:A100,FALSE,TRUE)

Duplicate count per value=COUNTIFS(A:A,A2)-1

Remove duplicates

Remove duplicates — built-inData → Remove Duplicates → choose key columnsdestructive

Safe copy before removingCopy entire range → paste to new sheet → then run Remove Duplicatessafe

Dedupe to new range (365)Non-destructive — original data untouched=UNIQUE(A2:A100)

Keep only truly unique rows (365)=FILTER(A2:C100,COUNTIF(A2:A100,A2:A100)=1)

Dedupe on 2 columns combinedResult > 1 means that row combo is duplicated=COUNTIFS(A:A,A2,B:B,B2)

Dedupe on 3+ column combo=COUNTIFS(A:A,A2,B:B,B2,C:C,C2)

Advanced Filter unique listData → Advanced → Copy to another location → Unique records onlyclassic

Power Query dedupe (repeatable)Data → Get Data → From Table → Home → Remove Rows → Remove Duplicatesbest

Dedupe across sheets / files

Check if value exists in Sheet2Returns TRUE/FALSE=COUNTIF(Sheet2!A:A,A2)>0

Find dupes between two sheets=IF(COUNTIF(Sheet2!A:A,A2)>0,"In both","Only here")

List values appearing in both sheets (365)=FILTER(A2:A100,COUNTIF(Sheet2!A:A,A2:A100)>0)

List values only in Sheet1 (365)=FILTER(A2:A100,COUNTIF(Sheet2!A:A,A2:A100)=0)

Blanks & gaps

Select, count, fill, skip, and delete empty cells and rows

Find & count blanks

Select all blank cells in rangeCtrl+G → Special → Blanks → OK

Check if a cell is blank=ISBLANK(A2)

Count blank cells in range=COUNTBLANK(A2:A100)

Count non-blank cells=COUNTA(A2:A100)

Count cells that look blank but have spacesCells with just spaces pass ISBLANK but fail this=SUMPRODUCT((TRIM(A2:A100)="")*1)

Find first blank row number in column=MATCH(TRUE,ISBLANK(A:A),0)

Fill blanks

Fill all blanks with value above (bulk)Select range → Ctrl+G → Special → Blanks → type = → press ↑ → Ctrl+Enterfast

Fill blank with value from above (formula)=IF(A2="",A1,A2)

Replace blank with fallback text=IF(ISBLANK(A2),"N/A",A2)

Replace blank with 0=IF(ISBLANK(A2),0,A2)

First non-blank value in a row=INDEX(A2:E2,MATCH(TRUE,A2:E2<>"",0))

Last non-blank in a column=LOOKUP(2,1/(A:A<>""),A:A)

Fill blanks downward using Power QueryTransform → Fill → Down (fills merged-cell style blanks)best

Skip blanks in formulas

SUM ignoring blanks (SUM does this anyway)=SUMIF(A:A,"<>",A:A)

AVERAGE ignoring blanks (AVERAGE also does this)=AVERAGEIF(A:A,"<>",A:A)

Filter out blank rows (365)=FILTER(A2:C100,A2:A100<>"")

Filter where column isn't blank or zero=FILTER(A2:C100,(A2:A100<>"")*( A2:A100<>0))

Delete blank rowsFilter column → select Blanks → select visible rows → Ctrl+– → Delete rowsdestructive

Conditional count ignoring blanks=COUNTIFS(A:A,"<>",B:B,"Done")

Clean & fix

Whitespace, invisible characters, case, replacements, text splitting

Whitespace & invisible chars

Remove leading & trailing spaces=TRIM(A2)

Remove non-printable / hidden chars=CLEAN(A2)

Trim AND clean (most thorough)=TRIM(CLEAN(A2))

Remove ALL spaces including internal=SUBSTITUTE(A2," ","")

Remove specific invisible char (char 160 = non-breaking space)Common in data pasted from web=SUBSTITUTE(A2,CHAR(160),"")

Remove tabs from text=SUBSTITUTE(A2,CHAR(9),"")

Remove line breaks from cell=SUBSTITUTE(A2,CHAR(10)," ")

Replace specific character=SUBSTITUTE(A2,"-","")

Replace nth occurrence only=SUBSTITUTE(A2,"-","",2)

Find & replace all (bulk in sheet)Ctrl+H

Case & text standardization

Title Case (capitalize each word)=PROPER(A2)

ALL UPPERCASE=UPPER(A2)

all lowercase=LOWER(A2)

Standardize phone format=TEXT(A2,"000-000-0000")

Pad number with leading zeros=TEXT(A2,"00000")

Add country code to phone="91"&TEXT(A2,"0000000000")

Normalize mixed separators (replace / with -)Nest SUBSTITUTE calls for multiple replacements=SUBSTITUTE(SUBSTITUTE(A2,"/","-"),".","-")

Split & extract

Split by delimiter (built-in, static)Data → Text to Columns → Delimited → choose delimiter

Flash fill — pattern-based splitType expected result in column → press Ctrl+ECtrl+E

Extract first word=LEFT(A2,FIND(" ",A2)-1)

Extract last word=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

Extract email domain=MID(A2,FIND("@",A2)+1,99)

Extract email username (before @)=LEFT(A2,FIND("@",A2)-1)

Extract text before first comma=LEFT(A2,FIND(",",A2)-1)

Extract text after first comma=TRIM(MID(A2,FIND(",",A2)+1,99))

Extract N characters from left=LEFT(A2,5)

Extract N characters from right=RIGHT(A2,4)

Extract N characters from positionMID(text, start_position, num_chars)=MID(A2,3,6)

Split text by delimiter into columns (365)TEXTSPLIT spills automatically=TEXTSPLIT(A2,",")

Split and stack into rows (365)=TEXTSPLIT(A2,,",")

Detect & fix common issues

Check if value contains keywordReturns TRUE/FALSE=ISNUMBER(SEARCH("keyword",A2))

Case-sensitive contains check=ISNUMBER(FIND("Keyword",A2))

Count character in cell (e.g. commas)=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

Check if cell has numbers mixed with text=AND(ISNUMBER(VALUE(A2)),A2<>"")

Find cells with duplicate spaces=A2<>TRIM(A2)

Check if two versions match after cleanup=TRIM(LOWER(A2))=TRIM(LOWER(B2))

Length of text in cell=LEN(A2)

Check if text starts with specific string=LEFT(A2,3)="INV"

Check if text ends with specific string=RIGHT(A2,3)="Ltd"

Data types & conversion

Numbers stored as text, dates as numbers, format mismatches and how to fix them

Green triangle in top-left of cell = number stored as text. This breaks SUM, VLOOKUP, and sorting. Always convert before working with data.

Number / text conversion

Text → Number (quick fix)Select cells with green triangles → yellow warning icon → Convert to Numberfastest

Force text string to number=VALUE(A2)

Force text to number (multiply trick)Paste 1 somewhere → copy → select problem cells → Paste Special → Multiplybulk

Force number to text=TEXT(A2,"0")

Number to text with formatting=TEXT(A2,"#,##0.00")

Check if a cell contains a number=ISNUMBER(A2)

Check if stored as text number=ISNUMBER(VALUE(A2))*ISTEXT(A2)

Extract number from mixed text/number=VALUE(TRIM(A2))

Remove currency symbol & convertWorks if symbol is just a prefix character=VALUE(SUBSTITUTE(A2,"₹",""))

Date conversion & parsing

Text string → Excel dateWorks if Excel recognizes the format=DATEVALUE(A2)

Build date from DD/MM/YYYY text=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

Build date from YYYYMMDD textCommon export format=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Number shown as dateFormat cell as General (Ctrl+1 → Number → General)

Date shown as numberFormat cell as Date (Ctrl+1 → Number → Date)

Convert date to text for display=TEXT(A2,"DD-MMM-YYYY")

Parse US date (MM/DD/YYYY) into date=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))

Convert Unix timestamp to dateUnix is seconds since 1970-01-01=(A2/86400)+DATE(1970,1,1)

Boolean & type checking

Is number?=ISNUMBER(A2)

Is text?=ISTEXT(A2)

Is blank?=ISBLANK(A2)

Is error?=ISERROR(A2)

Is logical (TRUE/FALSE)?=ISLOGICAL(A2)

Is a formula?=ISFORMULA(A2)

Get cell type as text=TYPE(A2)

Force TRUE/FALSE to 1/0=INT(A2)

Force 1/0 to TRUE/FALSE=EXACT(A2&"","1")

Data validation

Drop-downs, input restrictions, error alerts, and dependent lists

Setting up validation

Open Data Validation dialogData → Data Validation → Settings

Allow only whole numbers in rangeAllow: Whole number, Between 1 and 1000

Allow only dates in a rangeData Validation → Allow: Date → set start/end

Allow text length limitAllow: Text length, less than 50

Create simple dropdown listData Validation → Allow: List → Source: item1,item2,item3

Create dropdown from a rangeData Validation → Allow: List → Source: =$G$2:$G$10

Dynamic dropdown from named rangeDefine name first (Ctrl+F3), then use name in sourceSource: =Cities

Dependent (cascading) dropdownUse INDIRECT referencing parent dropdown cell=INDIRECT(A2)

Validation with formulas

Prevent duplicates in a columnSet Allow: Custom, Formula:=COUNTIF($A$2:$A$100,A2)=1

Allow only email addressesCustom formula for @ and dot presence=AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2)))

Allow entry only if another cell has valuePrevents data entry in B until A is filled=A2<>""

Restrict to uppercase only=EXACT(A2,UPPER(A2))

Allow only future dates=A2>TODAY()

Find cells that break existing validationData → Data Validation → Circle Invalid Dataaudit

Custom validation rules

Only allow unique values (no duplicates)=COUNTIF($A:$A,A1)=1

Only allow positive numbers=A1>0

Require value starts with prefix (e.g. INV-)=LEFT(A1,4)="INV-"

Only allow weekday dates (no weekends)=WEEKDAY(A1,2)<=5

Only allow future dates=A1>TODAY()

Budget cap — sum stays under limit=SUM($B$2:$B$50)<=100000

Allow only values from another sheet's listName that range first (Ctrl+F3), then type the name in Source field

Show guidance tip when cell is clickedData Validation → Input Message tab → write hint text

Custom error message when rule violatedError Alert tab → Style: Stop/Warning/Info → write message

Circle all currently invalid dataData → Data Validation → Circle Invalid Dataaudit

Clear validation circlesData → Data Validation → Clear Validation Circles

Copy validation rules to other cellsCopy cell → Paste Special → Validation (Ctrl+Alt+V → N)

Dependent (cascading) dropdowns

What they doSelection in column A controls what appears in column B dropdown. E.g. Category drives Sub-category options

Step 1 — create named range for each sub-listSelect values for "Fruits" → Name Box → type "Fruits". Repeat for each category. Name must exactly match parent value

Step 2 — main dropdown in A2Data Validation → List → Source: type comma-separated categories

Step 3 — dependent dropdown in B2=INDIRECT(A2)

Handle spaces in category names=INDIRECT(SUBSTITUTE(A2," ","_"))

Modern 365 FILTER-based approach=FILTER(SubCats,Categories=A2)

Cells & ranges

Selection tricks, paste special, absolute references, and Go To Special

Select & navigate

Select current data regionCtrl+Shift+*

Select all used cellsCtrl+A

Extend selection to last filled cellCtrl+Shift+End

Select to last cell in column/rowCtrl+Shift+↓/→

Select visible cells only (filtered)Alt+;

Go To Special (blanks, errors, etc.)Ctrl+G → Special

Select all formula cellsCtrl+G → Special → Formulas

Select all cells with constantsCtrl+G → Special → Constants

Select all cells with conditional formattingCtrl+G → Special → Conditional Formats

Jump to a cell addressCtrl+G → type A1 → Enter

Name a range for use in formulasCtrl+F3 → New

Paste special

Paste values only (remove formulas)Alt+E+S+V → Enter

Paste formatting onlyAlt+E+S+T → Enter

Paste and transpose rows↔colsAlt+E+S → check Transpose

Paste and add to existing valuesAlt+E+S+D → Add

Paste column widths onlyAlt+E+S → Column widths

Transpose formula (spills, 365)=TRANSPOSE(A1:D5)

Convert formula column to static valuesCopy → same cell → Paste Special → Valuesoverwrites

Skip blanks when pastingPaste Special → check Skip Blanks

References

Absolute reference (locks both)Use when copying formula across rows/cols$A$1

Lock row onlyA$1

Lock column only$A1

Toggle $ in formula (cycle through)Press F4 while cursor is on the referenceF4

Reference entire columnA:A

Reference another sheet=Sheet2!A1

Reference another workbookMust be open, or full path needed when closed=[Book2.xlsx]Sheet1!$A$1

Dynamic reference using INDIRECTTurns text string into a cell reference=INDIRECT("A"&B2)

Create dynamic sheet reference=INDIRECT("'"&A2&"'!B1")

Columns & rows

Insert, delete, hide, move, split, sort, filter, group

Column operations

Insert column to left of selectionCtrl+Shift++

Delete selected columnCtrl+–

Hide columnCtrl+0

Unhide columnSelect columns either side firstCtrl+Shift+0

Autofit column widthDouble-click column border in header

Autofit all columns at onceCtrl+A → double-click any column border

Move column without overwritingSelect column → hold Shift → drag border to new position

Split column by delimiterData → Text to Columns → Delimited

Flash fill to split/combineCtrl+E

Column letter from number=SUBSTITUTE(ADDRESS(1,A2,4),1,"")

Column number from letter=COLUMN(INDIRECT(A2&"1"))

Row operations

Insert row above selectionCtrl+Shift++

Delete selected rowCtrl+–

Hide rowCtrl+9

Unhide rowCtrl+Shift+9

Autofit row heightHome → Format → AutoFit Row Height

Delete rows matching a conditionFilter column to show matches → select visible rows → Ctrl+– → Delete entire rowcheck filter

Count rows with specific value=COUNTIF(A:A,"value")

Get row number of a match=MATCH("value",A:A,0)

Freeze top rowView → Freeze Panes → Freeze Top Row

Freeze first columnView → Freeze Panes → Freeze First Column

Freeze multiple rows and columnsClick cell below rows and right of columns to freeze → View → Freeze Panes

Sort & filter

Filter toggleCtrl+Shift+L

Sort ascendingAlt+H+S+A

Sort descendingAlt+H+S+D

Multi-level sortData → Sort → Add Level → set each level

Sort by color or iconData → Sort → Sort On: Cell Color / Font Color / Cell Icon

Sort formula (dynamic, 365)=SORT(A2:C100,2,-1)

Sort by another column (365)=SORTBY(A2:C100,B2:B100,-1)

Randomize order=SORTBY(A2:A100,RANDARRAY(99))

Filter with condition (365)=FILTER(A2:C100,B2:B100>1000)

Filter AND conditions (365)=FILTER(rng,(A2:A100="X")*(B2:B100>0))

Filter OR conditions (365)=FILTER(rng,(A2:A100="X")+(B2:B100="Y"))

Show message when filter has no results (365)=FILTER(A2:A100,B2:B100>0,"No results")

Group & outline

Group rows or columnsSelect rows/cols → Data → GroupAlt+Shift+→

Ungroup rows or columnsAlt+Shift+←

Collapse group (hide)Click – button on outline

Show all levels at onceClick level number 1/2/3 on outline bar

Auto-outline (group by formula structure)Data → Group → Auto Outline

Remove all groupingData → Ungroup → Clear Outline

Subtotal grouped data (auto-collapse)Data → Subtotal → set grouping column and function

Tables & named ranges

Structured references, auto-expansion, slicers, and name manager

Excel Tables (Ctrl+T)

Convert range to TableAuto-expands, auto-fills formulas, adds filterCtrl+T

Name your tableTable Design → Table Name field (top-left)

Reference whole column in table=SUM(Table1[Revenue])

Reference same row in table (structured)=[@Revenue]*[@Qty]

Reference table header row=Table1[#Headers]

Reference totals row=Table1[#Totals]

Add totals rowTable Design → check Total Row

Remove duplicates from tableTable Design → Remove Duplicates

Add slicer to tableTable Design → Insert Slicer → choose column

Convert table back to rangeTable Design → Convert to Range

Named ranges

Create named rangeSelect range → Name Box (top-left) → type name → Enter

Manage all namesCtrl+F3

Create names from selection headersSelect range with headers → Formulas → Create from Selection

Dynamic named range (expands with data)=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

Use name in formula=SUM(Revenue)

Delete a nameCtrl+F3 → select name → Delete

Paste list of all names into sheetCtrl+F3 → Paste List (at bottom)

Lookup & merge

VLOOKUP, XLOOKUP, INDEX/MATCH, multi-key joins, cross-sheet lookups

VLOOKUP (classic)

Basic VLOOKUPcol_num = column to return; 0 = exact match=VLOOKUP(A2,Sheet2!A:C,2,0)

VLOOKUP with error handling=IFERROR(VLOOKUP(A2,Sheet2!A:C,2,0),"")

VLOOKUP left (workaround)VLOOKUP can't look left — use INDEX/MATCH instead=INDEX(A:A,MATCH(D2,B:B,0))

VLOOKUP approximate match (sorted data)Last arg 1 = approximate; data must be sorted ascending=VLOOKUP(A2,Sheet2!A:C,2,1)

VLOOKUP with wildcard=VLOOKUP("*"&A2&"*",Sheet2!A:C,2,0)

VLOOKUP from another workbook=VLOOKUP(A2,[Book2.xlsx]Sheet1!$A:$C,2,0)

XLOOKUP (modern, 365/2021)

Basic XLOOKUPNo column index needed; can look left=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)

XLOOKUP with "not found" fallback=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"Not found")

XLOOKUP return multiple columns=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:E)

XLOOKUP match on 2 keys combined=XLOOKUP(A2&B2,S2!A:A&S2!B:B,S2!C:C)

XLOOKUP wildcard match5th arg 2 = wildcard mode=XLOOKUP("*"&A2&"*",S2!A:A,S2!B:B,,2)

XLOOKUP last match (reverse search)4th arg -1 = search from end=XLOOKUP(A2,S2!A:A,S2!B:B,,-1,-1)

XLOOKUP approximate match (next smaller)5th arg -1 = next smaller; data needn't be sorted=XLOOKUP(A2,S2!A:A,S2!B:B,,-1)

XLOOKUP horizontal (look across row)=XLOOKUP(A2,Sheet2!1:1,Sheet2!2:2)

INDEX / MATCH

Basic INDEX/MATCHMore flexible than VLOOKUP; can look any direction=INDEX(B:B,MATCH(A2,C:C,0))

Two-way lookup (row and column)=INDEX(B2:E10,MATCH(A2,A2:A10,0),MATCH(B1,B1:E1,0))

Multi-criteria INDEX/MATCHArray formula — Ctrl+Shift+Enter in older Excel=INDEX(C:C,MATCH(1,(A:A=A2)*(B:B=B2),0))

Return row of a match=MATCH(A2,A:A,0)

Return column of a match=MATCH(A2,1:1,0)

Nth occurrence matchFind 2nd, 3rd match with COUNTIF trick=MATCH(1,(A2:A100="X")*(COUNTIF(A$1:A1,"X")<2),0)

Combine & stack

Join two cells with space=A2&" "&B2

Join multiple cells with separator=TEXTJOIN(", ",TRUE,A2:D2)

Combine column values conditionally=TEXTJOIN(", ",TRUE,IF(B2:B10="Yes",A2:A10,""))

Stack ranges vertically (365)=VSTACK(A1:C50,Sheet2!A1:C50)

Stack ranges side by side (365)=HSTACK(A1:B50,Sheet2!A1:B50)

Pull single cell from another sheet=Sheet2!B2

Consolidate multiple sheets (built-in)Data → Consolidate → choose ranges + function

Left join (keep all from Sheet1)Blank returned if no match in Sheet2=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"")

XLOOKUP — all options explained

Syntax=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Return multiple columns at once=XLOOKUP(A2,ID_col,Full_table)

Wildcard match (partial text, match_mode=2)=XLOOKUP("*"&A2&"*",Names,Values,"",2)

Approximate match — next smaller (match_mode=-1)=XLOOKUP(A2,Brackets,Rates,"",-1)

Return LAST match (search_mode=-1)=XLOOKUP(A2,Names,Values,"",,,-1)

Horizontal lookup (look across a row)=XLOOKUP(A2,Header_row,Data_row)

Two-way lookup (row and column)=XLOOKUP(RowVal,RowH,XLOOKUP(ColVal,ColH,Matrix))

INDEX / MATCH — all patterns

Basic INDEX/MATCH (replaces VLOOKUP)=INDEX(B:B,MATCH(A2,A:A,0))

Return multiple columns (drag right)=INDEX($B:$D,MATCH($A2,$A:$A,0),COLUMN(A1))

Two-way lookup (matrix intersection)=INDEX(Matrix,MATCH(RowVal,RowH,0),MATCH(ColVal,ColH,0))

Last occurrence of a value=INDEX(B:B,MATCH(2,1/(A:A=A2),1))

First non-blank in column=INDEX(A:A,MATCH(TRUE,LEN(A:A)>0,0))

Approximate match (grade thresholds)=INDEX(Grades,MATCH(A2,Thresholds,1))

Multi-criteria lookup (two conditions)=INDEX(C:C,MATCH(1,(A:A=E2)*(B:B=F2),0))

XMATCH — modern MATCH (365)=XMATCH(A2,B:B)

Math & statistics

SUM, SUMIF, COUNTIF, AVERAGEIF, conditional aggregates, ranking

SUM family

Sum a range=SUM(A2:A100)

Sum if condition met (one condition)=SUMIF(A:A,"Lucknow",B:B)

Sum with multiple conditions=SUMIFS(C:C,A:A,"Lucknow",B:B,">0")

Sum if cell contains text (wildcard)=SUMIF(A:A,"*Ltd*",B:B)

Sum top N values=SUMPRODUCT(LARGE(A2:A100,ROW(INDIRECT("1:5"))))

Running total=SUM($B$2:B2)

Sum every Nth row=SUMPRODUCT((MOD(ROW(A2:A100)-ROW(A2),3)=0)*A2:A100)

Sum unique values (no double-counting)=SUMPRODUCT(B2:B100/COUNTIF(A2:A100,A2:A100))

COUNT family

Count numbers in range=COUNT(A2:A100)

Count anything non-blank=COUNTA(A2:A100)

Count blank cells=COUNTBLANK(A2:A100)

Count with one condition=COUNTIF(A:A,"Lucknow")

Count with multiple conditions=COUNTIFS(A:A,"Lucknow",B:B,"Open")

Count cells > value=COUNTIF(B:B,">1000")

Count cells containing text=COUNTIF(A:A,"*")

Count unique values (pre-365)=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

Count unique values (365)=COUNTA(UNIQUE(A2:A100))

AVERAGE, MIN, MAX, RANK

Average (ignores blanks)=AVERAGE(A2:A100)

Average with condition=AVERAGEIF(A:A,"Lucknow",B:B)

Average with multiple conditions=AVERAGEIFS(C:C,A:A,"X",B:B,">0")

Minimum in range=MIN(A2:A100)

Minimum with condition=MINIFS(B:B,A:A,"Lucknow")

Maximum in range=MAX(A2:A100)

Maximum with condition=MAXIFS(B:B,A:A,"Lucknow")

Nth largest value=LARGE(A2:A100,3)

Nth smallest value=SMALL(A2:A100,3)

Rank value in list=RANK(A2,A$2:A$100,0)

Percentile=PERCENTILE(A2:A100,0.9)

Median=MEDIAN(A2:A100)

Standard deviation (sample)=STDEV(A2:A100)

Rounding & math ops

Round to N decimal places=ROUND(A2,2)

Round up always=ROUNDUP(A2,0)

Round down always=ROUNDDOWN(A2,0)

Round to nearest multiple of N=MROUND(A2,5)

Ceiling (round up to multiple)=CEILING(A2,10)

Floor (round down to multiple)=FLOOR(A2,10)

Integer part only=INT(A2)

Remainder after division=MOD(A2,3)

Absolute value=ABS(A2)

Power / exponent=POWER(A2,2)

Square root=SQRT(A2)

Random number 0–1=RAND()

Random integer between two values=RANDBETWEEN(1,100)

Rounding functions

ROUND — N decimal places=ROUND(A2,2)

ROUNDUP — always rounds away from zero=ROUNDUP(A2,0)

ROUNDDOWN — always rounds toward zero=ROUNDDOWN(A2,0)

CEILING — round up to nearest multiple=CEILING(A2,0.5)

FLOOR — round down to nearest multiple=FLOOR(A2,100)

MROUND — round to nearest N=MROUND(A2,50)

INT — remove decimal (floor toward -infinity)=INT(A2)

TRUNC — remove decimals without rounding=TRUNC(A2,2)

ABS — absolute value (remove negative sign)=ABS(A2)

MOD — remainder after division=MOD(A2,3)

Statistical functions

MEDIAN — middle value in range=MEDIAN(A2:A100)

MODE — most frequent value=MODE(A2:A100)

STDEV — standard deviation (sample)=STDEV(A2:A100)

VAR — variance=VAR(A2:A100)

PERCENTILE — Nth percentile value=PERCENTILE(A2:A100,0.9)

QUARTILE — Q1, Q2, Q3=QUARTILE(A2:A100,1)

RANK — rank of a value in range=RANK(A2,$A$2:$A$100,0)

LARGE — Nth largest value=LARGE(A2:A100,3)

SMALL — Nth smallest value=SMALL(A2:A100,3)

CORREL — correlation coefficient=CORREL(A2:A100,B2:B100)

FORECAST.LINEAR — predict a value=FORECAST.LINEAR(A102,B2:B100,A2:A100)

RANDBETWEEN — random integer in range=RANDBETWEEN(1,100)

Text formulas

Concatenate, search, replace, format, measure, convert

Combine & format text

Concatenate (ampersand)=A2&" "&B2

CONCAT (no separator)=CONCAT(A2,B2,C2)

TEXTJOIN with separator, ignore blanks=TEXTJOIN(", ",TRUE,A2:D2)

Repeat text N times=REPT("★",A2)

Format number as currency text=TEXT(A2,"₹#,##0.00")

Format number with commas=TEXT(A2,"#,##0")

Format as percentage text=TEXT(A2,"0.0%")

Add ordinal suffix (1st, 2nd…)Requires complex IF chain or helper column

Search & position

Find position of text (case-insensitive)=SEARCH("@",A2)

Find position (case-sensitive)=FIND("@",A2)

Find Nth occurrence position=FIND("_",A2,FIND("_",A2)+1)

Check if text contains substring=ISNUMBER(SEARCH("Ltd",A2))

SEARCH with wildcard=ISNUMBER(SEARCH("inv*",A2))

Find position of last slash or delimiter=FIND("✱",SUBSTITUTE(A2,"/","✱",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))

Replace & transform

Replace all occurrences of a substring=SUBSTITUTE(A2,"old","new")

Replace at a specific positionREPLACE(text, start_num, num_chars, new_text)=REPLACE(A2,1,3,"INV")

Remove specific word=TRIM(SUBSTITUTE(A2,"Ltd",""))

Convert to number by removing chars=VALUE(SUBSTITUTE(A2,",",""))

Reverse order of wordsComplex — best done with TEXTSPLIT+TEXTJOIN (365)=TEXTJOIN(" ",1,TRANSPOSE(SORTBY(TEXTSPLIT(A2," "),SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1),,-1)))

Extract and split text

Extract before delimiter (classic)=LEFT(A2,FIND("-",A2)-1)

Extract after delimiter (classic)=MID(A2,FIND("-",A2)+1,LEN(A2))

TEXTBEFORE — before delimiter (365)=TEXTBEFORE(A2,"-")

TEXTAFTER — after delimiter (365)=TEXTAFTER(A2,"-")

TEXTBEFORE — Nth occurrence=TEXTBEFORE(A2,".",2)

TEXTSPLIT — split to columns by delimiter (365)=TEXTSPLIT(A2,",")

TEXTSPLIT — split into rows (vertical)=TEXTSPLIT(A2,,",")

Extract first word=LEFT(A2,FIND(" ",A2&" ")-1)

Extract last word=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

Count words in a cell=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

Text to Columns (no formula needed)Select column → Data → Text to Columns → Delimited → set delimiter → Finisheasiest

Search and replace in text

FIND — position (case-sensitive, errors if missing)=FIND("@",A2)

SEARCH — position (case-insensitive, wildcards ok)=SEARCH("l?d",A2)

Check if cell contains a word=ISNUMBER(SEARCH("word",A2))

SUBSTITUTE — replace all occurrences=SUBSTITUTE(A2,"old","new")

SUBSTITUTE — replace only Nth occurrence=SUBSTITUTE(A2,"a","A",2)

REPLACE — replace characters at position N=REPLACE(A2,3,2,"XX")

Remove all spaces=SUBSTITUTE(A2," ","")

Count occurrences of a character=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

Case, convert and format

UPPER — all capitals=UPPER(A2)

LOWER — all lowercase=LOWER(A2)

PROPER — Title Case=PROPER(A2)

LEN — count characters=LEN(A2)

TRIM — remove extra spaces=TRIM(A2)

CLEAN — remove non-printable characters=CLEAN(A2)

TEXT — convert number to formatted text=TEXT(A2,"dd/mm/yyyy")

VALUE — convert text number to real number=VALUE(A2)

Convert text to number (multiply trick)=A2*1

Pad with leading zeros to fixed length=TEXT(A2,"00000")

CHAR — get character from ASCII code=CHAR(10)

CODE — get ASCII code of a character=CODE("A")

REPT — repeat text N times=REPT("*",A2)

Combine text and formatted number="Total: "&TEXT(SUM(A:A),"#,##0")

Dates & time

TODAY, date math, working days, age, quarters, time calculations

Current date & time

Today's date (updates daily)=TODAY()

Current date and time=NOW()

Static today (won't change)Press Ctrl+; in a cellCtrl+;

Static current timeCtrl+Shift+;

Extract date parts

Get year from date=YEAR(A2)

Get month number from date=MONTH(A2)

Get month name=TEXT(A2,"MMMM")

Get day of month=DAY(A2)

Day of week number (1=Sun)=WEEKDAY(A2,1)

Day name (Monday, Tuesday…)=TEXT(A2,"DDDD")

Week number of year=WEEKNUM(A2)

Quarter number from date=INT((MONTH(A2)-1)/3)+1

Last day of month=EOMONTH(A2,0)

First day of month=DATE(YEAR(A2),MONTH(A2),1)

Date math

Days between two dates=B2-A2

Age in years=DATEDIF(A2,TODAY(),"Y")

Age in years, months, days=DATEDIF(A2,TODAY(),"Y")&"y "&DATEDIF(A2,TODAY(),"YM")&"m"

Months between two dates=DATEDIF(A2,B2,"M")

Add N days to date=A2+30

Add N months to date=EDATE(A2,3)

Add N working daysExcludes weekends; optionally pass holiday list=WORKDAY(A2,10)

Working days between dates=NETWORKDAYS(A2,B2)

Working days excluding holidays=NETWORKDAYS(A2,B2,Holidays)

Is date in the past?=A2

Is date within last 30 days?=AND(A2>=TODAY()-30,A2<=TODAY())

Time calculations

Hours between two times=HOUR(B2-A2)

Total hours as decimalFormat result as Number, not Time=(B2-A2)*24

Hours and minutes difference=TEXT(B2-A2,"h:mm")

Sum time values > 24 hoursFormat cell as [h]:mm:ss to show totals over 24h

Convert decimal hours to time=A2/24

Convert time to minutes=(B2-A2)*1440

Get time portion of datetime=MOD(A2,1)

Get date portion only=INT(A2)

Time calculations

Current date and time (recalculates live)=NOW()

Difference in hours between two times=((B2-A2)*24)

Difference in minutes=((B2-A2)*24*60)

Extract hour from time value=HOUR(A2)

Extract minute=MINUTE(A2)

Build time from hours, minutes, seconds=TIME(14,30,0)

Add hours to a time=A2+TIME(2,30,0)

Round time to nearest 15 minutes=MROUND(A2,"0:15")

Sum hours worked — format cell as [h]:mm=SUM(C2:C20)

Check if time is within business hours=AND(MOD(A2,1)>=TIME(9,0,0),MOD(A2,1)<=TIME(18,0,0))

EDATE, EOMONTH and fiscal dates

Add N months to a date=EDATE(A2,3)

Subtract N months=EDATE(A2,-3)

Last day of current month=EOMONTH(A2,0)

First day of current month=EOMONTH(A2,-1)+1

First day of next month=EOMONTH(A2,0)+1

Last day of next month=EOMONTH(A2,1)

Days remaining in month=EOMONTH(A2,0)-A2

Fiscal quarter (Apr-Mar year)=CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3)

Fiscal year number (Apr-Mar)=YEAR(A2)+(MONTH(A2)>=4)

Age in completed years=DATEDIF(A2,TODAY(),"Y")

Age in years and months=DATEDIF(A2,TODAY(),"Y")&"y "&DATEDIF(A2,TODAY(),"YM")&"m"

Logic & conditions

IF, IFS, AND, OR, SWITCH, nested logic, conditional arrays

IF & IFS

Basic IF=IF(A2>100,"High","Low")

IF with blank result=IF(A2="","",A2*B2)

Nested IF (3 outcomes)=IF(A2>90,"A",IF(A2>70,"B","C"))

IFS — cleaner multiple conditions (2016+)=IFS(A2>90,"A",A2>70,"B",A2>50,"C",TRUE,"F")

SWITCH — value matching (2016+)=SWITCH(A2,"Mon","Monday","Tue","Tuesday","Other")

IF with AND conditions=IF(AND(A2>0,B2="Open"),"Yes","No")

IF with OR conditions=IF(OR(A2="X",A2="Y"),"Match","No")

NOT equal operator=IF(A2<>"","Has value","Empty")

Logical operators & helpers

AND — all conditions true=AND(A2>0,A2<100,B2="Yes")

OR — any condition true=OR(A2="X",A2="Y",A2="Z")

NOT — reverse true/false=NOT(ISBLANK(A2))

XOR — exactly one condition true (2013+)=XOR(A2>0,B2>0)

TRUE / FALSE constants=TRUE

Force logical to 1 or 0=IF(A2>0,1,0)*1

Short-circuit with * (array AND)=SUMPRODUCT((A2:A100="X")*(B2:B100>0))

Short-circuit with + (array OR)=SUMPRODUCT(((A2:A100="X")+(B2:B100="Y"))>0)

LET & LAMBDA (365)

LET — assign variable names in formulaAvoids repeating the same expression=LET(x,A2*B2,y,C2+5,x+y)

LAMBDA — create reusable custom functionDefine once in Name Manager, use like built-in=LAMBDA(x,y,x^2+y^2)

Recursive LAMBDA (factorial example)Named function calls itself=LAMBDA(n,IF(n<=1,1,n*Factorial(n-1)))

Error handling

IFERROR — catch any error=IFERROR(A2/B2,0)

IFNA — catch only #N/A error=IFNA(VLOOKUP(A2,B:C,2,0),"Not found")

ISERROR — returns TRUE if cell has error=IF(ISERROR(A2/B2),"error",A2/B2)

ISBLANK — check if cell is completely empty=IF(ISBLANK(A2),"empty","has value")

ISNUMBER — check if value is a number=ISNUMBER(A2)

ISTEXT — check if value is text=ISTEXT(A2)

Safe division — handle zero denominator=IF(B2=0,"—",A2/B2)

Return blank not zero when source is empty=IF(A2="","",A2*1.1)

Advanced IF patterns

IFS — multiple conditions without nestingTests each in order. Add TRUE,default at end as fallback=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"F")

SWITCH — match value to list of outcomes=SWITCH(A2,"Mon","Start","Fri","End","Mid")

CHOOSE — pick from list by index number=CHOOSE(A2,"Q1","Q2","Q3","Q4")

XOR — exclusive or (one true but not both)=XOR(A2>100,B2>100)

Conditional SUMPRODUCT=SUMPRODUCT((A2:A100="Done")*B2:B100)

Conditional text join (365)=TEXTJOIN(", ",TRUE,IF(B2:B10="Done",A2:A10,""))

Double negative — force TRUE/FALSE to 1/0=SUMPRODUCT(--(A2:A100="Done"))

Nested IF — 5 outcomes (use IFS if available)=IF(A2>100,"V.High",IF(A2>50,"High",IF(A2>20,"Med",IF(A2>5,"Low","Min"))))

Pivot tables

Create, configure, refresh, group, calculate, and troubleshoot pivots

Create & configure

Insert Pivot TableInsert → PivotTable → choose source range → choose locationAlt+N+V

Recommended Pivots (quick start)Insert → Recommended PivotTables

Change value aggregationRight-click value → Summarize Values By → choose (Sum, Count, Average…)

Show values as % of totalRight-click value → Show Values As → % of Grand Total

Show as % of row / column totalShow Values As → % of Row Total / % of Column Total

Show running totalShow Values As → Running Total In → select field

Rank within categoryShow Values As → Rank Smallest to Largest / Largest

Add calculated fieldPivotTable Analyze → Fields, Items & Sets → Calculated Field

Refresh & troubleshoot

Refresh pivot dataAlt+F5

Refresh all pivots in workbookCtrl+Alt+F5

Pivot won't update — source data changedPivot caches data — always refresh after source changesalways refresh

Old items still showing in filterPivotTable Options → Data tab → Retain items: None

Change data sourcePivotTable Analyze → Change Data Source

Pivot table is counting instead of summingSource column contains text or blanks — clean data first, then refreshfix source

Group dates by month/quarterRight-click a date cell in pivot → Group → choose interval

Cannot group datesColumn has text dates or blanks — convert to real dates firstfix type

Add slicer to pivotPivotTable Analyze → Insert Slicer

Connect slicer to multiple pivotsRight-click slicer → Report Connections → check which pivots

GETPIVOTDATA

Reference pivot cell (auto-generated)Appears when you click a pivot cell in a formula=GETPIVOTDATA("Revenue",A3,"Month","Jan")

Disable GETPIVOTDATA auto-generationPivotTable Analyze → uncheck Generate GetPivotData

Dynamic GETPIVOTDATA with cell reference=GETPIVOTDATA("Revenue",$A$3,"Month",B1)

Calculated fields and value display

Add Calculated FieldPivotTable Analyze → Fields, Items and Sets → Calculated Field → name + formula

Profit margin example=Profit/Revenue

Show values as % of Grand TotalRight-click value → Show Values As → % of Grand Total

Show as % of Row or Column TotalShow Values As → % of Row Total / % of Column Total

Running total (cumulative)Show Values As → Running Total In → choose field

Difference from previous periodShow Values As → Difference From → base field + item (Previous)

Rank largest to smallestShow Values As → Rank Largest to Smallest

Index (value relative to overall average)Show Values As → Index

Change aggregation functionRight-click value field → Summarize Values By → choose function

Grouping, slicers and timelines

Group dates by Month/Quarter/YearRight-click any date value → Group → select Month, Quarter, Year

Group numbers into bucketsRight-click number → Group → Starting at, Ending at, By

Group selected items manuallyCtrl+click items → Right-click → Group

Insert Slicer (visual filter panel)PivotTable Analyze → Insert Slicer → choose fieldsdashboards

Connect slicer to multiple pivotsRight-click slicer → Report Connections → check all pivots

Insert Timeline (date range slider)PivotTable Analyze → Insert Timeline

Clear slicer filterClick the X button in top-right of slicer

GETPIVOTDATA — reliable pivot referenceType = then click a cell inside the pivot. Stays correct even when pivot layout changes

Disable GETPIVOTDATA auto-creationPivotTable Analyze → PivotTable → Options → uncheck Generate GetPivotData

Compare sheets

Find missing rows, highlight mismatches, diff two workbooks

Compare two lists

Is value in other list?=COUNTIF(Sheet2!A:A,A2)>0

Show "Missing" if not found=IF(COUNTIF(Sheet2!A:A,A2)=0,"Missing","")

List values only in Sheet1 (365)=FILTER(A2:A100,COUNTIF(Sheet2!A:A,A2:A100)=0)

List values in both sheets (365)=FILTER(A2:A100,COUNTIF(Sheet2!A:A,A2:A100)>0)

Highlight missing — conditional formattingHome → CF → New Rule → Formula: =COUNTIF(Sheet2!$A:$A,A1)=0

Count in A but not in B=SUMPRODUCT((COUNTIF(B:B,A2:A100)=0)*1)

Compare column values

Are two cells equal?=A2=B2

Case-sensitive compare=EXACT(A2,B2)

Compare after trimming whitespace=TRIM(A2)=TRIM(B2)

Compare after case normalization=LOWER(TRIM(A2))=LOWER(TRIM(B2))

Highlight mismatched cellsSelect col B → CF → Formula: =A1<>B1

Count rows where columns differ=SUMPRODUCT((A2:A100<>B2:B100)*1)

Show numeric difference=B2-A2

Show % change=IF(A2=0,"",(B2-A2)/ABS(A2))

For full workbook diff: File → Options → Add-ins → COM Add-ins → enable Inquire. Then Inquire → Compare Files for a visual side-by-side of two workbooks with every changed cell highlighted.

Power Query is the best tool for repeatable merge/compare — every step is recorded and reruns with refreshed data automatically. Data → Get Data → From Table.

Error fixes

Every Excel error — what causes it and how to fix it

Error types & causes

#N/A — value not foundVLOOKUP/MATCH can't find lookup value in source. Fix: check for typos, extra spaces, data type mismatch (text vs number)#N/A

#VALUE! — wrong data typeFormula gets text where it expects a number. Fix: convert text to numbers, check for spaces in numeric cells#VALUE!

#REF! — broken cell referenceReferenced cell was deleted or the formula references an invalid range. Fix: undo delete (Ctrl+Z), or fix formula#REF!

#DIV/0! — dividing by zero or blankDenominator is 0 or empty. Fix: wrap with IFERROR or add IF(B2=0,"",...)#DIV/0!

#NAME? — function name typoExcel doesn't recognize the function. Fix: check spelling, ensure no smart quotes in formula, or function may need 365#NAME?

#NUM! — invalid numeric operationNumber too large, SQRT of negative, DATEDIF order wrong. Fix: check inputs and formula logic#NUM!

#NULL! — incorrect range operatorSpace used instead of comma between ranges. Fix: replace space with comma in SUM(A1:A5 B1:B5) → SUM(A1:A5,B1:B5)#NULL!

##### — column too narrowNot an error — just need to widen the column. Double-click column border to auto-fit#####

Circular reference warningFormula refers to its own cell. Excel → Formulas → Error Checking → Circular References to find itcircular

Fix & suppress errors

Suppress any error, show blank=IFERROR(formula,"")

Suppress any error, show custom text=IFERROR(formula,"Not found")

Suppress only #N/A (keep other errors visible)=IFNA(VLOOKUP(A2,B:C,2,0),"")

Fix #DIV/0! — check denominator=IF(B2=0,"",A2/B2)

Fix #VALUE! — force to number=VALUE(TRIM(A2))

Fix #N/A — trim and lowercase before lookup=VLOOKUP(TRIM(LOWER(A2)),TRIM(LOWER(Sheet2!A:A)),1,0)

Check for error type=ISERROR(A2)

Get error type number1=#NULL!, 2=#DIV/0!, 3=#VALUE!, 4=#REF!, 5=#NAME?, 6=#NUM!, 7=#N/A=ERROR.TYPE(A2)

Select all cells with errorsCtrl+G → Special → Errors

Audit formula with Trace PrecedentsFormulas → Trace Precedents (blue arrows show inputs)

Evaluate formula step by stepFormulas → Evaluate Formula → Step In

Ctrl+Shift+L — Toggle AutoFilter on/off instantly. Add or remove filter dropdowns on any table in one keystroke.

Ctrl+Backspace — Scrolled far away? Snaps the view back to your active cell instantly without moving the selection.

Name Box as range selector — Click the Name Box, type A1:Z1000, press Enter. Selects that range instantly. Also jumps to named ranges by typing their name.

Never merge cells in data ranges — They break sorting, filtering, VLOOKUP, and copy-paste. Use "Center Across Selection" instead (Ctrl+1 > Alignment > Horizontal: Center Across Selection). Looks identical, works correctly.

Left-aligned numbers are stored as text — Real numbers right-align by default. Left-aligned means SUM/AVERAGE will ignore them. Fix: multiply by 1 or use =VALUE(A2).

Dates stored as text won't filter or calculate — Fix: select column > Data > Text to Columns > Finish (just click through). Forces Excel to reparse them as real dates.

Ctrl+T is the #1 Excel habit to build — Converting data to a Table: auto-expands for new rows, formulas fill down, filters built in, PivotTable connections stay fresh on refresh.

F2 colour-codes formula references — Press F2 in any formula cell and each referenced range gets a colour-coded border. Makes complex formula dependencies instantly visible.

Highlight subformula + F9 to evaluate — In formula bar, drag to select any portion of a formula, press F9 to see its result. Press Escape (NOT Enter!) or you replace the formula with the value.

Quick Access Toolbar (QAT) is hugely underused — Right-click any ribbon button > Add to Quick Access Toolbar. Best additions: Save As, Paste Values, Format Cells, Camera Tool.

Alt key shows all ribbon keyboard shortcuts — Press Alt and letter hints appear on every button. Type the sequence without the mouse. E.g. Alt > H > V > V = Paste Values.

Status bar shows instant stats on selection — Select any numbers. The bottom bar shows Sum, Average, Count, Min, Max. Right-click it to add/remove stats. No formula needed.

Camera Tool creates live range images — Pastes a live photograph of any range that updates when data changes. Add to QAT (search Camera in QAT customise). Perfect for dashboards pulling from multiple sheets.

Ctrl+[ traces precedents — From a formula cell, Ctrl+[ selects all cells that feed into it. Ctrl+] selects all cells that depend on it.

INDIRECT and OFFSET are volatile — They recalculate every time anything changes anywhere in the workbook. In large files this kills performance. Use INDEX or named Tables instead.

Whole-column references (A:A) hurt performance — Each A:A scans all 1,048,576 rows. In large workbooks use A2:A50000. Conditional Formatting on A:A is especially damaging.

Ctrl+Shift+F3 bulk-creates named ranges — Select data with headers > Ctrl+Shift+F3 > Excel creates named ranges for each column using its header. Saves enormous time on large datasets.

Analyze Data button (Home tab) — Excel's AI scans your data and suggests charts, pivot tables, and insights automatically. Great starting point for any unfamiliar dataset.

Paste as Picture locks table appearance — Home > Paste dropdown > As Picture. Creates a static image that cannot be accidentally edited. Perfect for locking report templates.

Never delete rows/columns in data models — Deleting shifts references in ALL formulas across the file and can silently break things. Hide rows/columns instead, use filters to work with subsets.

Watch Window monitors distant cells — Formulas > Watch Window > Add Watch. Shows key cells in a floating panel while you work elsewhere in the file.

Ctrl+` shows all formulas at once — Toggles Show Formulas mode. Every cell shows its formula instead of result. Backtick is left of 1. Press again to return.

Excel has 1,048,576 rows and 16,384 columns — Over 17 billion cells per sheet. If you are hitting this limit you need a database (SQL, Power BI), not Excel.

F4 cycles reference types in formulas — With cursor on any reference in formula bar, press F4 to cycle: A1 > $A$1 > A$1 > $A1 > back to A1. Lock rows, columns, or both.

Table structured references never go stale — =Table1[Sales] always means the Sales column regardless of inserted/deleted rows. Far safer than A:A references in complex models.

Right-click the sheet tab reveals hidden options — Insert, Delete, Rename, Move or Copy (to another workbook!), Tab Color, Hide, View Code. Copying a sheet to another workbook takes 3 clicks.

Ctrl+drag a sheet tab = instant copy — Hold Ctrl while dragging a sheet tab to a new position. Releases a copy there. No menus needed.

#N/A in VLOOKUP? 90% of the time it's a data type mismatch. Your lookup value is a number but the table has it stored as text (or vice versa). Test with =ISNUMBER(A2) vs =ISNUMBER(Sheet2!A2) to confirm, then convert.

#REF! after deleting rows/cols? Undo immediately (Ctrl+Z). If you can't undo, look for #REF! in the formula bar — each one represents a deleted range you need to restore or repoint.

Formatting

Conditional formatting, custom number formats, cell styles

Conditional formatting

Highlight cells greater than valueHome → CF → Highlight Cell Rules → Greater Than

Top/Bottom N valuesHome → CF → Top/Bottom Rules → Top 10 Items

Data bars (visual bar in cell)Home → CF → Data Bars

Color scales (heatmap)Home → CF → Color Scales

Icon sets (arrows, traffic lights)Home → CF → Icon Sets

Highlight entire row based on conditionSelect whole data range. CF formula references column A with absolute: =$A1="Done"=$A1="Done"

Highlight duplicates with CFCF → Highlight Cell Rules → Duplicate Values

Highlight blanks with CFCF → New Rule → Formula:=ISBLANK(A1)

Alternate row shadingCF formula for zebra stripes=MOD(ROW(),2)=0

Manage/edit/delete rulesHome → CF → Manage Rules → select scope

Clear all CF from selectionHome → CF → Clear Rules → Clear Rules from Selected Cells

Custom number formats (Ctrl+1)

Open format cells dialogCtrl+1

Thousands separator, 2 decimals#,##0.00

Indian lakh format[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

Show positive as green, negative red[Green]#,##0;[Red]-#,##0

Show + for positive, – for negative+#,##0;-#,##0;0

Percentage with 1 decimal0.0%

Date format DD-MMM-YYYYDD-MMM-YYYY

Time over 24 hours[h]:mm:ss

Hide cell value (display nothing);;;

Show text with number0 "units"

Show millions shorthand0.0,,"M"

Cell & sheet formatting

Bold / Italic / UnderlineCtrl+B / Ctrl+I / Ctrl+U

StrikethroughCtrl+5

Add bordersAlt+H+B

Merge cells (use sparingly — breaks sorting)Home → Merge & Centeravoid

Center across selection (better than merge)Ctrl+1 → Alignment → Horizontal: Center Across Selectionuse this

Wrap text in cellAlt+H+W

Lock cell for editing (protect sheet first)Ctrl+1 → Protection tab → check Locked → then Protect Sheet

Protect sheet with passwordReview → Protect Sheet → enter password

Tab/sheet colorRight-click tab → Tab Color

Charts

Insert, configure, dynamic charts, sparklines, common issues

Create & configure

Insert recommended chartAlt+N+R

Quick chart from selected dataF11 (new sheet) or Alt+F1 (inline)

Change chart typeRight-click chart → Change Chart Type

Switch row/columnChart Design → Switch Row/Column

Add data labelsChart Elements (+) → Data Labels

Add secondary axisRight-click series → Format Data Series → Secondary Axis

Make chart dynamic with Table sourceConvert data to Table (Ctrl+T) — chart auto-expands as rows are addedbest practice

Add sparklines (mini charts in cells)Insert → Sparklines → Line / Column / Win/Loss

Chart troubleshooting

Chart not updating with new dataSource is a plain range — convert to Table. Or manually extend source: right-click → Select Datafix source

Dates show as numbers on axisRight-click axis → Format Axis → Number → Date format

Blank cells creating gaps in line chartSelect chart → Chart Design → Select Data → Hidden and Empty Cells → Connect with line

Remove chart border/backgroundRight-click plot area → Format Plot Area → Fill: None; Border: None

Chart is distorting because axis doesn't start at 0Right-click axis → Format Axis → Minimum: 0

Legend overlapping chartClick legend → drag to outside, or Chart Elements → Legend → Bottom

Chart types — when to use each

Column — compare values across categoriesMonthly revenue, category totals. Vertical bars. Under 12 categories works best

Bar — same as column but horizontalBetter for long category names, rankings, many items

Line — trends over timeStock prices, monthly growth. Data must be in chronological order. Markers for <10 points

Area — trend plus volumeLike line but filled. Stacked area shows composition changing over time

Pie — parts of a wholeOnly 2-6 slices when proportions are the point. Never for time series

Donut — like pie with a holeEasier to compare slices. Can show a KPI number in the centre

Scatter — correlation between two numeric variablesHeight vs weight, price vs demand. X values in one column, Y in another

Bubble — scatter with 3rd variable (size)X + Y position + bubble size = 3 variables in one view

Combo — two chart types on one chartRevenue bars + Growth % line. Insert → All Charts → Combo

Waterfall — running total with up/down stepsCash flow, P&L bridge, budget vs actual variance

Funnel — stages narrowing in valueSales pipelines, hiring funnels, conversion rates

Histogram — distribution of numeric dataGroups values into bins. Insert → Statistical → Histogram

Box and Whisker — distribution + outliersShows median, Q1, Q3, min, max, outliers

Treemap — hierarchical proportions as rectanglesArea = value, colour = category. Portfolio, budget breakdown

Sunburst — multi-level pieInner ring = parent, outer rings = children. Hierarchical data

Filled Map — shade regions by value (365)Colours countries or states by a metric. Insert → Maps → Filled Map365

Chart customisation and tips

Edit chart titleClick chart → click "Chart Title" text → type

Add axis labelsChart Design → Add Chart Element → Axis Titles

Add data labels (values on bars)Chart Design → Add Chart Element → Data Labels

Add secondary Y-axis (dual-axis chart)Right-click series → Format Data Series → Secondary Axis

Add trendlineRight-click series → Add Trendline → choose type

Show trendline equation on chartFormat Trendline → check "Display Equation on chart"

Change one bar's colourClick series (selects all) → click that specific bar again → Format → Fill

Reverse axis orderRight-click axis → Format Axis → Categories in reverse order

Set axis min/max manuallyFormat Axis → Bounds → Minimum / Maximum

Format axis numbers (K, M, % etc.)Format Axis → Number → custom format code

Remove chart borderRight-click chart area → Format Chart Area → No line

Save as template for reuseRight-click chart → Save as Template → reuse via Insert → Templates

Auto-update chart when data growsSource chart from a Table (Ctrl+T). Tables expand automatically, chart picks up new rowsbest practice

Move chart to its own full sheetChart Design → Move Chart → New Sheet

Performance & file issues

Slow workbooks, large file size, volatile functions, calculation mode

Speed up slow workbooks

Switch to manual calculationStops Excel recalculating on every keystrokeFormulas → Calculation Options → Manual

Force recalculate manuallyF9 (all) or Shift+F9 (active sheet)

Volatile functions (recalculate constantly) — avoid where possibleRAND(), NOW(), TODAY(), OFFSET(), INDIRECT(), CELL(), INFO()volatile

Replace VLOOKUP with INDEX/MATCH or XLOOKUPXLOOKUP is faster and more flexible on large datafaster

Replace A:A (whole column) with specific range=COUNTIF(A2:A10000) vs =COUNTIF(A:A) — huge speed differencefaster

Convert formula results to valuesCopy range → Paste Special → Values — removes recalculation overhead

Use Tables instead of OFFSET for dynamic rangesTables auto-expand without volatile OFFSET/INDIRECTbest

Avoid SUMPRODUCT on huge rangesSUMIFS is faster for conditional sums — SUMPRODUCT calculates all array operations in memory

File size & corruption

Reduce file size — delete unused rows/colsCtrl+End to find last used cell — select from there to actual data end and delete

Delete phantom used rangeCtrl+End to find it → select to actual last data row → Delete → Save

Save as .xlsx not .xlsb for compatibility.xlsb is binary and faster but less compatible with Power Query/external tools

Save as .xlsb for pure speedBinary format opens/saves faster on large files

Remove excess conditional formattingHome → CF → Manage Rules → delete unused rules

Recover corrupt fileFile → Open → Browse → select file → click dropdown on Open → Open and Repair

Auto-recover backup locationFile → Options → Save → AutoRecover file location

Compress images in workbookSelect any image → Picture Format → Compress Pictures → All Pictures

Essential shortcuts

Navigation, editing, formatting, formula, and file shortcuts

Navigation

Jump to last used cell in directionCtrl+Arrow

Go to beginning of rowHome

Go to cell A1Ctrl+Home

Go to last used cellCtrl+End

Move between sheetsCtrl+PgUp / Ctrl+PgDn

Go to specific cell or rangeCtrl+G or F5

Scroll without moving selectionScroll Lock + Arrow

FindCtrl+F

Find & ReplaceCtrl+H

Edit & enter data

Enter and move downEnter

Enter and move rightTab

Enter same value in multiple cellsSelect cells → type → Ctrl+Enter

Edit active cellF2

Cancel editEscape

Fill downCtrl+D

Fill rightCtrl+R

Flash fillCtrl+E

Undo / RedoCtrl+Z / Ctrl+Y

Repeat last actionF4 (outside formula)

Delete cell contentsDelete

Delete cell (shift others up/left)Ctrl+–

Line break inside cellAlt+Enter

Today's date (static)Ctrl+;

Current time (static)Ctrl+Shift+;

Copy, paste & format

CopyCtrl+C

CutCtrl+X

PasteCtrl+V

Paste specialCtrl+Alt+V

Paste values onlyAlt+E+S+V → Enter

Format painter (one use)Alt+H+F+P

Format painter (lock on — multiple uses)Double-click the Format Painter button

BoldCtrl+B

ItalicCtrl+I

UnderlineCtrl+U

Format cells dialogCtrl+1

Formulas & functions

Show/hide all formulasCtrl+`

Toggle $ reference (absolute)Press F4 while cursor is on reference in formula barF4

AutoSum selected rangeAlt+=

Insert function wizardShift+F3

Evaluate formula step by stepFormulas → Evaluate Formula

Recalculate workbookF9

Recalculate active sheet onlyShift+F9

Enter array formula (older Excel)In 365, arrays are automaticCtrl+Shift+Enter

Name ManagerCtrl+F3

File & workbook

SaveCtrl+S

Save AsF12

OpenCtrl+O

New workbookCtrl+N

PrintCtrl+P

Close workbookCtrl+W

New sheetShift+F11

Rename sheetDouble-click tab

Insert row/columnCtrl+Shift++

Delete row/columnCtrl+–

Zoom in/outCtrl+Scroll

Spell checkF7

Power Query

Import, transform, merge, and load data without formulas — repeatable with one click

💡 Power Query (Get & Transform) is the most powerful Excel skill for data work. Every transformation is recorded as M code and refreshes instantly when data changes. Access via Data → Get Data.

Import sources

Import from CSV / text fileData → Get Data → From File → From Text/CSV

Import from Excel workbookData → Get Data → From File → From Workbook

Import from folder (combine many files)Data → Get Data → From File → From Folder → Combinepowerful

Import from web URLData → Get Data → From Other Sources → From Web

Import from SQL / databaseData → Get Data → From Database → SQL Server

Import from SharePoint / OneDrive listData → Get Data → From Online Services

Refresh all queriesData → Refresh All (or Ctrl+Alt+F5)Ctrl+Alt+F5

Auto-refresh on openQuery Properties → Refresh data when opening file

Transform steps

Remove duplicate rowsHome → Remove Rows → Remove Duplicates

Filter rows by conditionColumn dropdown → Number/Text Filters → condition

Remove null / blank rowsColumn dropdown → uncheck null

Fill down (propagate values)Transform → Fill → Downbest for merged cells

Split column by delimiterTransform → Split Column → By Delimiter

Trim and clean textTransform → Format → Trim / Clean

Change data typeClick type icon in column header → choose type

Rename columnDouble-click column header

Reorder columnsDrag column headers

Add custom column with formulaAdd Column → Custom Column → write M expression

Add conditional column (if/else)Add Column → Conditional Column → build rules visually

Group by (aggregate rows)Home → Group By → set key column and aggregationlike a pivot

Merge & append

Append (stack tables vertically)Home → Append Queries → choose tables to stack

Merge (join two tables, like SQL JOIN)Home → Merge Queries → set key column + join type

Left join (keep all rows from left table)Merge → Left Outer — nulls appear where no matchmost common

Inner join (only matching rows)Merge → Inner — drops unmatched rows

Anti join (rows NOT in other table)Merge → Left Anti — great for finding missing records

Expand merged columnsClick expand icon ⊞ in merged column header → choose fields

Combine all files in folderFolder import → Combine → Transform Sample Filebatch import

Reshape data

Unpivot columns (wide → long format)Select value columns → Transform → Unpivot Columnsmost useful reshape

Pivot column (long → wide)Transform → Pivot Column → set value column + aggregation

Transpose table (rows ↔ columns)Transform → Transpose

Use first row as headersHome → Use First Row as Headers

Promote / demote headersTransform → Use First Row as Headers / Demote Headers

Extract year/month from date columnDate column → Transform → Date → Year / Month

Index column (add row numbers)Add Column → Index Column → From 1

View / edit M code directlyView → Advanced Editoradvanced

Dynamic Arrays & Spill

Excel 365/2021 spill functions — FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP and array magic

⚡ Dynamic array formulas spill results into multiple cells automatically. Enter them in the top-left cell — no Ctrl+Shift+Enter needed. Reference a spill range with =A1# (the # spill operator).

FILTER — conditional extraction

Filter rows where column equals value=FILTER(A2:C100,B2:B100="Done")

Filter with AND (both conditions true)=FILTER(A2:C100,(B2:B100="Done")*(C2:C100>100))

Filter with OR (either condition)=FILTER(A2:C100,(B2:B100="Done")+(B2:B100="Review"))

Show "No results" if nothing matches=FILTER(A2:C100,B2:B100="X","No results")

Filter by partial text match=FILTER(A2:C100,ISNUMBER(SEARCH("Ltd",A2:A100)))

Count filtered results=ROWS(FILTER(A2:A100,B2:B100="Done"))

Sum filtered results=SUM(FILTER(C2:C100,B2:B100="Done"))

Return single column from filter=FILTER(A2:A100,B2:B100>1000)

SORT & SORTBY

Sort range by first column ascending=SORT(A2:C100)

Sort by second column descending=SORT(A2:C100,2,-1)

Sort by a column not in resultSort names in col A by scores in col B=SORTBY(A2:A100,B2:B100,-1)

Sort by multiple columns=SORTBY(A2:C100,B2:B100,1,C2:C100,-1)

Sort and filter in one formula=SORT(FILTER(A2:C100,B2:B100="Done"),3,-1)

Randomize / shuffle a list=SORTBY(A2:A100,RANDARRAY(99))

Top N items by value=TAKE(SORT(A2:B100,2,-1),5)

UNIQUE & SEQUENCE

List unique values from column=UNIQUE(A2:A100)

Unique across multiple columns=UNIQUE(A2:C100,FALSE,FALSE)

Values that appear exactly once=UNIQUE(A2:A100,FALSE,TRUE)

Count distinct values=COUNTA(UNIQUE(A2:A100))

Sorted unique list=SORT(UNIQUE(A2:A100))

Number sequence 1 to N=SEQUENCE(10)

Sequence as grid (rows × cols)=SEQUENCE(5,4,1,1)

Date sequence (30 days from today)=SEQUENCE(30,1,TODAY(),1)

Row numbers for a spill result=SEQUENCE(ROWS(A1#))

Spill operators & helpers

Reference entire spill range# after cell address selects the whole spill=SUM(A1#)

Reference Nth item of spill=INDEX(A1#,3)

TAKE first N rows of a spill=TAKE(SORT(A2:B100,2,-1),10)

DROP first N rows=DROP(A2:A100,1)

CHOOSEROWS — pick specific rows by index=CHOOSEROWS(A2:C100,1,3,5)

CHOOSECOLS — pick specific columns=CHOOSECOLS(A2:E100,1,3)

TOCOL — flatten 2D range to single column=TOCOL(A2:C10)

TOROW — flatten to single row=TOROW(A2:A20)

WRAPROWS — reshape list into grid=WRAPROWS(A2:A100,4)

Fix #SPILL! error — check for blockersClear all cells in the spill range; hidden content causes thiserror fix

LAMBDA, LET & Custom Functions

Write reusable named functions, eliminate repetition, and build readable formulas

LET — name variables inside formulas

Basic LET syntaxname → value pairs, then calculation using those names=LET(tax,A2*0.18,total,A2+tax,total)

LET with multiple variablesMakes long formulas readable — define each piece=LET(rate,B2/12,n,C2*12,pv,A2,pv*rate/(1-(1+rate)^-n))

LET to avoid duplicate range calcOnly calculates the range once even if referenced multiple times=LET(rng,FILTER(A:A,B:B="Done"),COUNTA(rng)&" items, sum="&SUM(rng))

Debug LET — return intermediate valueTemporarily return an inner variable to inspect it=LET(x,A2*B2,y,x+C2,x)

LAMBDA — create custom functions

Create a reusable LAMBDA functionDefine in Name Manager (Ctrl+F3) → give it a name → paste LAMBDA as value=LAMBDA(rate,years,pv,(1+rate)^years*pv)

Call your named LAMBDAAfter naming it "FutureValue" in Name Manager=FutureValue(0.08,10,A2)

Test LAMBDA inline (without naming)Wrap it in itself with test args to preview=LAMBDA(x,y,x*y)(A2,B2)

Recursive LAMBDA (advanced)Use MAP, REDUCE, SCAN, BYROW, BYCOL helpers365 only

Array helpers (MAP, BYROW, REDUCE)

MAP — apply function to each cell in rangeLike applying a formula to every row without dragging down=MAP(A2:A100,LAMBDA(x,IF(x>100,"High","Low")))

BYROW — aggregate each row into one value=BYROW(A2:D100,LAMBDA(row,SUM(row)))

BYCOL — aggregate each column=BYCOL(A2:D100,LAMBDA(col,AVERAGE(col)))

REDUCE — accumulate across arrayRunning total, factorial, string concatenation=REDUCE(0,A2:A100,LAMBDA(acc,val,acc+val))

SCAN — running calculation (like running total)Returns array of cumulative results=SCAN(0,A2:A100,LAMBDA(acc,val,acc+val))

Finance & Financial Modeling

Loan payments, NPV, IRR, depreciation, bond valuation, and financial formats

Loan & payment functions

Monthly loan paymentrate=annual/12, nper=years*12, pv=loan amount (negative)=PMT(B2/12,C2*12,-A2)

Total interest paid over loan=PMT(B2/12,C2*12,-A2)*C2*12-A2

Interest portion of payment N=IPMT(rate/12,period,nper,-pv)

Principal portion of payment N=PPMT(rate/12,period,nper,-pv)

Loan balance after N payments=PV(rate/12,remaining_nper,payment)

How many periods to repay=NPER(rate/12,-payment,loan_amount)

What interest rate is implied=RATE(nper,-pmt,pv)*12

NPV, IRR & investment

Net Present ValueRate = discount rate; values = cash flows from year 1 onward=NPV(rate,B2:B10)+A2

Internal Rate of Return=IRR(A2:A10)

IRR with guess (for multiple solutions)=IRR(A2:A10,0.1)

Modified IRR (MIRR)=MIRR(A2:A10,finance_rate,reinvest_rate)

XNPV — NPV with irregular dates=XNPV(rate,values_range,dates_range)

XIRR — IRR with irregular dates=XIRR(values_range,dates_range)

Future value of investment=FV(rate/12,nper,-pmt,-pv)

Present value of future cash flow=PV(rate,nper,pmt,fv)

Depreciation & formats

Straight-line depreciation=SLN(cost,salvage,life)

Declining balance depreciation=DB(cost,salvage,life,period)

Double-declining balance=DDB(cost,salvage,life,period)

Sum-of-years digits depreciation=SYD(cost,salvage,life,period)

Format as currency (INR)₹#,##0.00

Format as currency (USD)$#,##0.00

Indian lakh/crore format[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

Show in millions (shorthand)0.0,,"M"

Show in thousands (K)0.0,"K"

Percentage with 2 decimals0.00%

% change formula=IF(A2=0,"",(B2-A2)/ABS(A2))

VBA & Macros

Record, write, and run macros — automate repetitive tasks with Visual Basic

Getting started

Open VBA editorAlt+F11

Record a macroDeveloper tab → Record Macro → do steps → Stop Recording

Run a macroAlt+F8

Enable Developer tabFile → Options → Customize Ribbon → check Developer

Save as macro-enabled workbookRequired to keep macros — save as .xlsm not .xlsx

Run selected sub with F5Cursor inside Sub → press F5

Step through code line by linePress F8 in VBA editor

Add breakpointF9 on the line

Essential VBA snippets

Basic macro structureSub MyMacro() ' Your code here End Sub

Write value to a cellRange("A1").Value = "Hello"

Read value from a cellx = Range("A1").Value

Loop through rows until emptyDim i As Long For i = 2 To Cells(Rows.Count,1).End(xlUp).Row Cells(i,2).Value = Cells(i,1).Value * 2 Next i

Show message boxMsgBox "Done! " & i & " rows processed."

Ask user for inputname = InputBox("Enter name:")

Copy sheet to new workbookActiveSheet.Copy

Save workbook silentlyThisWorkbook.Save

Turn off screen flicker (faster)Application.ScreenUpdating = False ' ... your code ... Application.ScreenUpdating = True

Common automation tasks

Delete all empty rows in rangeOn Error Resume Next Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Apply formula to whole columnRange("B2:B" & lastRow).Formula = "=A2*1.1"

Export each sheet as separate PDFDim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:\" & ws.Name & ".pdf" Next ws

Find last used rowlastRow = Cells(Rows.Count,"A").End(xlUp).Row

Clear a sheet's contents (keep formatting)Sheets("Data").UsedRange.ClearContents

Open another workbookWorkbooks.Open "C:\path\to\file.xlsx"

Print, Export & Share

Print areas, page setup, PDF export, CSV export, and sharing options

Page setup

Set print areaSelect range → Page Layout → Print Area → Set Print Area

Fit sheet to 1 page widePage Layout → Scale to Fit → Width: 1 page

Fit to N pagesFile → Print → Scaling → Fit Sheet to One Page

Repeat header row on every pagePage Layout → Print Titles → Rows to repeat at top

Repeat column on every pagePage Layout → Print Titles → Columns to repeat at left

Set marginsPage Layout → Margins → Narrow / Normal / Wide / Custom

Add header/footerInsert → Header & Footer

Add page number to footerIn footer: &[Page] of &[Pages]&[Page] of &[Pages]

Print gridlinesPage Layout → Sheet Options → Gridlines → Print

Print row/column headings (A, B, C / 1, 2, 3)Page Layout → Sheet Options → Headings → Print

Preview page breaksView → Page Break Preview

Landscape orientationPage Layout → Orientation → Landscape

Export & share

Export to PDFFile → Export → Create PDF/XPSsafe share

Export selection only as PDFFile → Print → Print Selection → Save as PDF (from printer dropdown)

Save as CSVFile → Save As → CSV (Comma delimited) — saves active sheet only

Remove macros before sharingSave as .xlsx (not .xlsm) — strips all VBA codeprivacy

Share to OneDrive for collaborationFile → Share → Save to Cloud first

Send as email attachmentFile → Share → Email → Send as Attachment

Copy table as imageSelect range → Home → Copy → Copy as Picture

Embed Excel in Word/PowerPointIn Word: Insert → Object → Microsoft Excel Worksheet

Protection & Security

Lock cells, protect sheets, workbook passwords, hide formulas

Cell & sheet protection

Protect sheet (prevent edits)Review → Protect Sheet → set password + options

Unprotect sheetReview → Unprotect Sheet → enter password

Lock specific cells only1. Ctrl+A → Format Cells → Protection → uncheck Locked. 2. Select cells to lock → check Locked. 3. Protect Sheet.3 steps

Allow specific users to edit rangesReview → Allow Edit Ranges → add range + password

Hide formula from formula barFormat Cells → Protection → check Hidden → Protect Sheet

Protect workbook structureReview → Protect Workbook → prevents sheet add/delete/rename

Password to open fileFile → Info → Protect Workbook → Encrypt with Passwordremember it!

Mark as final (read-only suggestion)File → Info → Protect Workbook → Mark as Final

Audit & track changes

Track changesReview → Track Changes → Highlight Changes (legacy mode)

Inspect document (remove personal data)File → Info → Check for Issues → Inspect Documentbefore sharing

Check accessibilityFile → Info → Check for Issues → Check Accessibility

See who last modifiedFile → Info → Properties → Last Modified

Version history (OneDrive/SharePoint)File → Info → Version History

Digital signatureFile → Info → Protect Workbook → Add a Digital Signature

Formula Auditing & Debugging

Trace errors, evaluate step-by-step, find circular refs, map dependencies

Auditing tools

Step through formula evaluationFormulas → Evaluate Formula → click Evaluate repeatedlymost useful

Show all formulas in sheetFormulas → Show Formulas (or Ctrl+`)Ctrl+`

Trace precedents (what feeds this cell)Formulas → Trace Precedents

Trace dependents (what uses this cell)Formulas → Trace Dependents

Remove trace arrowsFormulas → Remove Arrows

Find circular referencesFormulas → Error Checking → Circular References → shows cell

Error checking across sheetFormulas → Error Checking → steps through each error

Watch window (monitor cells while scrolling)Formulas → Watch Window → Add Watch → select cells

Debug formulas in-cell

Wrap any part in N() to test number resultSelect part of formula in bar → press F9 to see its valueSelect subformula → F9

Wrap with IFERROR to silence while debugging=IFERROR(your_formula,"CHECK")

Use LET to isolate each calculation stepReturn each variable one at a time to check values=LET(step1,A2*B2,step2,step1+C2,step1)

Check if ranges are same size#VALUE often means mismatched array sizes=ROWS(A2:A100)=ROWS(B2:B100)

Check for hard-coded values in formulasCtrl+G → Special → Constants → Numbers

Find all formula cellsCtrl+G → Special → Formulas

Name manager — find broken namesCtrl+F3 → look for #REF! or errors in refers-to column

Collaboration & Comments

Co-author, comments, @mentions, version history, sharing settings

Comments & notes

Insert comment (threaded, modern)Right-click → New Comment (or Shift+F2)Shift+F2

Insert note (old-style popup)Right-click → New Note

@mention someone in commentType @ then their name — they get email notification

Resolve a comment threadClick comment → Resolve Thread

Show/hide all commentsReview → Show All Comments

Delete all comments in sheetSelect all → Review → Delete

Print with commentsPage Layout → Print Titles → Sheet tab → Comments: At end of sheet

Co-authoring & cloud

Enable real-time co-authoringSave to OneDrive/SharePoint first, then share the link

See who else is editing right nowColored cursors and initials appear in real time

Restore previous versionFile → Info → Version History → open → Restore

Share with editing accessFile → Share → Share → enter email → Can Edit

Share with view-only accessFile → Share → Share → Can View

Get shareable linkFile → Share → Copy Link → set permissions

Stop sharing / revoke accessFile → Share → Manage Access → remove person

Power Pivot & Data Model

Relate multiple tables, handle millions of rows, write DAX measures

🔗 Power Pivot lets you create relationships between tables (like SQL) and build measures using DAX. Enable via: File → Options → Add-ins → COM Add-ins → Microsoft Power Pivot.

Data Model basics

Add table to Data ModelInsert → PivotTable → check "Add to Data Model"

Open Power Pivot windowPower Pivot tab → Manage

Create relationship between tablesPower Pivot → Diagram View → drag common column between tables

Add table from connectionPower Pivot → Get External Data → From Database

Handle millions of rowsPower Pivot uses VertiPaq compression — handles 100M+ rows easilyscalable

DAX measures

Basic measure syntaxCreated in Power Pivot window → New MeasureTotal Sales:=SUM(Sales[Amount])

Filtered measure (CALCULATE)Done Count:=CALCULATE(COUNTA(Tasks[ID]),Tasks[Status]="Done")

Year-to-date totalYTD Sales:=TOTALYTD(SUM(Sales[Amt]),Calendar[Date])

Previous year comparisonPY Sales:=CALCULATE([Total Sales],PREVIOUSYEAR(Calendar[Date]))

Ratio to total (% of grand total)Share:=DIVIDE([Total Sales],CALCULATE([Total Sales],ALL(Sales)))

Count distinct customersUnique Customers:=DISTINCTCOUNT(Sales[CustomerID])

Running total (cumulative)Running:=CALCULATE([Total Sales],FILTER(ALL(Calendar),Calendar[Date]<=MAX(Calendar[Date])))

What-If Analysis & Scenarios

Goal Seek, Solver, Scenario Manager, Data Tables — model uncertainty

Goal Seek & Solver

Goal Seek — find input for desired outputData → What-If Analysis → Goal Seek → Set cell, To value, By changing cellquick

Solver — optimize with multiple variablesData → Solver → set objective, variables, constraintsadd-in required

Enable Solver add-inFile → Options → Add-ins → Excel Add-ins → Solver Add-in

Solver: maximize profitSet Objective = Profit cell, Max, change resource cells, add constraint cells ≤ limits

Circular reference for iterative calcFile → Options → Formulas → Enable Iterative Calculation → set max iterations

Data Tables & Scenarios

One-variable data tableList inputs in column, formula in row above first input, select range → Data → What-If → Data Table → Column input cellsensitivity

Two-variable data tableRow inputs across top, column inputs down side, formula at intersection → Data → What-If → Data Table → both input cells

Create scenarioData → What-If Analysis → Scenario Manager → Add → name it → set changing cells

Generate scenario summary reportScenario Manager → Summary → choose result cells

Switch between scenarios instantlyScenario Manager → select scenario → Show

Pro Tips & Hidden Gems

Lesser-known tricks, time-savers, and Excel features most people miss

Flash Fill (Ctrl+E) — Type the pattern you want in the next column once, then press Ctrl+E. Excel guesses the rest. Works for splitting names, reformatting phone numbers, extracting domains, etc. No formula needed.

Ctrl+; (date) and Ctrl+Shift+; (time) — Instantly inserts today's date or current time as a static value. Great for timestamping data entries.

Double-click Fill Handle — Instead of dragging a formula down, double-click the fill handle (little square at cell corner). It auto-fills down as far as the adjacent column has data.

Ctrl+Shift+L — Toggle AutoFilter on any table in one keystroke. Much faster than going to the ribbon.

Alt+= (AutoSum) — Select a cell below a column or to the right of a row, press Alt+= and Excel writes the SUM formula automatically.

Ctrl+D / Ctrl+R — Fill Down and Fill Right. Select a cell with a formula and the range below/right, then press to copy it everywhere instantly.

F4 key in formulas — Cycles between A1, $A$1, A$1, $A1 reference types. Press while cursor is on a cell reference in the formula bar.

Status bar calculations — Select any range of numbers. The status bar at the bottom shows Sum, Average, Count, Min, Max instantly — no formula needed. Right-click the status bar to add more.

Camera tool — Creates a live image of any range that updates automatically. Add it to Quick Access Toolbar (QAT). Great for dashboards where you want a table's picture elsewhere without formulas.

Ctrl+` (backtick) — Toggle Show Formulas mode. See all formulas at once instead of their results. Toggle back to exit.

Never delete rows/columns in the middle of data models — Use filters and hide instead. Deleting shifts references in formulas across the entire file and can break things in non-obvious ways.

Avoid INDIRECT and OFFSET in large workbooks — They are volatile: Excel recalculates them every time anything changes, even in unrelated cells. Use INDEX or named tables instead.

Whole-column references (A:A) slow things down — In large workbooks, use explicit ranges like A2:A10000 instead of A:A. Each A:A reference scans over 1 million rows.

Alt+Enter inside a cell — Adds a line break within a cell. Enable "Wrap Text" on the cell to see it properly.

Ctrl+Z undo goes back 100 steps — But once you save, you can't undo past that point. Use version history (OneDrive) for a true safety net.

Use Tables (Ctrl+T) for everything — Tables auto-expand, auto-fill formulas, have structured references, and make PivotTables refresh-ready. They are the foundation of good Excel design.

Formula Library

720 formulas from the knowledge base — browse by category or search above