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.
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.
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.
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.
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.
=IF(E1>10, "Over", "10 or Under")
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".
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".
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.
=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.
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.
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.