Microsoft Excel is more than just rows and columns—it’s a dynamic tool that empowers users to perform complex calculations, analyze data, and visualize trends. As a beginner, understanding the following essential formulas will set you on the path to becoming an Excel pro.
https://www.youtube.com/watch?v=sFn0gE_y1U4
1. SUM
The SUM formula is your go-to for adding up numbers. Simply select a range of cells (e.g., A1:A10) and use:
\text{=SUM(A1:A10)}
This formula calculates the total sum of the specified cells.
2. AVERAGE
To find the average of a range of values, use the AVERAGE formula:
\text{=AVERAGE(B1:B20)}
Replace B1:B20 with your desired range. Excel will compute the average value.
3. COUNT
Need to count how many items are in a list? Use COUNT:
\text{=COUNT(C1:C100)}
This formula counts the non-empty cells within the specified range.
4. MAX and MIN
To find the highest and lowest values in a range, use MAX and MIN:
- Maximum value:
\text{=MAX(D1:D50)}
- Minimum value:
\text{=MIN(D1:D50)}
5. IF
Conditional logic is crucial. The IF formula allows you to create custom conditions. For example:
\text{=IF(E1>50, “Pass”, “Fail”)}
This formula checks if the value in cell E1 is greater than 50. If true, it returns “Pass”; otherwise, it returns “Fail.”
6. VLOOKUP
The VLOOKUP formula searches for a value in a table and returns a corresponding value from another column. Syntax:
\text{=VLOOKUP(F1, TableRange, ColumnNumber, False)}
- F1: The value to search for.
- TableRange: The range containing your data.
- ColumnNumber: The column index from which to retrieve the result.
- False: Exact match required.
7. CONCATENATE
Combine text from different cells using CONCATENATE (or the & operator):
\text{=CONCATENATE(G1, ” “, G2)}
This formula joins the contents of cells G1 and G2 with a space in between.
8. DATE
Working with dates? The DATE formula constructs a date based on year, month, and day:
\text{=DATE(2024, 8, 31)}
This returns August 31, 2024.
9. TEXT
The TEXT function allows you to format numbers as text. For instance:
To display the value in cell H1 as a percentage with two decimal places:
=TEXT(H1, “0.00%”)
This formula converts the numeric value to a percentage format.
10. INDEX and MATCH (Advanced Usage)
The combination of INDEX and MATCH is a powerful alternative to VLOOKUP. It allows you to search for a value in a table and return a corresponding value from any column, not just the rightmost one. Here’s how it works:
- INDEX:
- The INDEX function retrieves a value from a specified range based on row and column numbers.
- Syntax:
- =INDEX(Range, RowNumber, ColumnNumber)
Example: Suppose you have a table of sales data, and you want to retrieve the sales amount for a specific product (e.g., “Widget A”). Use:
=INDEX(SalesAmountRange, MATCH(“Widget A”, ProductRange, 0), 1)
-
- SalesAmountRange: The range containing sales amounts.
- ProductRange: The range containing product names.
- 0 as the third argument ensures an exact match.
- MATCH:
- The MATCH function finds the position of a value within a range.
- Syntax:
- =MATCH(Value, LookupRange, MatchType)
Example: To find the row number where “Widget A” appears in the product list, use:
=MATCH(“Widget A”, ProductRange, 0)
-
- LookupRange: The range where you’re searching for the value.
- 0 as the third argument indicates an exact match.
- Combining INDEX and MATCH:
- By nesting MATCH inside INDEX, you can retrieve data dynamically.
For instance, if you want to find the sales amount for a specific product entered in cell J1:
=INDEX(SalesAmountRange, MATCH(J1, ProductRange, 0), 1)
11. INDEX and MATCH (Advanced Usage)
The combination of INDEX and MATCH is a powerful alternative to VLOOKUP. It allows you to search for a value in a table and return a corresponding value from any column, not just the rightmost one. Here’s how it works:
- INDEX:
- The INDEX function retrieves a value from a specified range based on row and column numbers.
- Syntax: =INDEX(Range, RowNumber, ColumnNumber)
Example: Suppose you have a table of sales data, and you want to retrieve the sales amount for a specific product (e.g., “Widget A”). Use:
=INDEX(SalesAmountRange, MATCH(“Widget A”, ProductRange, 0), 1)
-
- SalesAmountRange: The range containing sales amounts.
- ProductRange: The range containing product names.
- 0 as the third argument ensures an exact match.
- MATCH:
- The MATCH function finds the position of a value within a range.
- Syntax: =MATCH(Value, LookupRange, MatchType)
Example: To find the row number where “Widget A” appears in the product list, use:
=MATCH(“Widget A”, ProductRange, 0)
-
- LookupRange: The range where you’re searching for the value.
- 0 as the third argument indicates an exact match.
- Combining INDEX and MATCH:
- By nesting MATCH inside INDEX, you can retrieve data dynamically.
For instance, if you want to find the sales amount for a specific product entered in cell J1:
=INDEX(SalesAmountRange, MATCH(J1, ProductRange, 0), 1)
12. COUNTIF and SUMIF
- COUNTIF:
- Use COUNTIF to count the number of cells that meet a specific condition.
Example: To count how many orders have a quantity greater than 10:
=COUNTIF(QuantityRange, “>10”)
-
- QuantityRange: The range containing order quantities.
- SUMIF:
- SUMIF adds up values based on a condition.
Example: To calculate the total sales for a specific product (e.g., “Widget B”):
=SUMIF(ProductRange, “Widget B”, SalesAmountRange)
13. CONCATENATE (Alternative: Ampersand Operator)
- The CONCATENATE function combines text from different cells.
Alternatively, you can use the ampersand (&) operator:
=A1 & ” ” & B1
-
- This joins the contents of cells A1 and B1 with a space in between.
14. TODAY and NOW
- TODAY:
- Returns the current date.
- Example: =TODAY()
- NOW:
- Returns the current date and time.
- Example: =NOW()
15. IFERROR
- Handle errors gracefully using IFERROR.
Example: To display “N/A” when dividing by zero:
=A1 / B1
=IFERROR(A1 / B1, “N/A”)
Remember, practice and experimentation are key to mastering Excel. As you become more comfortable with these formulas, explore additional functions, pivot tables, and charts. Happy spreadsheeting! 🚀🔍
