EXCEL Learning

Excel for Beginners vs Advanced — Must-Know Formulas 👇

Whether you're just getting started or already building advanced solutions, these formulas are game-changers:

📌 Beginner Formulas:
TRIM() – Clean up messy text
CONCAT() – Combine multiple cells
PROPER() – Capitalize each word
TEXT() – Format numbers/dates
LEFT() / RIGHT() – Extract characters
RAND() / RANDBETWEEN() – Generate random values
📌 Advanced Formulas:
FILTER() – Extract data by condition
UNIQUE() – Get distinct values
SEQUENCE() – Create number series
LET() – Name and reuse variables
LAMBDA() – Build custom functions
SORTBY() – Sort based on another range
TEXTJOIN() – Merge text with delimiter

Excel isn’t just for tables — it’s a tool for smart, scalable solutions.

#
1. SUM
Adds all numbers in a range

=SUM(A1:A9)
Add values from cell A1 to A9

2. AVERAGE
Calculates the average of numbers in a range

=AVERAGE(B1:B9)
Average of B1 to B9

3. ROUND
Rounds a number to a specified number of digits

=ROUND(C1;2)
Round C1 to 2 decimal places

4. MIN
Identifies the lowest number in a range

=MIN(D1:D9)
Find the minimum in D1 to D9

5. MAX
Finds the highest number in a range

=MAX(D1:D9)
Get the maximum value in D1 to D9

6. COUNT
Counts the number of cells that contain numbers

=COUNT(F1:F9)
Count numeric cells in F1 to F9
*It only counts the numerical inputs

7. INDEX
Returns a value in table based on row & column number

=INDEX(K1:L10,2,3)
For the value in the 2nd row & 3rd column from the range K1:K10

8. MATCH
Searches for a value and returns its relative position

=MATCH(L1,M1:M9,0)
To find L1's position from the range M1:M9

9. VLOOKUP
Searches for a value in the first column of a range

=VLOOKUP (G1,H1:H10,2,FALSE)
To find G1 in H1:H10

10. HLOOKUP
Searches for a value in the top row of a table

*Number of rows that contains the data you want to retrieve

=HLOOKUP(I1,J1:S2,2,FALSE)
To find I1 in the top row J1:S2

11. XLOOKUP
Finds a value in one range & returns a result from another

=XLOOKUP(A7,A2:A4,B2:C4)
To find A7 in A2:A4 & return the corresponding value from B2:C4 if a match is found

12. COUNTIF/COUNTIFS
Counts cells that meet a condition

=COUNTIF (N1:N9;">5")
To count cells greater than 5

13. COUNTA
Counts non-empty cells

=COUNTA(O1:O9)
To count all non-empty cells in a range

14. SUMIF / SUMIFS
Counts non-empty cells

=SUMIF(P1:P9,">5")
Add cells that meet a condition

15. IF / NESTED IFS
Performs conditional tests

=IF(R1>5;"Yes";"No")
For simple conditions

Note:

IF: 1 variable
IFS: multiple variables

=IF(test, true, false)

=IF(test1, true, IF(test2, true, false)
Nested IF

=IFS(test1, true, test2, true, test3, true)

16. AVERAGEIF / AVERAGEIFS
Calculates the average for cells that meet criteria

=AVERAGEIF(Q1:Q9;">5")
Average of values over 5

17. Logical Operators (AND, OR)
Combines multiple conditions

=AND(S1>5;S2 Output is true

=OR(S1>5;S2 Output is true