Excel offers a huge variety of features and functions that can improve the ability to perform tasks daily. It does not matter that you are a business owner or an office worker, or any casual Excel user, these 10 Excel functions will definitely benefit your performance on Excel spreadsheets. This article will work like Excel training for you because many Excel tutorials and Excel certification include these functions in their courses.
These functions will be helpful because you can apply for fulfilling many requirements while working on a spreadsheet. Let’s see these 10 Excel functions and proper usage of them in any spreadsheet.
Without further adieu, here is the list of 10 Excel functions everyone should know:
1. TRIM Function
As the name suggests, it is the most basic but important Excel function. Which is used for trimming and removing extra spaces of any cell. It is helpful when you pull any data from the database, then you will see some extra space, so the TRIM function helps remove it. The basic syntax of this excel function is:
Formula: =TRIM(A1)
2. VLOOKUP
VLOOKUP is one of the most famous and best Excel functions because it matches data from the table according to the user. This Excel function looks for specific information from a huge table and copies it to another table. HLOOKUP is similar to it, but there is a slight difference that VLOOKUP searches the information vertically, that means in columns, but HLOOKUP search information horizontally, that means in rows. Many Excel tutorials and Excel certifications include VLOOKUP as the basic and essential process.
Once they find the information, they return the value in the specified column/row to select the table array. Hence, the last parameter finds whether the formula must look for the exact/approximate string. The formula’s Syntax is:
VLOOKUP(Lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP(Lookup_value, table_array, row_index_num, [range_lookup])If you want to use VLOOKUP, add any column you want to display that found data. After that, select the first blank cell of that column, then click on the Insert> Function and type VLOOKUP. After selecting this option, you will see a dialogue for defining four values.
3. SUMIF
SUMIF is used for adding the values when a cell meets the provided criteria, and you can easily specify more than a single condition through it. For example, SUMIF is used for adding up the complete sales for the specific product manufactured by the particular salesperson. The basic syntax of SUMIF is:
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2…)
It is a good Excel function, so it is good to master this basic function to improve your performance.
4. COUNTIF
COUNTIF helps you to count the number of cells in a specific range that meets the given criteria. This works slightly similar to the SUMIG, but it counts the field that can meet the particular criteria rather than summing them. The syntax of COUNTIF is:
COUNTIF (range, criteria)
5. RIGHT, LEFT and MID
RIGHT, LEFT, and MID functions are used for grabbing a specific part from the text. In other words, these Excel functions are used for returning a specified number of different characters from the text strings. All of these functions use the text as a first argument, so Right and Left Excel functions have one more argument for determining the number of characters which requires to be included. MID Excel function uses two more arguments for determining the start point of characters and the number of characters required to be extracted. So, here is a list of syntax:
LEFT(text, [num_chars])
RIGHT(text, [num_chars])
MID(text, start_num, num_chars)6. CONCATENATE
Concatenate is an Excel function that can save your lot of time because it works to join or merge information from different types of cells and makes spreadsheet dynamics. Concatenate is a powerful excel function for financial analysts to perform financial modeling. You can easily use the function, first insert a new column in which you want to collect the data. So here is a syntax of Concatenate
CONCATENATE (cells to be combined)
7. IF Function
IF function is a quick Excel function that determines that any specific data meets the particular criteria. This function is easy to use, and you can check that a statement (true/false) then performs the actions according to the results. The specific syntax of the IF function is:
IF (logical_test, value_if_true, value_if_false).
In some cases, you can use Nested IF that allows you to replace any value_if_false part of the IF syntax with other IF statements, so you can further break down data.
8. INDEX MATCH
INDEX MATCH works as an advanced alternative of the HLOOKUP or VLOOKUP, so it is a great combination of Excel formulas that can help you to improve your financial modeling and analysis performance. VLOOKUP offers a good feature, but it has little limitation because it can only work from left to right. Hence, INDEX MATCH works to lookup values anywhere in the lookup table. The syntax of the INDEX MATCH is
INDEX(Profit column, MATCH(Lookup Value,Product Name column,0))
9. SUM Function
SUM function helps you add two or more numbers. It can be applied to the set of numbers separated through the comma or by selecting specific cells. You can add this function while having Excel training on any platform because you can quickly sum up information through it. SUM functions can add different values in defined arguments, and these arguments can be a single cell, static numerical value, or range. The basic syntax of the SUM function is:
SUM(number1,[number2],…)
For example, if you want to add different values, then use SUM(B2, B3, B4) for adding B2, B3, and B4, as shown in the image below
You can also add the range of cells by pressing ALT and Equal(=) key together on your keyboard. Apart from it, you can use a specific formula as shown in the image below
10. COUNT function
COUNT functions work on counting a number of cells instead of adding them. This function is used to count the number of cells containing any specific characters of the selected range. COUNT function can take up to 255 arguments at a time, and these arguments can be range references, cell references, or numbers. The basic syntax of the COUNT function is
COUNT(cell reference 1, cell reference 2,…)
COUNT(A1:A12)
COUNT(A1:A12, B1:B12)( It is used for counting more than one range)Conclusion
In this article, we have provided complete information about 10 Excel functions everyone should know. We have tried to add up all of those basic functions, which you will get in Excel training certification courses.
These excel functions will help you to grow your knowledge and performance on Excel spreadsheets.