10 Functions Every Excel User Should Know: Improve Working Efficiency
Microsoft Excel has been around for nearly 4 decades and is the go-to tool for working with data.
Most people have used Excel because it is the best spreadsheet editor. Even though a few other spreadsheet software programs have popped up over the years, they are no match for Excel.
It is used extensively in different industries to make reports and understand business information with its many functions and formulas.
In this article, we’ll talk about the top 10 functions every Excel user should know when working with this tool.
So, let’s get started!
1. VLOOKUP to Extract Info From Other Sheets
The first Excel function that every user must know is VLOOKUP. This function looks for data in a table, even if it’s on a different sheet, and gives you a matching value from a different column.
This is useful for combining data from different sheets, such as matching product codes with descriptions and updating one sheet based on another.
Example
Let’s say you have an employee list and a department list:
We need to find the department in which Henry works, for which we will demonstrate how to use the VLOOKUP function.
Step 1
Go to “Formulas > Lookup & Reference” and select “VLOOKUP” from the menu.
Step 2
Enter the details as follows:
● Lookup value: A11 (Type “Henry” in the cell beforehand)
● Table array: A2:E7
● Column index number: 3
● Range lookup: 0
Step 3
When you click “OK,” Henry’s department will be displayed in B11.
Alternatively, you can also enter the function manually to get the results:
=VLOOKUP(A11,A2:E7,3,0)
2. INDEX/MATCH in Excel
INDEX and MATCH are two of Excel’s best tools for finding information. When you use them together, they become even more powerful and help you quickly and accurately find the information needed in a big data set.
In short, INDEX and MATCH combine two functions:
● INDEX points to a specific cell based on row and column numbers.
● MATCH finds the position of a value within a range.
You can use this function in cases where you need to look up information based on multiple criteria and want more control over your lookups, like finding data in a non-standard table format.
Example
We will continue with the previous example of the employee and department list. So, let’s say you want to find the city where John lives:
Step 1
Type “John” in one of the cells, and enter the function:
=INDEX(D2:D7,MATCH(A11,A2:A7,0))
Step 2
When you hit “Enter,” John’s city will be displayed in front of his name in A11.
3. Find and Remove Duplicates in Excel
When working with a large amount of data, there is always a chance of entering duplicate data. However, with Excel’s “Remove Duplicate” function, finding and removing duplicates has become easier than ever.
This feature can identify and eliminate unwanted copies, streamline data, and facilitate analysis.
Example
Let’s say you have entered an employee’s name and department multiple times and want to remove it.
Here’s what to do:
Step 1
Go to “Data” and in the “Data Tools” tab, click on “Remove Duplicates.” This option is indicated by cells with a cross icon.
Step 2
Click on “Select All > OK.”
Step 3
This function will then locate and remove duplicate entries from the list.
To learn more details about how to find duplicates in Excel, you can check this article.
4. SUM Function in Excel
The SUM function is the most used formula in Excel. As the name suggests, this function adds up all the numbers in the selected range of cells.
You can use SUM when you need to total a list of numbers, automate calculations in your spreadsheets, or combine data from multiple cells for reports or analysis.
Example
Let’s take a toy company as an example. It has the data for several toys, along with their quantity and price.
You’ll use the SUM function to find out the total price of all the toys combined. Here’s how:
Step 1
Select all the cells with the total price, click on the sigma symbol Σ, and select SUM from the list.
Step 2
This will give you a total of all the toy prices.
Step 3
Alternatively, you can type “=SUM(D2:D6)” in the Fx space at the top.
5. AVERAGE Function in Excel
Just like the basic SUM function, there’s an AVERAGE function in Excel, too! It calculates the average (or mean) of a group of numbers, giving you a central value.
You can use the AVERAGE function to understand the typical value in a range of numbers or when you need to compare data sets by looking at their average values.
Example
So, if you have the total price of a bunch of toys and want to find out the average, here’s how to go about it:
Step 1
Select the total price of the toys from D2 to D6, click on the sigma icon Σ, and select AVERAGE.
Step 2
You’ll get the average price of all the toys.
Step 3
Get UPDF’s stories in your inbox
Join Medium for free to get updates from this writer.
And if you’re an Excel whiz and want to enter the function manually, use “=AVERAGE(D2:D6)” to get the results.
6. MIN Function in Excel
Are you looking to find the smallest value in your data set? That’s where the MIN function in Excel comes in handy! This formula scans a range of cells and gives you the smallest numeric value, saving you time and making sure your data is accurate.
You can use the MIN function to find the minimum value in a list or set minimum thresholds or goals based on the data.
Example
Let’s take the example of a spreadsheet tracking daily temperatures. The MIN function can instantly tell you the coldest day of the week.
Here are the steps for using the MIN function in Excel:
Step 1
Select the cells with the temperature range, click the sigma symbol in the toolbar, and select MIN.
Step 2
This will instantly give you the minimum temperature during the week.
Step 3
You can enter “=MIN(C3:C9)” in the Fx space at the top for the manual function.
7. MAX Function in Excel
The MAX function in Excel is similar to MIN; however, it finds the highest value in a given data set.
You can use it in instances where you want to find the highest value in a list or need to identify peak performance or maximum limits in your data.
Example
We’ll use the previous example for this function also, but this time we will identify the highest temperature in Chicago.
Let’s use the MAX function to determine the highest temperature during the week:
Step 1
Select the cells with the temperature range, click Σ in the toolbar, and select MAX.
Step 2
This will instantly give you the maximum temperature during the week.
Step 3
If you want to enter the MAX function manually, type “=MAX(C3:C9)” in the Fx space at the top.
8. Flash Fill in Excel
In 2013, Excel introduced a helpful feature called Flash Fill. This function automatically fills your data when it detects a pattern. For example, if you have a list of product numbers and you only need the numbers after a certain point, Flash Fill can easily help you with that.
This function comes in handy for combining data from separate columns and creating consistent formats for data. It also helps you save time and avoid errors from manual data entry.
Example
In this example, we will demonstrate how to enter email addresses ending with “gmail.com” in your Excel sheet using Flash Fill.
Step 1
Enable “Flash Fill” by going to “File > Options > Advanced” and selecting the “Automatically Flash Fill” option.
Step 2
Start typing the email addresses in the cells. After you type 10 email addresses that end with “gmail.com,” the Flash Fill function will detect the pattern, and Excel will automatically fill the cells for you.
9. Find and Replace in Excel
If you have data in which you want to modify some information or fix an error, the “Find and Replace” function can help! It lets you quickly locate and swap text, numbers, or even formatting, saving you time and effort.
You can use it to fix typos or update outdated information across multiple sheets.
Example
Let’s say you have a list of employees and their respective departments:
However, Henry resigns and is replaced by Liam Hemsworth. In this case, you’ll have to update the data in Excel.
Step 1
Open the Excel sheet, click on “Find & Select” in the “Home” tab, and select “Replace” from the options.
Step 2
We want to replace Henry with Liam, so type “Henry” in “Find What” and “Liam” in “Replace With.”
Step 3
Click “Replace” or “Replace All” in case of multiple changes, after which your data will be updated without any manual effort.
10. Save Excel to Other Formats
There may be instances where you might need to share your data with others who don’t have Excel or prefer a different format. That’s where the “Save As” feature comes in with its variety of file types!
This allows you to save your Excel sheet in a format compatible with other programs or for specific uses, including PDFs, CSV, XLSX, and XML, to name a few.
Example
If you have updated the employee list in Excel and want to save it in PDF format for printing or CSV format to create a file with comma-separated values, here’s what to do:
Step 1
Go to “File > Save As” and select the location where you want to save the file.
Alt Text: select file location
Step 2
Now, name the file and select the format in which you want to save it. For instance:
● If you’re looking to import simple data into other applications, such as databases, we recommend using the CSV (Comma-separated values) format.
● To share plain text data without formatting, use TXT format.
● And to create a static document that preserves layout and formatting for easy sharing and viewing, go for PDF format.
PDF is the most common format in which users save their Excel files.
However, while Excel’s “Save As” can handle individual files, if you need to convert a large number of files or want to perform batch conversions, we recommend using a PDF converter like UPDF.
What is UPDF?
UPDF is an AI-powered PDF converter, editor, annotator, and reader. It helps automate the Excel to PDF conversion process, saving you time and effort, especially when dealing with large batches of files.
You can also edit a PDF with this tool using its OCR capabilities. The best part is that UPDF is compatible with Windows, Mac, iOS, and Android, so whether you’re using a PC or working on your mobile, UPDF has your back!
Conclusion
Microsoft Excel is used by approximately 750 million people worldwide, and 63% of companies, big and small, use it for their day-to-day accounting operations.
Even though we have been using this amazing tool for decades, most of us are only aware of a handful of its functions. But did you know that it has over 400 functions?
Therefore, to make it easy for you to use and understand Excel, we have rounded up 10 functions every Excel user must know.
So, improve your work efficiency! Work smarter, not harder, with MS Excel and its valuable functions!
