Excel Formulas You Must Know : Top 10 Excel Formulas You Must Know


Computer learning is where there is so much to learn. Especially young people have many things to learn. Excel is used for the compilation of many data and calculations. Excel formulas make calculating numbers and making sense of large amounts of data simple.

You can perform various operations in Excel by knowing some important formulas given here. That will increase your productivity and reduce the risk of making calculation mistakes. Here are 10 Excel formulas you must know.

There are a lot of complicated formulas out there, but a good formula doesn’t have to be complicated. In fact some simple formulas are very useful and can help you maximize Excel capabilities.

If you use Excel to organize information, here are 10 Excel formulas you should know, including some simple formulas that have nothing to do with data analysis.

Concatenate : Syntax: =CONCATENATE(string1, [string2, …]) Concatenate allows you to combine the values ​​of several cells into one. It is one of the most powerful formulas for conducting data analysis. Commonly used to combine text, dates and numbers. Combining components of a URL or address lines are two typical examples of what a formula is used for. Example: You have someone’s first name and last name. Now we want to merge this information into the same cell, but typing it manually would take a lot of time. Here we can use Concatenate Excel function. The main challenge is that when we do this there is no space between the two strings (names). What we do to combat this is we can simply add another argument: “” (two double quotes around a space). Make sure the three arguments are separated by commas.

Average : Syntax: =AVERAGE(value1, [value2, …]) This is another advanced Excel formula that is very simple. It basically calculates the average of specific numbers for you. Type the formula and select the cells you want to calculate the average. You can also type cells or a range of individual cells. AVERAGE returns the average of the combined value arguments; That is, the sum of the values ​​in the value arguments divided by the number of such values. Any text encountered in the value arguments is ignored. To treat text values ​​as 0 values, use AVERAGEA.

SUMIF : Syntax: =SUMIF(range, criteria, [sum_range]) This advanced Excel formula is essential in the world of data analytics. The formula adds the values ​​in the cells that meet the selected number.

IF : Syntax: =IF(logical_expression, value_if_true, value_if_false) This advanced Excel formula returns one value if the logical expression is ‘true’ and another value if it is ‘false’. The IF statement helps to use the advanced Excel IF function to create a new field based on these conditions in an already existing track.

TRIM : Syntax: =TRIM(text) This is one of the most used formulas in Excel that cleans up any unnecessary space in fields. This advanced Excel formula basically removes leading, trailing and repeating spaces in text. Using TRIM is important when text is used in formulas or data validation because spaces before or after the text are significant. For example you can clearly see that cell ‘A3’ has a lot of unnecessary spaces in between. Analysts often need to clean up data before using it to work with data, and the Trim function lets you do that easily.

Len : Syntax: =LEN(text) This advanced Excel function basically returns the length of a given string. For example we are counting the number of characters in full names.

Proper : Syntax: =PROPER(text_to_capitalize) This advanced Excel formula is used to capitalize the first letter of each word in a specified string.

Substitute : Syntax: =SUBSTITUTE(search_text, search_for, replace_with, [number_of_occurrences]) This Excel function is used to replace existing text with new text in a string. For example we replaced letter ‘a’ with letter ‘z’ in each name. We can also set the number of occurrences to the formula. By default all occurrences of search_for are replaced; However if occurrence_number is specified only the instance specified for search_will be replaced.

COUNTIF & COUNTIFS : Syntax: =COUNTIF(range, criteria) This Excel formula is used to return a conditional count across a range. In this case, we want to check how many people have the last name “Web”. The formula returns the exact value ‘4’. Syntax: =COUNTIFS(criterion_range1, criterion1, [criterion_range2, criterion2, …]) This advanced Excel formula is used if there are multiple criteria in the range you want to check. In this case we wanted to check how many people were named “Duke Peterson”. The formula returns the exact value ‘2’.

MINIFS/MAXIFS : Syntax: =MINIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …) This advanced Excel formula returns the minimum value in a range of cells filtered by a set of criteria. Syntax: =MAXIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …) This formula returns the maximum value in a range of cells, filtered by a set of criteria.

Leave A Reply

Your email address will not be published.