In this Excel tutorial, we highlight the Top 12 Most Important Excel Functions and Features that can help most users in getting the basics right, and improve productivity and efficiency drastically.
12 Most Important Excel Functions and Features YOU MUST LEARN & MASTER
Learn the most important formulas & features of Microsoft Excel. I have listed them below for your learning pleasure.
Must-Know Excel Functions for Productivity & Career Enhancement
- Average,
- Sum,
- Count,
- Countif,
- CountA,
- CountBlank,
- Sumif,
- Concatenate,
- Proper,
- Round,
- Vlookup, and
- Creating Dropdown lists in Microsoft Excel.
With a sound knowledge of these 12 Most Important Functions and Features of Excel, you will be on your way to Excel Mastery. You have to take one step at a time.
Learn one Excel function and master it. You come closer to victory with each step, each new function, each new formula learned. Some of these are basic Excel Formulas and Functions than are the easiest to master.
Using the AVERAGE Function in Excel
The Average Function is pretty easy to use.
It adds all the numbers in the provided range, and then divides the grand total by the count of the numbers provided.
You can only Average Numeric Values.
Syntax of Average Function in Excel:
=AVERAGE(number1, number2, number3)
=AVERAGE(Range_of_Cells) like =AVERAGE(A1:A10)
When do you use the AVERAGE Function in Excel:
Managers and business owners often ask executive about the Average Sales, or Average Revenue, or Average Salary, or Average Number of Tickets Resolved, or Average Time Spent on Calls.
All such measures can be easily calculated with the Average Function, which is pre-built into Excel, and is one of the most popular excel functions.
Using the SUM Function in Excel
The Sum Function adds all the numbers in the provided range, and then provides the Grand Total in the resulting cell.
You can provide numbers, refer to multiple cells, or select a range of cells to sum the numbers easily.
The SUM function only works on Numeric Values. It is considered the most basic excel formulas and functions.
If you provide a non numeric value (text value), it will result in a #VALUE error to be displayed in the Grand Total cell.
Syntax of Sum Function in Excel:
=SUM(number1, number2, number3)
=SUM(Range_of_Cells) like =SUM(A1:A10)
When do you use the SUM Function in Excel:
Managers and business owners often ask their staff about the Total Sales, or Total Revenue, or Total Salary for the company or department, or Total Number of Tickets Resolved, or Total Time Spent on Calls, or Total Expenses.
All such measures can be easily calculated with the SUM Excel Function, which is pre-built into Excel. This function is the most common, and most popular excel functions.
This function can be invoked from the Home Tab, by clicking the Sigma Icon. If the grand total cell is next to a range of data, it will auto populate the SUM formula, without writing anything. This is a handy trick that you should practice and get used to.
It will help you a lot to get the Grand Total, and is one of the most important function in Excel, and the most commonly used function in Excel too.
If you want to summarize data in Excel easily, check out this detailed article and video tutorial.
Using the COUNT Function in Excel
The COUNT Function counts all the numeric numbers in the provided range, and then provides the Total Count in the resulting cell.
You can provide numbers, refer to multiple cells, or select a range of cells to Count the numbers easily.
The COUNT function only works on Numeric Values.
If you provide a non numeric value (text value), it will NOT BE COUNTED in the final count.
Syntax of COUNT Function in Excel:
=COUNT(number1, number2, number3)
=COUNT(Range_of_Cells) like =COUNT(A1:A10)
When do you use the COUNT Function in Excel:
It is common in the management, and executives to count the number of rows of data.
For example, managers often want to know the total number of Headcount, the total number of transactions, or Total Number of Tickets Resolved.
All such measures can be easily calculated with the COUNT Function, which is pre-built into Excel. This function is the most common.
This function can be invoked from the Home Tab, by clicking the drop down arrow of the Sigma Icon.
If the grand count cell is next to a range of data, it will auto populate the COUNT formula, without writing anything.
This is a handy trick that you should practice and get used to for counting the number of rows or transactions easily in Excel.
It will help you a lot to get the Total Count, and is one of the most important function in Excel, and one of the most commonly used function in Excel too.
SUM, COUNT, AVERAGE are the basic Excel formulas and functions that everyone should know at the bare minimum. But there are several more important functions in Excel also.
Using the COUNTIF Function in Excel
The COUNTIF function is amazing. It can conditionally count values, based on a criteria. This is one of the most popular logical functions in Excel.
This COUNTIF function can count the number of people in a particular department, count the number of products in a particular category, count the number of values that exactly match a particular value to name a few.
Countif can be also used to count non numeric values. This is a Big Plus for people wanting to count text values, based on a criteria.
Syntax of COUNTIF Function in Excel:
=COUNTIF(numbers in a range, criteria)
=COUNTIF(A1:A100, “=Oranges”) will count the number of rows in cells A1 through A100, which contain the word “Oranges” exactly.
=COUNTIF(A1:A100, “>5000”) will count the number of rows in cells A1 through A100, which contains values that are greater than 5000.
Interestingly, you can use this to count values that are greater than or lesser than a particular value too.
You can use Greater Than (>), Lesser Than (<), Greater Than or Equal To (>=), Lesser Than or Equal To (<=), and Not Equal To (<>) signs in the COUNTIF function.
Check out the Multiple Ways to Count in Excel.
When do you use the COUNTIF Function in Excel:
If your client wants to know how many big orders, above 10,000 were received today, or how many products of the Electronics category were sold, you could use the COUNTIF Excel function easily.
=COUNTIF(A1:A100, “>10000”)
=COUNTIF(A1:A100, “Electronics”)
=COUNTIF(A1:A100, 500) shows the number of entries where the value equates to 500.
Additional Resources: How To Count Cells With a Certain Value in Excel
This is one of the several logical functions in Excel. Other logical functions in Excel include IF, SUMIF, AVERAGEIF etc.
Some people consider COUNTIF to be an advanced function in Excel. Nonetheless, it is extremely useful, and I consider it to be an essential Excel formula to know and use.
Using the COUNTA Function in Excel
The COUNTA Function counts all non blank values in the provided range, and then provides the Total Count in the resulting cell.
You can provide numbers, text, refer to multiple cells, or select a range of cells to Count the non blank cells easily.
The COUNTA function works on Numeric & Non-Numeric Values. Thus, it can count Text value cells too.
The A in COUNTA actually stands for Alphanumeric values. So that’s why it can count text as well as numbers.
If you provide a non numeric value (text value), it CAN BE EASILY COUNTED in the final count.
Syntax of COUNTA Function in Excel:
=COUNTA(number1, number2, number3)
=COUNTA(Range_of_Cells) like =COUNTA(A1:A10)
When do you use the COUNTA Function in Excel:
It is common in the management, and executives to count the number of rows of data. If the data being counted is numeric, you use COUNT, but if you are counting names, or departments, or Products or Categories etc., it is better to use COUNTA, as it counts text values too.
For example, managers often want to know the total number of Employees, the total number of Customers, or Total Number of Tickets Resolved.
All such measures can be easily calculated with the COUNTA Function, which is pre-built into Excel.
This function not known to many people. Learning to use this function well will improve your productivity and efficiency in Excel.
Additional Resources: Counting Text Values in Excel
Using the COUNTBLANK Function in Excel
The COUNTBLANK Function counts all the blank values in the provided range, and then provides the Total Count in the resulting cell.
You can provide cells, refer to multiple cells, or select a range of cells to Count the Blanks easily.
The COUNTBLANK function works on Any kind of cells.
If you provide a range of Numeric or Non-Numeric cells to the COUNTBLANK function, then these will not be counted.
This function only counts empty cells.
CAUTION: A common issue with the COUNTBLANK function is that if a cell contains a space, it does NOT get counted.
This may make the CountBlank total not seem to total with the visible blanks. But this is because it is difficult to see a space in a cell.
Syntax of COUNTBLANK Function in Excel:
=COUNTBLANK(cell1, cell2, cell3)
=COUNTBLANK(Range_of_Cells) like =COUNTBLANK(A1:A10)
When do you use the COUNTBLANK Function in Excel:
You may be wondering why you may want to count blanks. But this efficient function definitely has its uses.
For example, office managers often want to know the total number of Products without a price (price is blank), count the number of Products that didn’t get a looked up value from a VLOOKUP operation, or count how many employees don’t have a valid cost center code.
All such things can be easily counted with the COUNTBLANK Function, which is pre-built into Excel.
The COUNTBLANK function is a life saver when checking counts and it helps to find the illusive missing or empty value.
It will help you a lot to get the Total Empty or Blank Count, and is one of the 12 most important function in Excel, and one that is not so commonly known.
Using the SUMIF Function in Excel
The SUMIF function is amazing. It can conditionally SUM values, based on a criteria. SUMIF is another one of the most popular logical functions in Excel, along with COUNTIF.
This SUMIF function can SUM the total Salary in a particular department, SUM the total Sales in a particular category, Sum the amounts that exactly match a particular value to name a few.
SUMIF can be also used to sum based on a Criteria that is a non numeric value.
This is a Big Plus for people wanting to SUM Numbers, based on a text value criteria.
Syntax of SUMIF Function in Excel:
=SUMIF(range, criteria, [sum_range])
In this formula, the criteria is the second argument. If the range meets the criteria, and the range is a numeric range, it can be summed up directly.
But if the range is a Text based Range, and it matches the criteria, you can provide a third, optional argument, which would then sum up the corresponding values from the sum range, where the range matches the criteria.
=SUMIF(A1:A100, “>1000”) will sum all the number cells A1 through A100, which contain a value that is greater than 1000.
=SUMIF(A1:A100, “>5000”, B1:B100) will sum the number in cells B1 through B100, which contains a values that are greater than 5000 in the cell range A1 through A100.
You can use Greater Than (>), Lesser Than (<), Greater Than or Equal To (>=), Lesser Than or Equal To (<=), and Not Equal To (<>) signs in the SUMIF function.
=SUMIF(A1:A100, 1000) will sum all the amounts that are exactly equal to One Thousand in the range A1 through A100.
When do you use the SUMIF Function in Excel:
If your client wants to know the total sales for a particular category, like total sales amount in the Electronics category, you could use the SUMIF function easily.
=SUMIF(A1:A100, “Electronics”, B1:B100)
=SUMIF(A1:A100, “=1000”) will give the total of the values that are exactly 1000 in cells A1:A100.
Once you learn COUNTIF & SUMIF, you know two of the most important excel Formulas and Functions. These are the basic Excel formulas and functions that everyone should know.
Using the Concatenate Function in Excel
Combining Text, or Concatenating Text is frequently required.
No doubt that the CONCATENATE function, now also called as the CONCAT function is one of the Top 12 Most important functions in Excel.
The concatenate function helps you to combine two or more items into a single string.
The most common use is to use it for combining people’s first and last names.
SYNTAX of CONCATENATE Function in Excel
CONCATENATE(text1, text2, text3…)
For example, if you want to concatenate and combine two string, you get this
=CONCATENATE(“Hello”, “World”)
Result: HelloWorldAs you notice, there is no space between the two words.
This is because concatenate does not add any additional spaces in the combined text. Nor does it remove any spaces.
So if you want to add a space between the two strings, you will have to hard code the space in the concatenate function.
=CONCATENATE(“Hello”, ” “, “World”)
Result: Hello World
If the values to be combined are in separate columns, you can also use the Concatenate function
=CONCATENATE(A2, ” “, B2)
Result: Mickey MouseNow you can drag the formula or copy and paste it into the cells below, to get all the other names combined.
Note: Concatenate is best suited for combining Text Values. If you use it to combine numbers, you will get strange results.
=CONCATENATE(5, 6)
Result: 56
Concatenate can be used to combine separate Day, Month, and Year to resemble a date too.
=CONCATENATE(5, “-“, “Dec”, “-“, 2024)
Result: 5-Dec-2024
Using the PROPER Function in Excel
It is often required to display names of people, Categories, Departments to appear nicely in Excel, with the first letter of each word appearing in a Capital Letter.
While we have Excel functions like UPPER() & LOWER(), which can convert every letter to uppercase or lowercase, they are not so handy.
Because customers often want to see only the first letter capitalized, and the remaining letters to be in small letters.
Syntax of PROPER Function in Excel:
=PROPER(text_value)
=PROPER(“mickey mouse”) would result in Mickey Mouse. Notice that we supplied the “mickey mouse” in lower case. But the returned value had the first letter of each word capitalized.
When do you use the PROPER Function in Excel:
Whenever you want to capitalize the first letter of each word, you can use Proper function in Excel instantly.
I consider this to be one of the most useful and important functions of Excel.
Additional Resources: Learn the Text Functions in Excel
Using the ROUND Function in Excel
Whenever you divide two numbers, the resulting answer can be a large fraction. For example, dividing 100 by 3 gives us 33.333333333.
But business users may not want to use so many decimal points. Thus, the ROUND function comes in handy.
It can be used to round any fraction to any number of decimal points.
Syntax of ROUND Function in Excel:
=ROUND(number1, number_of_digitits_to_round_of)
=ROUND(33.33333333, 2) will result in 33.33 being the answer.
=ROUND(33.333333, 1) will result in 33.3 being the answer.
=ROUND(33.333333, 0) will result in 33 being the answer.
When do you use the ROUND Function in Excel:
The business managers, finance managers often ask to present the numbers rounded. For example, they may ask the Profit margin to be displayed in 2 decimal points.
Sometimes, a business user may want to round the values up or down, based on the business case.
So ROUNDUP(100/3, 2) will result in 33.34
And ROUNDDOWN(100/3, 2) will result in 33.33.
With multiple Rounding functions available, you can fulfill every user’s needs and requirement around Rounding.
So ROUND, ROUNDUP or ROUNDDOWN, and help each and every user with one of the most useful Excel functions.
Using the Vlookup Function in Excel
VLOOKUP is considered one of the Most Useful Functions of Excel. It is used to benchmark the skills of interviews often.
If you know VLOOKUP and can use it well, with its optional settings, then you are considered an Advanced User or an Experienced User of Excel.
Heck, some people also consider you to be an Excel Guru if you have mastered the VLOOKUP Function.
Learning and Using VLOOKUP is quite easy.
The use of this function in almost every corporate spreadsheet, and its amazing use cases have raised it to the Top of the Most Important Excel Functions of all times.
However, don’t think that it is difficult to use or that it is complex.
Let’s understand what is VLOOKUP first, and then we understand its syntax and usage.
VLOOKUP is used to lookup any value in a table, and pick a corresponding value from the other columns of the matching row.
Syntax of VLOOKUP Function in Excel:
=VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])
=VLOOKUP(“James Bond”, employee_Table, 2) will find the second column of the employee_table, where the first column of the employee_table is set to “James Bond”.
In this example, we maybe looking for the department or salary, or employee code of the employee named James bond. Depending on the table, if the second column after the employee name is Department, we get the department.
If the third column is Salary, and we need the salary, then we can modify this formula slightly.
=VLOOKUP(“James Bond”, employee_Table, 3) will find the third column of the employee_table, where the first column of the employee_table is set to “James Bond”. Now we can get the Salary of James Bond.
When do you use the VLOOKUP Function in Excel:
Whenever you want to lookup one value, and pick another corresponding value from the lookup table, then the VLOOKUP function will be the most appropriate.
Managers and business users often use the VLOOKUP function to lookup product cost, employee salary, product attributes from the Product Master table, or the employee Master Tables.
The interesting thing is that the lookup table can be on another sheet in the same Excel file, or it could be in a completely different Excel file.
The Looked up table can come from another file in another folder or directory.
This makes it very powerful, and one of the most powerful and useful functions of Excel.
Once you master these most important functions of Excel, you will improve your level of expertise in Excel, and with it you will also raise your competency, and understanding of Microsoft Excel
Creating Dropdown lists in Microsoft Excel
To avoid data entry errors, and to provide a basic level of data validation, it is often desired that users don’t key in arbitrary values, which are difficult to debug, could be full of spelling mistakes, and a single typo can make the Lookup on that value fail.
Thus, business users often want the data to be of a good quality. One simple way to improve data quality is to avoid typing in values in cells, and instead pick the correct value from a drop down list.
For example, you could display a drop down list of Customers, Products, Countries, Departments, Cost centers, Accounting GL Codes, to name a few. This way,
When do you use the DROPDOWN Data Validation Feature of Excel:
Whenever you want to limit user entry to only a few select values, it is appropriate to setup a dropdown data validation list. It is fairly easy to setup. In fact, Microsoft already provides you with a few standard list, like the Days (Monday, Tuesday, etc.), Month Names, Areas (North, South etc.).
And the good thing is that you can always define your own custom list in Excel. I have a detailed article on how to setup a Drop Down list in Excel. Check it out!
There you have it. A roundup of the most essential excel formulas in Excel. Consider them the Most important Excel Formulas and Functions to Master first.
Conclusion: Top 12 Most Important Functions of Excel
The tips, tricks and all the 12 Most Important Excel Functions and Features work in every version of Microsoft Excel.
These are the list of most important excel functions that every professional or analyst must know.
Knowledge of these must-know excel functions for productivity and career boost is mandatory for every professional and Excel enthusiast.
Additional Resources & Video Tutorials from ExcelChamp Vinai Prakash
- VLOOKUP DETAILED VIDEO:
• VLOOKUP With Exact Match Type: Most I… - IF Function DETAILED VIDEO: • How To Use IF & IFS in Excel To Find …
- Top 25 Excel Tips & Tricks
- Excel For Beginners – A simple, step by step Video Tutorial
- How To Freeze Top Rows and Columns in Excel
- Lookup Data from multiple sheets, without writing VLOOKUP or any other formulas
- Find & Remove Duplicates in Excel Easily
- How to Subtract a Date from Today’s Date in Excel
Subscribe to our channel for more Excel Tips & Tricks
These tips work in every version of Microsoft Excel. Just give it a try, and if it helps you, remember to give it a Thumbs Up, and Like the Video.
