📘 Excel Formulas – Beginner /
Intermediate / Expert
📌 BASIC GUIDE: How to Select Cells
Using the Mouse (Instead of Typing
Address)
1. Selecting a Single Cell
Just click once on the cell.
2. Selecting a Range (A1 to A10)
Click on the first cell (A1)
Hold and drag the mouse down to A10
This automatically enters A1:A10 in formulas.
3. Selecting Non-Adjacent Cells (A1, A4, A7)
Hold Ctrl
Click each cell one by one
Excel automatically adds commas: A1, A4, A7
4. Selecting an Entire Row
Click the row number (e.g., 5).
5. Selecting an Entire Column
Click the column letter (e.g., A).
6. Selecting Table (All Data Together)
Press Ctrl + A while inside the data.
7. Selecting Using Shift Key (Advanced)
Click A1
Hold Shift
Click A20
Excel selects everything between them.
🔹 BEGINNER LEVEL FORMULAS (Most
Basic & Essential)
Useful for simple calculations, attendance, marks, totals, text basics.
Basic Math
=SUM(A1:A10) – Add numbers
=AVERAGE(A1:A10) – Calculate average
=MAX(A1:A10) – Highest value
=MIN(A1:A10) – Lowest value
=COUNT(A1:A10) – Count numbers
=COUNTA(A1:A10) – Count non-empty cells
Basic Text
=LEFT(A1,5) – First characters
=RIGHT(A1,5) – Last characters
=LEN(A1) – Count characters
=UPPER(A1) – Convert to uppercase
=LOWER(A1) – Convert to lowercase
=TRIM(A1) – Remove extra spaces
Basic Date
=TODAY() – Current date
=NOW() – Current date & time
Basic Logical
=IF(A1>50,"Pass","Fail") – Simple condition
🔹 INTERMEDIATE LEVEL FORMULAS
(Most Common in Offices)
Useful for HR, admin work, finance, data cleaning, lookup tasks.
Text & Cleaning
=CONCAT(A1,B1) – Join text
=TEXTJOIN(" ",TRUE,A1:A5) – Join with separator
=MID(A1,3,4) – Extract text
=REPLACE(A1,1,3,"New") – Replace text
Date & Time
=DAY(A1) – Extract day
=MONTH(A1) – Extract month
=YEAR(A1) – Extract year
=DATEDIF(A1,B1,"D") – Days between
=NETWORKDAYS(A1,B1) – Working days
Lookup & References
=VLOOKUP(A1, B1:E20, 3, FALSE) – Vertical search
=HLOOKUP(A1, B1:E20, 2, FALSE) – Horizontal search
=INDEX(B1:B20, 5) – Return value from row
=MATCH("Name", A1:A20, 0) – Find position
=INDIRECT("A"&5) – Reference from text
Math & Random
=POWER(A1,2) – Square
=SQRT(A1) – Square root
=ABS(A1) – Absolute value
=INT(A1) – Integer only
=RANDBETWEEN(1,100) – Random number
Error Handling
=IFERROR(A1/B1,"Error") – Avoid error message
🔹 EXPERT LEVEL FORMULAS (Advanced
Work, MIS, Data Analysis)
Useful for automation, dashboards, analysis, large datasets.
Advanced Lookup
=XLOOKUP(A1, B1:B20, C1:C20) – Modern, best lookup
=INDEX(A1:C20, MATCH(D1, A1:A20, 0), 2) – INDEX + MATCH combo
Arrays & Data Manipulation
=SUMPRODUCT(A1:A10, B1:B10) – Multiply + sum
=TRANSPOSE(A1:C3) – Switch rows/columns
=UNIQUE(A1:A100) – Unique values
=SORT(A1:A100) – Sort data
=FILTER(A1:B50, B1:B50>50) – Filter with condition
Financial
=PMT(rate, nper, pv) – Loan EMI
=FV(rate, nper, pmt) – Future value
=PV(rate, nper, pmt) – Present value
=IRR(values) – Internal rate of return