Ten Times Your Productivity With These 22 Excel Mist-Known Formulas

 

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")

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<2)

Both conditions are true => Output is true

=OR(S1>5;S2<2)

Just 1 condition is true => Output is true

18. CONCATENATE

Joins two or more text strings

=CONCATENATE(T1," ",T2)

Combine first name and last name in one cell

19. LEFT/RIGHT

Extracts characters from the start/end of a text string

=LEFT(U1;5)

Excel

=RIGHT(U1;5)

Class

20. MID

Extracts characters from the middle of a text string

=MID(V1;7;7)

7 characters starting from the 7th => Mastery

21. TEXT

Converts a value to text in a specific number format

=TEXT(V1;”MMM-YY")

7/02/2024 => Feb-24

22. IFERRROR

Returns a custom result if a formula errors out

=IFERROR(W1,"0")

Give 0 as output, instead of "# div" error