12 Excel functions everyone should know about

Key Takeaways

• Excel functions like SUM and AVERAGE simplify data analysis tasks, ensuring accurate results effortlessly.
• Functions like CONCAT and REPLACE help merge or change text strings, enhancing text data management in Excel.
• Advanced functions like VLOOKUP and INDEX & MATCH offer flexibility for precise data extraction in Excel sheets.

Microsoft Excel is a go-to solution for professionals and businesses to crunch numbers and create insightful charts, data analysis, and data visualization. If you are new to Excel, the software’s endless menu and formulas can initially seem overwhelming. In this post, we will walk you through some of the must-know Excel functions to make tackling your everyday Excel tasks a breeze.

Whether you are managing monthly expenses, planning a vacation, calculating your class performance, or organizing data, the functions below will help you juggle all tasks with better efficiency.

While they are closely related, you need to understand the difference between functions and formulas in Excel. A formula starts with an equal sign (=), while a function represents its intended use (e.g. SUM, COUNT, etc.). Basically, functions are specialized tools, while a formula includes a broader expression to achieve desired outcomes.

12 SUM, AVERAGE, MIN, and MAX

We’ll begin with simple functions and gradually progress to more complex ones.

SUM

As the name suggests, the SUM function adds selected cells. Basically, you can type any cell range such as =SUM(B2:B5) and it will calculate the values from B2 to B5, displaying the result on B6.

AVERAGE

This is a must-have function for educators. Whether you want to calculate a student’s final result (grade) or your average monthly expenses, use the AVERAGE function. You can either select a range of cells or define specific ones.

=AVERAGE(B2:B5)

=AVERAGE(B2,B5,C7)

The former calculates the average of B2 to B5, while the latter calculates the average of the values in cells B2, B5, and C7.

MIN and MAX

As the name suggests, the MIN function displays the minimum value in a defined cell range. MAX is completely the opposite and shows the maximum value from the range of cells.

=MIN(D2:D6)

=MIN(B2,B5,C7)

=MAX(D2:D6)

11 SUMIF

The SUMIF function lets you total values in a cell range that meet specific criterion you set. It’s handy for conditional calculations and summarizing data with specific filters. Here’s an example of how the function looks.

=SUMIF(range, criteria, [sum range])

Let’s understand it with an example. Let’s say you have a list of expenses, including items and their costs. Now, you want to calculate the total expenses for a specific item: your total fuel costs.

=SUMIF(A2:A10,”fuel”,B2:B10)

This will sum the values in the range B2:B10 only where the corresponding cells in the range A2:A10 are equal to “fuel” in this example.

10 SUBTOTAL

In many ways, SUBTOTAL function may sound like SUM on paper. But there are major differences. SUBTOTAL function is especially useful when you have filtered rows in your data as it automatically excludes those from the calculation. You can choose from various options within the function, such as average, count, sum, minimum, maximum, and more, depending on your needs. Let’s glance over some common values which use numeric shorthand here in Excel.

• 1 means AVERAGE
• 2 means COUNT
• 3 means COUNTA
• 9 means SUM
• 10 means PRODUCT

=SUBTOTAL(9,D2:D80)

The 9 means SUM, so this will compute the total sales amounts in the range D2 to D80, but exclusively for the rows that remain visible after filtering. It’s quite a powerful function and opens up a host of possibilities for manipulating data in Excel.

9 DATE & TIME

Do you frequently create spreadsheets that involve date and time? Excel offers several handy functions that help you work with dates and times, such as year, month, hour, minute, and second. Let’s go over some examples.

• =TODAY() returns the current date based on your system settings
• =NOW() returns the current date and time
• =DATE(2024,8,24) returns 2024-08-24
• =TIME(12,20,40) returns 12:20:40

You can also extract year, month, and day from a specific cell. For example, if cell C3 contains 2024-08-24, then use the functions below.

• =YEAR(C3) displays 2024
• =MONTH(C3) shows 8
• =DAY(C3) returns 24

8 MODULUS

MODULUS is quite a straightforward one and comes in handy for researchers and data scientists. The MOD function determines the remainder when one number is divided by another.

=MOD(20,3)

In the example above, the function returns 2 (since 20 divided by 3 is 6 with a remainder of 2). Instead of direct value, you can also refer to cells in your function. For instance, you can use =MOD(A2,3) (which means the same thing when A2 cell is a value of 20).

7 CONCAT

The CONCAT function in Excel is intended to merge or link several text strings into one cohesive text string. You can concatenate a couple of cells, multiple ones, and even numbers and dates. Let’s go over an example shown in the image above.

• Cell A5 contains “First Name”
• Cell B5 contains “Last Name”
• Cell C5 contains “Parth”
• Cell D5 contains “Shah”

=CONCAT(A5,”: “C5,”,”,B5,”: “,D5)

The output will be “First Name: Parth, Last Name: Shah”.

6 CEILING and FLOOR

The CEILING and FLOOR functions in Excel round numbers, but they round in opposite directions. CEILING rounds the number up while FLOOR dials it down. For example, if the final result is 8.7 and has a multiple of 2, it will round up to 10 (nearest multiple of 2). Similarly, the FLOOR function will return 8.7 down to 8.

=CEILING(8.7,2)

=FLOOR(8.7,2)

General users may not find it useful in their workflow. However, CEILING and FLOOR functions are quite helpful in billing, inventory purposes, or controlling the display of numbers in different charts. It helps in rounding measurements for a specific level of precision.

5 TRIM

As the name suggests, the TRIM function simplifies text strings by eliminating extra space. It’s a powerful tool for managing text data in Excel. For example, if cell A10 contains ‘My name is Parth Shah’ with some extra spaces between characters as shown above, you can run =TRIM(A10) and it will return ‘My name is Parth Shah’ with normal spacing.

The TRIM function in Excel effectively removes extra spaces, making text data management easier and improving the accuracy and appearance of your spreadsheets. It’s a must-have function for data cleaning and consistent text formatting in Excel.

4 REPLACE and SUBSTITUTE

As the name suggests, the REPLACE function lets you replace a specific portion of text with something different. The SUBSTITUTE function is also quite similar to REPLACE, but not exactly the same. SUBSTITUTE lets you swap out words or phrases in a block of text with something else and can replace multiple words at a time.

Starting with REPLACE, let’s replace a specific part of a text string within a text string with a new text string. Sounds confusing, right? Allow us to simplify it with an example.

=REPLACE(old_text, start_num, num_chars, new_text)

In the image above, cell A1 contains Chocolate cake. Now, use the function below to just replace Chocolate with Vanilla.

=REPLACE(A1,1,9, “Vanilla”)

The function above will replace the first 9 characters (Chocolate) with the word Vanilla. It doesn’t always need to be a new word entirely either. It could even just be a few letters or numbers as a partial change. Just specify the position and number of characters that need replaced within the cell or cells.

Next, in the example image below, we see A4 contains “I like Dell and Dell laptops are reliable.” Let’s use the SUBSTITUTE function to replace the word Dell with HP (which will apply to both instances of “Dell” unless otherwise specified).

=SUBSTITUTE(A4,”Dell”,”HP”)

You could also use =SUBSTITUTE(A4,”Dell”,”HP”,1) and it will replace only the first Dell reference with HP.

It’s entirely up to your workflow to use REPLACE and SUBSTITUTE functions in Microsoft Excel. They surely come in handy when you want to create adaptable spreadsheets.

3 LEFT, RIGHT, and MID

When dealing with large databases of contacts, LEFT, MID, and RIGHT functions let you extract specific portions from within a cell. You can extract first name, middle name, last name, area codes, product codes, and much more from a large text string. It saves you manual work and gets the job done in no time. Let’s check it out in action with an example.

Let’s say cell A6 contains Michael James Ross, as in the image above. You can now use these functions to extract his first, middle, and last name.

=LEFT(A6,7) returns Michael

=MID(A6,9,5) returns James

=RIGHT(A6,4) returns Ross

As you can see from the example above, when you use the LEFT function, Excel counts characters from the left side (Michael), the RIGHT function counts the characters from the right side (Ross) and with the MID function, you need to enter the starting position (9 from left) and length (5 for James) to extract middle characters.

2 VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are used to extract specific values within a table. VLOOKUP performs vertical lookup, while HLOOKUP does the same horizontally. Let’s understand the function in detail.

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Here’s what each argument means.

• lookup_value: Enter the value you want to find in the table.
• table_array: Enter the range of cells that contain the data in the table, starting with the first column that contains the value you are searching for.
• col_index_num or row_index_num: The column or row number (starting with “1” as the first selected column/row, not using the Excel sheet numbers) from which the function extracts and returns a value to you.
• range_lookup: There are two options here. You can pick one based on your usage. TRUE means it finds an approximate match, while FALSE means it looks for an exact match. If it can’t find an exact match, Excel returns #N/A.

Let’s assume you want to find the price of the product with 98765 ID. The formula is below.

=VLOOKUP(98765, A6:B10,2,FALSE)

It will search for 98765 in the range between A6 to B10. It will return to you the value in the 2nd column searched. When you use the same formula with the HLOOKUP function, it finds the same in the 2nd row searched.

1 INDEX & MATCH

INDEX & MATCH offer more flexibility than VLOOKUP and HLOOKUP. You can use this to look things up in any direction and support multiple conditions. While INDEX returns the value or range in a table, MATCH shows its position in the range. Let’s use them together.

=INDEX(B2:B10,MATCH(“XPS 14”,A2:A10,0))

Let’s say you want to find the price of XPS 14 in a large database. Here, MATCH(“XPS 14”,A2:A10,0) searches for XPS 14 in the range A2 to A10 and shows its position (if it’s in the 7th row, the result is 7). 0 finds the first value that is exactly equal to the lookup value (XPS 14, in our case).

Now, =INDEX(B2:B10,7) shows the price of the product in the 7th row. Excel Pro users prefer this function due to its flexibility, robustness, and efficiency.

Unlock the true power of Excel

While Microsoft Excel boasts a vast array of features, these core functions can help you strip away the complexity and focus on essentials. What are you waiting for? Master these Excel functions and supercharge your spreadsheets in no time.

Apart from these functions, you should also embrace macros in Excel to boost productivity. Check out our dedicated post to learn to automate Excel with macros.