12 most useful functions in Excel

Useful functions In Excel – Part 1

How Ms Office's new version is different from the old one?
Useful functions In Excel

Useful functions In Excel Microsoft Excel is one of the most powerful applications that help to manage data and process them as per the need. If you want to perform the day-to-day tasks without spending a lot of time, then you need to know how to use the formulas. There are 12 most useful functions in Excel, but if you want to work on an advanced level then you may need VBA macros, outlining data, managing pivot tables, etc.

If you want to have a book of 100 tips in Excel, then click on the link and fill in 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 learning the 12 most useful functions in Excel. If you know about these functions, then you will be able to analyze your data most efficiently.

 

Let us check out all the 12 Most Useful functions In Excel one by one

 

Function 1 ( IF ) out of Useful Functions In Excel

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 the word ‘good job’, if the sales of center 1 are more than 300000. Otherwise, the word ‘do well’ is there.

=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)

Sumif

It is an extremely useful Excel function.  

 

3. COUNTIFS Function Out of Useful Functions In Excel

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

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

 

The COUNTIFS function is a function that counts the number of values that meet 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″)

Countif
Useful functions In Excel

Here, 1 thing needs to be remembered 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 helpful as it removes all the spaces from a cell that is not in between 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 into one as 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 first name and last name into a full name. A space is entered for the text2 argument.

=CONCATENATE (A40,” “,B40)

 

Useful functions In Excel
Concatenate

 

Function 5 – Left/Right

The left and right functions are also special functions that help 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 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 which is the Zone code.

=RIGHT (A47,1)

Useful functions In Excel
LTrim and Rtrim

 

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

 

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

Click Here to Buy or Sell Old Laptop