10 Useful Formulas in Microsoft Excel

Microsoft Excel is a powerhouse tool that can help you manage, analyze, and visualize data. This tutorial aims to introduce you to 10 useful Excel formulas that can help you work more efficiently.

1. SUM: Adding Values

=SUM(A1:A5)

The SUM function is used to add numerical values in a range of cells. For example, =SUM(A1:A5) adds all values in cells A1 through A5.

2. AVERAGE: Calculating the Average

=AVERAGE(B1:B5)

The AVERAGE function calculates the average (arithmetic mean) of its arguments. For instance, =AVERAGE(B1:B5) calculates the average of values in cells B1 through B5.

3. COUNT: Counting Entries

=COUNT(C1:C5)

The COUNT function counts the number of cells that contain numbers within a given range. For example, =COUNT(C1:C5) counts the number of cells in the range C1 through C5 that have numbers.

4. MAX/MIN: Finding Maximum/Minimum Values

=MAX(D1:D5)

The MAX and MIN functions return the maximum and minimum value of a given set, respectively. For instance, =MAX(D1:D5) returns the highest value in the range D1 through D5, while =MIN(D1:D5) gives the lowest.

5. IF: Conditional Logic

=IF(E1>10, "Over", "10 or Under")

The IF function performs a logical test and returns one value for a 'true' result and another for a 'false' one. For example, =IF(E1>10, "Over", "10 or Under") tests if E1 is greater than 10. If true, it returns "Over"; if not, it returns "10 or Under".

6. CONCATENATE/CONCAT: Combining Text

=CONCATENATE("Hello", "World")

=CONCAT("Hello", "World")

CONCATENATE or CONCAT (in Excel 2016 and later) combines two or more text strings into one. For instance, =CONCATENATE("Hello", "World") or =CONCAT("Hello", "World") returns "HelloWorld".

7. LEFT/RIGHT/MID: Extracting Substrings

=LEFT(F1,5)

These functions extract a specific number of characters from a text string. LEFT extracts from the start, RIGHT from the end, and MID from a specified point. For example, =LEFT(F1,5) extracts the first 5 characters from cell F1.

8. VLOOKUP: Vertical Lookup

=VLOOKUP("Apple", A1:B5, 2, FALSE)

VLOOKUP is used to find things in a table or a range by row. For example, =VLOOKUP("Apple", A1:B5, 2, FALSE) searches for "Apple" in the first column of the range A1:B5 and returns the value in the same row from the second column.

9. COUNTIF: Conditional Counting

=COUNTIF(A1:A5, ">10")

The COUNTIF function counts the number of cells within a range that meet a single condition. For instance, =COUNTIF(A1:A5, ">10") counts how many cells in the range A1:A5 contain a number greater than 10.

10. SUMIF: Conditional Sum

=SUMIF(B1:B5, ">20")

The SUMIF function sums the values in a range that meet criteria that you specify. For example, =SUMIF(B1:B5, ">20") sums only the numbers in B1 through B5 that are greater than 20.

By mastering these formulas, you'll make your Excel tasks quicker, easier, and more efficient.