12 Most Useful Functions In Excel

12 Most Useful functions In Excel – Part 1

How Ms Office's new version is different from the old one?

Microsoft Excel is one of the most powerful application that helps to manage data and process them as per the need. If you want to perform day to day task without spending a lot of time, then you need to know using the formulas. There are 12 most useful functions in Excel, but if you want to work on advance level than you may need VBA macros, outlining data, managing pivot tables etc.

If you want to have a book of 100 tips on excel, then click on the link and fill the details.

 

There are more than 475 functions in Excel. It can be overwhelming for anyone when you start with data analysis.

As we have so many functions, then it can be difficult to know what to use for specific Excel tasks. In this blog, we will be knowing the 12 most useful functions in Excel. If you know about these functions, then you will be able to analyze your data in the most efficient manner.

 

Let us check out all the 12 functions one by one

 

 

Function 1 – IF

The IF function is the most useful. This function means we will be able to automate the decision-making in the Excel sheets.

With the help of the IF function, we can get Excel to perform a different calculation or display different values depending on the outcome of a logical condition.

The IF function takes a logical test to perform, what action to happen if the test is true, and the alternative action if the result of the test is false.

 

The syntax of the function would be

=IF (logical test, value if true, value if false)

In the example below, we have displayed the word “good job”, if the sales of center 1 are more than 300000. Otherwise, the word “do well” is displayed.

=IF (D2>300000,”good job”,”do well”)

 

 

Function 2 – SUMIFS

The SUMIFS is the most useful Excel function that makes sums meet specific criteria.

We also have the SUMIF function in Excel, It does the same task except SUMIF can only test one condition, while SUMIFS can test many conditions.

So, the SUMIFS function is a superior function to SUMIF.

The function takes the range of values to sum, and then each range to test and what criteria to test it for.

 

The syntax of the function would be

=SUMIFS (sum range, criteria range 1, criteria 1, …)

 

In this example, we are summing the values in column C for the Store entered into cell E27.

=SUMIFS (C27:C35,B27:B35,E27)

It is an extremely useful Excel function.  

 

Function 3 – COUNTIFS

The COUNTIFS is another function that is very important for data analysis.

It is very much like the SUMIFS function. Same ways there are many other Excel functions like COUNTIFS like AVERAGEIFS, MAXIFS, and MINIFS functions.

 

The COUNTIFS function is a function that counts the number of values that meets the specific criteria.

 

The syntax for the formula would be

=COUNTIFS (criteria range 1, criteria 1, …)

In this example, we count the number of sales from the store entered into cell E27 that have a value of more than 200000.

=COUNTIFS (B27:B35,E27,C27:C35,”>200000″)

Here, 1 thing needs to be remembered that when you use SUMIFS or COUNTIFS functions, then the criteria should be entered as text or a cell reference.

 

 

Function 4 – TRIM

This is one function that can be really helpful as it removes all the spaces from a cell that is not in the between of the words. It removes trailing spaces.

This is something that happens when content is pasted from somewhere else or when users accidentally type spaces at the end of the text.

The spaces are not visible, but will not work properly.

 

The syntax of the formula would be

=TRIM(text)

In this example, the TRIM function is used in a separate column to clean the data.

=TRIM (E27)

 

Function 5 – Concatenate

This is one function that combines values from multiple cells into one. This is one useful function that can help us to combine multiple parts of data intoone like First name and last name.

 

The syntax of the formula would be

=CONCATENATE (text1, text2, text3, …)

In this example, CONCATENATE is used to combine the firstname and lastname into a fullname. A space is entered for the text2 argument.

=CONCATENATE (A40,” “,B40)

 

Concatenate
Concatenate

 

Function 5 – Left/Right

The left and right functions are also special functions that helps in extracting a specific number of characters from the start and end of the text. This is one formula that can be of help when we have to extract parts of an address or a field to use it for further references.

The LEFT and RIGHT functions request the same information. They want to know where the text is and how many characters you want to extract.

 

The syntax of the formula would be

=LEFT (text, num chars)

=RIGHT (text, num chars)

In this example, column A contains a reference that is made up of the State code (first two characters), a transaction ID, and then the Zone code (last character).

The following LEFT function is used to extract the State code.

=LEFT (A47,2)

 

The RIGHT function is used to extract the last character from the cells in column A that is Zone code.

=RIGHT (A47,1)

 
LTrim and Rtrim
LTrim and Rtrim

 

To learn more functions check out the part 2 of this blog. 

 

If you wish to learn Excel and advance Excel, check out the link.