" Top 21 Excel Formulas" 10x your productivity with these 21 Excel must known formulas

Top 21 Excel Formulas

👇 👇 👇

👉 Download the High Resolution PDF: https://lnkd.in/dkrCMWaq

10x your productivity with these 21 Excel must known formulas:

1. SUM Adds all numbers in a range

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

1. AVERAGE Calculates the average of numbers in a range

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

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

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

1. MIN Identifies the lowest number in a range

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

1. MAX Finds the highest number in a range

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

1. COUNT Counts the number of cells that contain numbers

Example: =COUNT(F1:F9)
Count numeric cells in F1 to F9

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

Example: =INDEX(K1:L10,2,3)

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

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

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

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

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

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

1. COUNTIF/COUNTIFS Counts cells that meet a condition

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

1. COUNTA Counts non-empty cells

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

1. SUMIF / SUMIFS Counts non-empty cells

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

1. IF / NESTED IFS Performs conditional tests

Example: =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)

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

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

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

Examples:
=AND(S1>5;S2<2)
Both conditions are true => Output is true

1. CONCATENATE Joins two or more text strings

Example: =CONCATENATE(T1," ",T2)
Combine first name and last name in one cell

1. LEFT/RIGTH Extracts characters from the start/end of a text string

Examples:
=LEFT(U1;5)
Excel

=RIGHT(U1;5)
Class

1. MID Extracts characters from the middle of a text string

Example: =MID(V1;7;7)
7 characters starting from the 7th => Mastery

1. TEXT Converts a value to text in a specific number format

Ex: =TEXT(V1;”MMM-YY")
7/02/2024 => Feb-24

1. IFERRROR Returns a custom result if a formula errors out

Ex: =IFERROR(W1,"0")
Give 0 as output, instead of "# div" error"