M I C R O S O F T E X C E L

Excel
Complete Reference Notes
Formulas · Functions · Lookups · Pivot Tables · Automation

12 SECTIONS · A ONE-STOP DESK REFERENCE

From core spreadsheet mechanics to dynamic arrays, LET/LAMBDA and QA-ready dashboards.
 Excel Complete Reference Notes

Contents at a Glance
Twelve sections, each with function tables, worked examples, best practices, and an interview tip.

📗 1 Excel Basics 🧮 2 Basic Formulas

Workbook, worksheet, cells & the interface SUM, AVERAGE, MIN/MAX & cell references

🔀 3 Logical Functions 🔎 4 Lookup & Reference

IF, IFS, IFERROR, AND/OR/NOT XLOOKUP, VLOOKUP, INDEX + MATCH

🔤 5 Text Functions 📅 6 Date & Time Functions

LEFT, RIGHT, TRIM, TEXTJOIN, TEXTSPLIT TODAY, EDATE, EOMONTH, NETWORKDAYS

🧹 7 Data Cleaning & Validation 📊 8 Pivot Tables & Charts

Sort, Filter, Duplicates, Validation Slicers, timelines, recommended charts

🚀 9 Advanced Excel ⚙️ 10 Automation & Productivity

FILTER, SORT, UNIQUE, LET, LAMBDA Power Query, Power Pivot, Macros, VBA

🧾 11 Cheat Sheet — Math & Statistics 🔍 12 Cheat Sheet — Lookup & Text
14 must-know numeric formulas 15 must-know lookup & text formulas

Page 2 · 20
 Excel Complete Reference Notes

📗 Excel Basics
SECTION 1

Excel is a powerful spreadsheet application by Microsoft used to store, organize, analyze, and visualize data
efficiently.

Workbook vs. Worksheet

• Workbook — the entire Excel file, saved with the .xlsx extension.
• Worksheet — an individual sheet living inside a workbook.

Rows, Columns & Cells

• Rows — horizontal collections of cells, numbered 1, 2, 3…
• Columns — vertical collections of cells, labeled A, B, C…
• Cells — the intersection of a row and a column, e.g. B2 is the intersection of column B and row 2.

Interface Overview
Element What it does
Cell Address Unique location of a cell, e.g. B2.
Name Box Shows the address or name of the active cell.
Formula Bar Displays and edits the content or formula of the active cell.
Ribbon Contains tabs (Home, Insert, Formulas, Data…) and the commands to perform actions.
Sheet Tabs Switch between the worksheets inside the current workbook.

✅ Best Practice
Keep data clean and structured.
Use meaningful sheet names.
Use formulas instead of hardcoding values.

🏆 Interview Tip
Know the difference between Workbook and Worksheet, and be ready to explain cell referencing (Relative: A1,
Absolute: $A$1). Practice navigating with the Ribbon and keyboard shortcuts.

Page 3 · 20
 Excel Complete Reference Notes

🧮 Basic Formulas
SECTION 2

Common Basic Formulas

Formula What it does
SUM(range) Adds all numbers in the range.
AVERAGE(range) Returns the average value.
MIN(range) Returns the smallest value.
MAX(range) Returns the largest value.
COUNT(range) Counts cells with numbers.
COUNTA(range) Counts non-empty cells.

Worked Example
Item Amount
Book 250
Pen 50
Bag 700
Total 1000

=SUM(B2:B4) → 1000 =AVERAGE(B2:B4) → 333.33 =MIN(B2:B4) → 50

=MAX(B2:B4) → 700 =COUNT(B2:B4) → 3 =COUNTA(B2:B4) → 3
💡 SUM, AVERAGE, MIN, and MAX only work on numeric values. COUNTA counts any non-empty cell — text, numbers, dates,
and more.

Cell References
Type Example Behavior when copied to B2
Relative (changes) A1 Becomes B2.
Absolute (fixed) $A$1 Remains $A$1.
Mixed — column fixed $A1 Becomes $A2.
Mixed — row fixed A$1 Becomes B$1.

✅ Best Practice
Use meaningful range names.
Prefer formulas over hardcoding values.
Use absolute references in reports & dashboards.
Double-check ranges before applying formulas.

Page 4 · 20
 Excel Complete Reference Notes

🏆 Interview Tip
Q: What is the difference between COUNT and COUNTA?
A: COUNT counts only cells with numbers, while COUNTA counts all non-empty cells (numbers, text, dates, logical
values, etc.).

Page 5 · 20
 Excel Complete Reference Notes

🔀 Logical Functions
SECTION 3

Logical functions help you make decisions, test conditions, and return results based on TRUE or FALSE — the
backbone of reports, scorecards, dashboards, and data-quality checks.

Common Logical Functions

Function What it does Syntax Example → Result
Returns one value if a condition is =IF(B2>=50,"Pass","
IF() IF(condition, if_true, if_false)
TRUE, another if FALSE. Fail") → Pass
=IFS(B2>=90,"A",B2>
Checks multiple conditions, returns
IFS() IFS(cond1, val1, cond2, val2, …) =75,"B",B2>=50,"C")
the value for the first TRUE one. → A / B / C
=IFERROR(A2/
Returns a custom value if a formula
IFERROR() IFERROR(value, value_if_error) B2,"Cannot Divide")
errors out. → Cannot Divide
Returns TRUE only if all conditions are =AND(B2>50,C2<100)
AND() AND(cond1, cond2, …)
TRUE. → TRUE / FALSE

Returns TRUE if any one condition is =OR(B2>50,C2<100)

OR() OR(cond1, cond2, …)
TRUE. → TRUE / FALSE
=NOT(B2>50) →
NOT() Reverses a logical value. NOT(logical)
TRUE / FALSE

Nested IF
An IF inside another IF handles multiple conditions step by step:
=IF(B2>=90,"A", IF(B2>=75,"B", IF(B2>=50,"C","Fail")))

✅ Best Practice
Keep conditions simple.
Use IFS() instead of stacking multiple nested IFs.
Use IFERROR() to handle errors gracefully.
Combine AND(), OR(), NOT() for powerful validations.

🏆 Interview Tip
Q: What's the difference between IF and IFS?
A: IF checks one condition; IFS checks multiple conditions in a clean, readable way — no nesting required.

Page 6 · 20
 Excel Complete Reference Notes

🔎 Lookup & Reference

SECTION 4

Lookup functions find and return data from a table or range based on a matching value.

Common Lookup Functions

Function What it does Example Notes
Searches both ways,
Searches a value in any direction and =XLOOKUP(D2,A2:A10,B2
XLOOKUP() ★ exact/approx match, handles
returns the matching result. :B10,"Not Found")
errors.
Looks up a value in the first column, =VLOOKUP(D2,A2:C10,3, Left-to-right only; use FALSE for
VLOOKUP()
returns a value from the same row. FALSE) exact match.
Looks up a value in the top row,
=HLOOKUP(D1,A1:Z5,4,F Top-to-bottom only; use FALSE
HLOOKUP() returns a value from the same
ALSE) for exact match.
column.
Returns the value at a given row & Very flexible — pairs perfectly
INDEX() =INDEX(B2:D10,3,2)
column position. with MATCH.
Returns the position of a value in a 0 = exact, 1 = less than, -1 =
MATCH() =MATCH(E2,A2:A10,0)
row or column. greater than.
Enhanced MATCH with more search =XMATCH(E2,A2:A10,0,- Searches any direction, more
XMATCH() ★
options. 1) match modes.

How INDEX + MATCH Works

Lookup Value → MATCH() finds position → INDEX() returns value

Worked Example
ID Name Product Sales
101 Ravi Laptop 50000
102 Neha Mobile 30000
103 Amit Tablet 20000
104 Kiran Watch 15000

Sales for ID 103: =INDEX(D2:D5, MATCH(103, A2:A5, 0)) → 20000

✅ Best Practice
Prefer XLOOKUP for new formulas.
Use INDEX + MATCH for maximum flexibility.
Always use exact match (FALSE or 0) unless approximate match is needed.
Keep lookup ranges clean and sorted where required.

Page 7 · 20
 Excel Complete Reference Notes

🏆 Interview Tip
Q: Which is better — VLOOKUP or XLOOKUP?
A: XLOOKUP is more powerful — it searches in both directions, handles errors natively, and returns exact or
approximate matches with ease.

Page 8 · 20
 Excel Complete Reference Notes

🔤 Text Functions
SECTION 5

Text functions extract, clean, combine, and manipulate text data — essential for cleaning messy data and
standardizing formats.

Common Text Functions

Function What it does Example → Result
LEFT() Leftmost characters from text. =LEFT("QA Insights",2) → QA

RIGHT() Rightmost characters from text. =RIGHT("QA Insights",8) → Insights

MID() Characters from the middle of text. =MID("QA Insights",4,7) → Insights

LEN() Total number of characters in text. =LEN("QA Insights") → 11

TRIM() Removes extra spaces from text. =TRIM(" QA Insights ") → QA Insights
=CONCAT("QA"," ","Insights") → QA
CONCAT() Joins multiple text strings into one.
Insights
Joins text with a delimiter, can ignore =TEXTJOIN(", ",TRUE,"QA","Insights") →
TEXTJOIN()
empties. QA, Insights

Splits text into rows/columns by

TEXTSPLIT() =TEXTSPLIT("QA-Insights-2026","-")
delimiter.

✅ Best Practice
Use TRIM() before matching or comparing data.
Prefer TEXTJOIN() over CONCAT() for ranges.
Use TEXTSPLIT() to break data into rows/columns quickly.
Combine with IF(), FILTER(), UNIQUE() for cleaning pipelines.

Real-World Use
• Data cleaning & standardization.
• Preparing reports & dashboards.
• Parsing logs, emails, IDs, and addresses.

🏆 Interview Tip
Q: Difference between CONCAT() and TEXTJOIN()?
A: CONCAT() joins text but includes empty cells. TEXTJOIN() adds a delimiter and can ignore empty cells via its
ignore_empty argument.

Page 9 · 20
 Excel Complete Reference Notes

📅 Date & Time Functions

SECTION 6

These functions handle dates and time values — essential for calculations, reporting, scheduling, and SLA tracking.

Common Date & Time Functions

Function What it does Example → Result
TODAY() Returns today's date. =TODAY() → 15-May-2026

NOW() Returns current date & time. =NOW() → 15-May-2026 10:30 AM

DATE() Creates a date from year, month, day. =DATE(2026,5,15) → 15-May-2026

YEAR() Extracts the year from a date. =YEAR(A2) → 2026

MONTH() Extracts the month from a date. =MONTH(A2) → 5

DAY() Extracts the day from a date. =DAY(A2) → 15

EDATE() Date N months before/after a start date. =EDATE(A2,3) → 15-Aug-2026

EOMONTH() Last day of the month N months away. =EOMONTH(A2,0) → 31-May-2026

NETWORKDAYS() Workdays between two dates. =NETWORKDAYS(A2,B2,C2:C10) → 18

Worked Example — Joining Date in A2 = 15-Feb-2024

Completion after 6 months =EDATE(A2,6) → 15-Aug-2024
Last day of that month =EOMONTH(A2,6) → 31-Aug-2024
Days worked till 15-May-2026 =NETWORKDAYS(A2,TODAY()) → 587
Year of joining =YEAR(A2) → 2024

✅ Best Practice
Store dates as real date values, not as text.
Use TODAY() instead of typing static dates.
Use NETWORKDAYS() for SLA & business-day calculations.
Combine with IF(), DATEDIF(), WORKDAY() for advanced use.

🏆 Interview Tip
Q: What's the difference between EDATE() and EOMONTH()?
A: EDATE() returns the same day in a future/past month; EOMONTH() returns the last day of that month.

Page 10 · 20
 Excel Complete Reference Notes

🧹 Data Cleaning & Validation

SECTION 7

Data cleaning is the process of fixing or removing incorrect, incomplete, duplicate, or inconsistent data. Clean data
leads to accurate reports, better decisions, and reliable analysis.

Key Features
Feature What it does Example / Use Case
Sort Arranges data ascending or descending. Sort employees by salary, high to low.
Filter Displays only rows meeting criteria. Filter only "Passed" test cases.
Remove Duplicates Removes duplicate rows. Remove duplicate Employee IDs.
Flash Fill Auto-fills data based on a pattern. Split first & last name automatically.
Text to Columns Splits one cell into multiple columns. Split "City,State,Country" into 3 columns.
Data Validation Restricts input based on rules. Allow only "Pass/Fail" in a Result column.
Conditional Formatting Highlights cells based on conditions. Highlight past-due dates.

Workflow
Raw Data → Clean & Validate → Review & Verify → Analyze & Report

💡 Good data in → good insights out.

✅ Best Practice
Always validate data at the point of entry.
Remove duplicates before analysis.
Use clear rules in Data Validation.
Use Conditional Formatting to flag exceptions.
Keep source data safe — work on a copy.

🏆 Interview Tip
Q: Why is data cleaning important in Excel?
A: Clean data ensures accuracy, reduces errors, and enables reliable reporting and decision-making.

Page 11 · 20
 Excel Complete Reference Notes

📊 Pivot Tables & Charts

SECTION 8

Pivot Tables summarize, analyze, explore, and present large datasets quickly — converting raw data into meaningful
insights without complex formulas.

Key Features
Feature What it does Benefit
Pivot Table Summarizes data by rows, columns and values. Quick summaries & deep insights.
Pivot Chart Creates charts from pivot table data. Visualize data for better understanding.
Slicers Interactive filter buttons. User-friendly, fast filtering.
Timelines Filter date fields with a slider. Easy time-based analysis.
Recommended Charts Suggests the best chart type. Save time, pick the right visual.
Complete business overview in one
Dashboard Basics Combine tables, charts, slicers & KPIs.
page.

How It Works
Raw Data → Create Pivot Table → Insert Pivot Chart → Add Slicers/Timelines → Build Dashboard

💡 Refresh data → pivot updates → dashboard stays dynamic!

Recommended Charts Guide

Goal Best Chart
Compare categories Column Chart
Show trend over time Line Chart
Show part-to-whole Pie / Doughnut
Compare multiple items Bar Chart
Show relationship Scatter Chart
Show progress to goal Gauge / KPI

✅ Best Practice
Keep source data clean before creating a pivot.
Use meaningful field names.
Refresh the pivot whenever source data changes.
Use slicers & timelines for interactivity.
Keep dashboards simple, clear & focused.

Page 12 · 20
 Excel Complete Reference Notes

🏆 Interview Tip
Q: Difference between a Pivot Table and a Table in Excel?
A: A Table stores and manages data with formatting and filters. A Pivot Table summarizes, analyzes, and reports data
dynamically.

Page 13 · 20
 Excel Complete Reference Notes

🚀 Advanced Excel
SECTION 9

Key Advanced Functions

Function What it does Result / Use Case
FILTER() Returns filtered records based on a condition. All matching rows, spilled automatically.
SORT() Sorts data by one or more columns. e.g. sort by 3rd column, descending.
UNIQUE() Returns unique values from a range. A clean, deduplicated list.
SEQUENCE() Generates sequential numbers, dates or arrays. Dynamic number grids or date lists.
LET() Names intermediate calculations for reuse. Easier formulas, better performance.
LAMBDA() Creates custom, reusable functions. Build your own Excel functions.
Dynamic Arrays Spill results automatically into ranges. FILTER, SORT, UNIQUE all spill by default.
Named Ranges Names for cells/ranges used in formulas. Improves readability & maintainability.

How They Work Together

Raw Data → FILTER() clean records → SORT() order data → UNIQUE() dedupe → LET()/LAMBDA() simplify

Example — Total Sales After Discount

=LET(Sales, C2:C100, Discount, D2:D100, Total, Sales*(1-Discount), SUM(Total))

✅ Best Practice
Prefer FILTER() over complex IF() + copy-paste solutions.
Use LET() to break complex formulas into readable steps.
Use Named Ranges for important datasets and parameters.
Combine FILTER + SORT + UNIQUE for powerful reports.
Keep formulas dynamic to reduce manual updates.

🏆 Interview Tip
Q: What's the advantage of Dynamic Arrays?
A: They automatically spill results into adjacent cells, cutting manual work and making reports far more flexible.

Page 14 · 20
 Excel Complete Reference Notes

⚙️ Automation & Productivity

SECTION 10

Automation Features
Feature What it does Example / Use Case
Load daily results from CSV, clean &
Power Query Import, clean, transform & combine data.
report.
Model test metrics: defects, execution,
Power Pivot Build data models with DAX measures.
coverage.
Macros Record actions and replay automatically. Format reports, apply filters, export data.
Custom validation, auto email reports,
VBA Basics Write custom scripts.
buttons.
Freeze Panes Keep rows/columns visible while scrolling. Lock header row on long result sheets.
Protect Sheet Lock cells/structure from edits. Protect formulas and critical report data.

Excel for QA & Data Analysis

• Analyze test execution, defects, requirements and coverage.
• Create dashboards, trend reports & KPIs.
• Validate data quality and business rules.
• Track metrics like Pass %, Defect Density, Reopen Rate.
• Integrate with APIs, databases, and automation tools.

Keyboard Shortcuts to Know

Shortcut Action
Ctrl + T Create Table
Ctrl + Shift + L Toggle Filter
Ctrl + Arrow Keys Jump to edge of data
Ctrl + Shift + $ Apply Currency format
Ctrl + 1 Format Cells
Alt + = AutoSum
Ctrl + Z / Ctrl + Y Undo / Redo
Ctrl + S Save
F4 Repeat last action

QA Dashboard Flow
Import (Power Query) → Model (Power Pivot) → Analyze & Visualize → Dashboard & Reports

Page 15 · 20
 Excel Complete Reference Notes

✅ Best Practice
Use Power Query for repeatable data preparation.
Prefer Power Pivot + DAX for large-scale analysis.
Record Macros for simple tasks, use VBA for complex logic.
Protect important sheets and cells.
Keep dashboards clean, interactive and meaningful.

🏆 Interview Tip
Q: When would you use Power Query over formulas?
A: When the data source changes frequently, needs cleaning/transformation, or requires multiple steps before
analysis — it's faster, repeatable, and reduces manual error.

Page 16 · 20
 Excel Complete Reference Notes

🧾 Formula Cheat Sheet — Math & Statistics

SECTION 11

# Formula What it does Example → Result

1 SUM() Adds all numbers in a range. =SUM(B2:B10) → 1250

2 AVERAGE() Returns the arithmetic mean. =AVERAGE(B2:B10) → 125

3 COUNT() Counts cells that contain numbers. =COUNT(B2:B10) → 10

4 COUNTA() Counts cells that are not empty. =COUNTA(A2:A10) → 10

5 COUNTIF() Counts cells meeting one criterion. =COUNTIF(B2:B10,">100") → 6

=COUNTIFS(B2:B10,">100",C2:C10,"East
6 COUNTIFS() Counts cells meeting multiple criteria.
") → 3
7 SUMIF() Adds cells meeting one criterion. =SUMIF(C2:C10,"East",B2:B10) → 780
=SUMIFS(B2:B10,C2:C10,"East",D2:D10,
8 SUMIFS() Adds cells meeting multiple criteria.
">=2025-01-01") → 950
=AVERAGEIF(C2:C10,"West",B2:B10) →
9 AVERAGEIF() Averages cells meeting one criterion.
142.5
10 ROUND() Rounds to a specified number of digits. =ROUND(123.456,2) → 123.46

11 ROUNDUP() Rounds away from zero. =ROUNDUP(123.451,2) → 123.46

12 ROUNDDOWN() Rounds toward zero. =ROUNDDOWN(123.459,2) → 123.45

13 ABS() Returns the absolute (positive) value. =ABS(-45.67) → 45.67

14 MOD() Returns the remainder after division. =MOD(17,5) → 2

How They Work Together

Raw Data → Apply Functions → Summarize & Analyze → Insightful Reports

💡 Clean data + the right formulas = accurate insights.

✅ Best Practice
Use SUMIFS/COUNTIFS for multi-criteria analysis.
Always lock ranges with $ for stable formulas.
Use AVERAGEIF instead of a complex IF + AVERAGE combo.
Round values only in the final report layer.
Keep formulas simple, documented & tested.

🏆 Interview Tip
Q: What's the difference between COUNT() and COUNTA()?
A: COUNT() counts only numeric cells; COUNTA() counts all non-empty cells, including text, blanks-with-spaces, and
more.

Page 17 · 20
 Excel Complete Reference Notes

🔍 Formula Cheat Sheet — Lookup & Text

SECTION 12

# Formula What it does Example → Result

Looks up a value, returns a matching =XLOOKUP(101,A2:A10,B2:B10,"Not
1 XLOOKUP()
result. Found") → Product A

Looks up in the first column, returns

2 VLOOKUP() =VLOOKUP(101,A2:D10,3,FALSE) → $250
another.
3 INDEX() Value at a given row & column. =INDEX(B2:D10,3,2) → $450

4 MATCH() Position of a value in a range. =MATCH(101,A2:A10,0) → 2

5 IF() Logical test → value if TRUE / FALSE. =IF(C2>=500,"Pass","Fail") → Pass

=IFERROR(VLOOKUP(999,A2:D10,2,FALSE),"
6 IFERROR() Custom value if a formula errors.
NA") → NA
7 LEFT() Leftmost characters from text. =LEFT("QA Insights",2) → QA

8 RIGHT() Rightmost characters from text. =RIGHT("QA Insights",8) → Insights

9 MID() Characters from the middle of text. =MID("QA Insights",4,8) → Insights

10 LEN() Total character count. =LEN("QA Insights") → 11

=TRIM(" QA Insights ") → QA
11 TRIM() Removes extra spaces.
Insights
12 TEXTJOIN() Joins a range with a delimiter. =TEXTJOIN(", ",TRUE,A2:A4) → A, B, C
=FILTER(A2:C10,C2:C10="Pass") →
13 FILTER() Filtered records matching a condition.
Filtered Rows
14 UNIQUE() Unique values from a range. =UNIQUE(A2:A10) → Unique List

15 SORT() Sorts ascending or descending. =SORT(A2:C10,2,-1) → Sorted Data

Lookup Flow
Find Value → Search Range → Return Result → Match Found!

✅ Best Practice
Pair XLOOKUP with IFERROR for clean fallback values.
Combine FILTER + SORT + UNIQUE for dynamic, self-updating reports.
Keep lookup and return ranges the same size.
Test formulas on sample data before scaling to full datasets.

🏆 Interview Tip
Q: What's the difference between VLOOKUP and XLOOKUP?
A: VLOOKUP can only look left to right; XLOOKUP can search in both directions and natively handles "not found"
results.

Page 18 · 20
 Excel Complete Reference Notes

Page 19 · 20
 Excel Complete Reference Notes

You've covered the full toolkit

Basics → Formulas → Logic → Lookups → Text → Dates → Cleaning → Pivots → Advanced Arrays →
Automation → Two Full Cheat Sheets

Keep this guide handy at your desk — and revisit the Interview Tip boxes before your next screen.

Page 20 · 20