Top 30 Excel formulas you should know in 2023

Republished By Plato

Followers: 0

Introduction

Welcome to the world of Excel, where numbers come to life and calculations become a breeze! Whether you’re a seasoned Excel user or just starting out, knowing a few key formulas can make your life so much easier. In this blog, we’re going to explore some essential excel formulas that will take your analtyical skills to the next level.

In this blog, we’ll walk you through some of these essential formulas step by step. We’ll provide excel tips and tricks along the way to make your learning experience interactive and engaging. By the end, you’ll have a solid foundation in Excel formulas and be equipped with the skills to tackle any data challenge that comes your way.

So, let’s dive in and unleash the full power of Excel formulas together. Get ready to impress your colleagues, supercharge your productivity, and become an Excel wizard in no time!

Check out this Excel course to upskill yourself with the basics of excel.

Also Read: Top 6 Free Online Excel Courses With Certificate

Excel formulas List

Here’s a list of some commonly used Excel formulas that can greatly enhance your data analysis and manipulation abilities:

SUM: Adds up a range of numbers.
AVERAGE: Calculates the average of a range of numbers.
COUNT: Counts the number of cells that contain numbers.
MAX: Finds the highest value in a range of numbers.
MIN: Finds the lowest value in a range of numbers.
IF: Performs a logical test and returns different values based on the result.
VLOOKUP: Searches for a value in the first column of a table and returns a corresponding value from another column.
HLOOKUP: Works like VLOOKUP but searches horizontally.
CONCATENATE: Joins the contents of multiple cells into one cell.
LEFT: Extracts a specified number of characters from the beginning of a text string.
RIGHT: Extracts a specified number of characters from the end of a text string.
LEN: Calculates the number of characters in a text string.
TODAY: Returns the current date.
NOW: Returns the current date and time.
COUNTIF: Counts the number of cells that meet a specific condition.
SUMIF: Adds up the cells that meet a specific condition.
AVERAGEIF: Calculates the average of the cells that meet a specific condition.
SUMIFS: Adds up the cells that meet multiple conditions.
AVERAGEIFS: Calculates the average of the cells that meet multiple conditions.
DATE: Creates a date based on the specified year, month, and day.

Excel Formulas and Functions

In Microsoft Excel, there are a large number of formulas and functions that depend on the type of its use. The formulas and functions are very useful and save us time in performing those calculations manually. Functions make us lives easier to perform calculations without doing any manual calculations. For instance, we used a formula in the upper section to add values of the cells A1, A2, and A3 manually by placing a + sign between them. And if we use the function to add them, we just need to use the =SUM() function, and inside the parentheses, we need to incorporate the cell numbers only. We can give a range of cells, or we can also enter specific cells inside the parentheses. It will work the same for both cases and return the result of the addition of those values.

In this section, we will see some mostly used and common formulas and functions for mathematical operations, conditional calculations, time-based calculations, and other important functions. If you are someone who already has some basic knowledge about Excel but wishes to learn more, you can take up the Excel Intermediate Level course or free online excel courses with certificate and gain a comprehensive understanding of Excel.

Let us see some important formulas and functions in Microsoft Excel:

1. Multiplication

Performing multiplication operations in Microsoft Excel is very easy. But to do that, we need to create a formula. As we just discussed, we need to add an equal sign before we write any formula, therefore you need to add an equal sign first and the remaining formula after that. For instance, we can use the ‘*’ operator to perform multiplication between values of any two or more cells.

For e.g.

= A1*B1

In the above example, A1 and B1 are two cells and the ‘*’ operator is used to perform multiplication among them. To use the Multiplication Function, we need to use the PRODUCT function.

For e.g.

= PRODUCT(A1,A2)

In the above example, we used the PRODUCT function after an equal sign that will perform multiplication between the cell values incorporated inside parentheses.

2. Division

To do this task, we need to use the forward-slash (/) in our formula. After using the equal sign, we need to use two cell references or values in our formula bar in Microsoft Excel. After giving the first value or cell reference, you need to add ‘/’ (forward slash) in order to perform a division between those two values.

E.g.

=A1/B1 or =8/4 will give 2 as a result.

In the above example, the formula will divide the value of the A1 Cell by the value of cell B1. It should be noted that there’s no DIVIDE function in Microsoft Excel.

3. SUM

The name of the function is self-defining as it will add the values provided or the range of cell values and return the Sum of those values. To perform this function, you can add individual cell references, values, range of cells, or a mix of these three.

The syntax of the SUM function in Excel is as follows:

“=SUM(num-1, num-2, ……, num-n)”

Where num-1 and num-2 are the first and second numbers that we want to be added and it can take up to n times such that num-n where n can be any positive number. The first argument num-1 is mandatory and the rest arguments are optional.

For e.g.

=SUM(A2:A10)

The above example will return the addition of cell values from A2 to A10 where SUM is a function.

Also Read: Excel Tips and Tricks to Take Your Career Ahead

4. Subtraction

To perform subtraction, we can directly enter the values or use cell references. We need to use the ‘-‘ sign between two or more values or cell references. The syntax for the Subtraction formula in excel is as follows:

=number1-number2

The equal sign is always mandatory whether you declare any function or formula, you always need to use the equal sign first. After that number 1 is the number from which you want to subtract any value. And number 2 is the value that you want to subtract from the first number. In this case, the values of both numbers are mandatory.

Let us see an example for directly entering the values for subtraction:

For e.g.

= 10-7

Here the ‘-‘ operator will subtract 7 from 10 and return the result as 3. To use cell references for subtraction, see the following example:

=A3-D3

Where the values of D3 will be subtracted from the value of A3 and the result will be returned to the desired cell where we put this formula.

5. IF

The IF function in Excel is one of the most useful and most popular functions. It allows you to make logical comparisons between a value and the expected value. So the IF statement can have two results such as True or False. True is when the comparison is true and False when the comparison is False.

Let us see the syntax to use this Function properly:

=IF(logical_condition, statement_if_true, statement_if_false)

In the above syntax, the logical statement is mandatory and it is that statement that we need to check in our worksheet or table. The second argument is the return statement that we want if the condition is true and this statement is mandatory. However, the last statement for the false condition is not mandatory.

For e.g.

=IF(C2=”Yes”,1,2)

Where IF (C2 = Yes, then return a 1, otherwise return a 2). So, if the value of cell C2 is Yes and it matches, it will return 1, else it will return 2.

Also Read: Microsoft Excel Interview Questions

6. DATE

The DATE function in Excel can be used when we need to take three separate values and combine them to make a date. The DATE function returns the sequential serial number just like a particular date.

For e.g.

=DATE(year, month, day)

In the above example, all three parameters such as Year, Month, and Day are mandatory and required. You need to provide cell references in place of Year, Month, and Day inside the parentheses. It will take values from those cells and return them in the form of a date.

7. Array

Array formulas are very useful and powerful that enable us to perform complex calculations which often can’t be done in simple worksheet functions. In Excel, there are two types of Array formulas such as the formulas that perform various calculations to generate a single value in the result and some which require an array of values as an argument.

For e.g. =SUMIF(A2:A20,”>5”)

In the above example, the SUMIF function will find the numbers in the range A2:A20 which are larger than 5 and will return the sum of those numbers in the desired cell.

13. TRIM

The TRIM function removes all spaces from the text except the single spaces between words. The TRIM function is very useful when we have copied any text from other applications and want to remove irregular spacing from the text. The TRIM function makes it easier to remove irregular spacing from the text.

The syntax of the TRIM function is as follows:

=TRIM(“ any string is provided here”)

In the above syntax, as we can see the unwanted spaces are there in the string which need to be removed and to do that, we can use the TRIM function that will remove the unwanted spaces from the string and make our text in that string more clear.

For e.g. = TRIM(“ First Quarter Sales “)

The above example will remove the unwanted space from the text and give the results as “First Quarter Sales”. We can specify the Cell reference or a range of cells from which we want to remove irregular spaces.

14. LEFT, MID, and RIGHT

The LEFT() function is used to find the characters of a string from the left or start of a text string. The MID() function is used to get the character from the middle of a text string. Lastly, the RIGHT() function will give the result of characters from the end of a text string. To better understand these functions, you should carefully look at these examples:

For e.g.

=LEFT(‘Apple’,3)

The above example will return ‘App’ for 3 characters that we specified in our formula as 3. We can provide the cell references or range of cells in place of the text string.

=MID(‘Apple’, 2, 2)

The MID function will return ‘pp’. As the first argument 2 is taken to select the character and the second argument takes the next 2 characters from the text string and returns the result.

=RIGHT(‘Apple’, 3)

For the RIGHT function, it takes 3 characters from the end of the string and returns as a result. For the above formula, it will return ‘ple’ as a result.

15. VLOOKUP

VLOOKUP is a function that helps you to find things in a table or a range of cells by row. For example, when we need to find if a value exists in our table or not, then we use the VLOOKUP function. The VLOOKUP function takes various arguments such as if we are finding any value or if we want to return a corresponding value to that value we need to find in the table. So here, we will look at some examples to better understand this function.

e.g.1

=VLOOKUP(B3, B2:E7,2, FALSE)

In the above example, VLOOKUP finds the value of the B3 cell in the table ranging from B2:E7 and gives the corresponding value of B2 in the 2^nd column, lastly FALSE is used to give the exact match of the value we are looking for.

e.g.2

=VLOOKUP(102,A2:D6,2,FALSE)

The 2^nd example shows that the VLOOKUP function is searching for 102 within the table ranging from A2:D6 and will return the value from the 2^nd column in the range if it finds an exact match in the table (False function is used).

e.g.3

= IF(VLOOKUP(102,A1:E6,2,FALSE)=”Sousan”, “Location”, “Not found”)

In the above example, the LOOKUP function is used with the condition using the IF statement where the value ‘Sousan’ in the 2^nd column is corresponding to the value 102 or not. If it is found at that location, it will return Located, otherwise, Not found.

Let us understand the VLOOKUP function in a very simple language. The budget of home supplies in the sheet has a serial number column that uniquely identifies any specific item in the budget. And suppose if you have that serial number of an item and you want to know the item description inside the table. At that time you can use the VLOOKUP function.

16. RANDOMIZE

This function is used to return the random real numbers which are greater than or equal to 0 and less than 1. Whenever the sheet is calculated, it will return a new random number each time. The function RAND() is very useful when we need to find any random roll number of students or any other information in our worksheet.

For e.g.

=RAND()

This function will return a random number between 0 and 1.

If we use =RAND()*100, it will return a number between 0 and 100. For recalculation in the worksheet or to get a new random number in our function, the F9 function key is used. It will run the function again returning a new value as a result for the specified range.

17. INDEX-MATCH

This function is very useful to find a value in a column to the left. When we use VLOOKUP and get stuck while returning an appraisal from a column towards the right, you can use the INDEX-MATCH function instead of VLOOKUP which will consider the lookup column and the return column.

For e.g.

=INDEX(A1:D11, MATCH(“America”, B1:B11,0),4)

In this example, we are looking for America in our table ranging from B1:B11 and suppose it is found in row 4 using the MATCH function. Then, INDEX will lookup for the argument and find the corresponding value in the 4^th column as we used 4 as the last argument in the formula. So, it will return the value corresponding to America in that specific column. You should give it a try and find how it can be used effectively.

18. HLOOKUP

This function searches for a value in the top row of the table or range of cells that we provided. It returns the value in the same column from the row that we specified in the table. The HLOOKUP() function is similar to LOOKUP(). The difference is just that in the HLOOKUP function, it searches for the value horizontally while in the VLOOKUP function, it searches for the value Vertically.

The syntax of HLOOKUP function is as follows:

=HLOOKUP(lookup_value, array_table, index_no_of_row, [lookup_range])

Here in the syntax above, we can clearly see the arguments where all the arguments except lookup_range are mandatory and need to be given inside the parentheses. The lookup value is the value that we are looking for in the cells. The array table is the table of information in which our data is looked up. And index_no_of_row is the row number in the table array in which we are trying to find the lookup value.

For e.g. =HLOOKUP(“Axles”, A1:C4, 2, TRUE)

The above example looks for ‘Axles’ in row 1 of the table and returns the value from row 2 which is in the same column of Axles.

19. DATEDIF

This function returns the difference between two dates after calculating the difference based on days, months, or years. The Function becomes very useful when calculating the Age of people in a table.

The syntax for the DATEDIF function is as follows:

=DATEDIF(start_date, end_date, unit)

In the above syntax, the arguments are mandatory where the start_date is the first date of a given period and may be entered as text strings inside quotation marks. End_date is the last or end date to calculate the number of days, months, or years between these two dates. The last Unit is used to get the result in a specific manner such as if we want to calculate only the years between two dates, we use “Y” as a unit or if we want to calculate the month’s difference between these dates, we need to use “M” for units. The same can be used for Days as “D”. Any combination of these can also be used as a single unit.

Let us see an example below to better understand the concept of the DATEDIF function.

For e.g. =DATEDIF(A2, B2, “Y”)

Here in the example above cell A2 gives the starting date whereas B2 gives the end date and Y is the unit in which we want our result to be. Such that “Y” is used for Years, “M” can be used for Months, and “D” is used for Days in that period.

20. TIME()

The TIME function returns the decimal number for any specific time. The result of this function formats the cell as a date even if the cell format was General before entering the function.

The syntax for the TIME() function is as follows:

=TIME(hour, minute, second)

In the above syntax, all the three arguments are required where the numerical values for all three arguments vary from 0 to 32767 based on the time.

For e.g.

=TIME(A3,B3,C3)

The TIME function will combine the cells in the format of TIME as Hours, Minutes, and Seconds. And the result will be a decimal number based on the time.

21. TODAY()

When we need to use the current date in our worksheets, we can use the TODAY() function. It returns the serial number of the Current date where the serial number is the code for date-time used by Microsoft Excel for calculations of date and time. TODAY() function is also helpful to find the intervals. Let us see an example of the TODAY() function.

For e.g.

=TODAY()

The function will return the current date in the format MM/DD/YEAR

Another example can also be as follows:

=TODAY() + 5

In this example, 5 days are added to the current date such as if the date of today is 4/18/2022, then 5 days will be added to this date and the resulting answer will be 4/23/2022.

22. SUBSTITUTE

This function is used to replace any existing text with a specific text in a text string. The SUBSTITUTE function takes two arguments, first is the text or cell reference with which we want to Substitute characters, second is the Old text that you want to replace and lastly New text that you want to replace the old text with.

Let us see the syntax for using SUBSTITUTE function below:

=SUBSTITUTE(text,old_text,new_text,[instance])

In the above syntax, all the arguments except instance are mandatory where text is the cell reference in which we want to change the text, old text is the text which we want to be changed with the new text that we specified in the next argument. And lastly, the instance is to replace the text.

For e.g.

= SUBSTITUTE(A2, “SALE”, “DISCOUNT”)

In the example, the text “SALE” in cell A2 will be replaced with the text “DISCOUNT”.

23. REPLACE

The REPLACE function replaces a part of a text string with a different string based on the number of characters that we specify with that string.

The syntax for REPLACE function is as follows:

=REPLACE(old_text, start_num, num_of_chars, new_text)

In the above syntax, the old text is the text which we want to be replaced, we can also specify the cell reference over there. After that, the second argument takes the number of starting locations in the text and the third argument is the number of characters from the starting number of the text and lastly, the new text that we want to be replaced in place of that text from starting number up to the number of characters of next arguments.

For e.g.

=REPLACE(A3,6,5,”*”)

In the above example, the function replaces five characters in the string from A3 Cell from the 6^th character with a single ‘*’ and returns the result.

24. CONCATENATE

The CONCATENATE() function is used to join or merge several text strings into a single text string.

The syntax for this function is as follows:

=CONCATENATE(text1, text2,…)

Where text1 and text2 are the arguments of two strings that we want to join or merge into a single string. We can also use more than two arguments or strings to join all of them in a single string.

There are some different ways to perform this function described with some examples below:

e.g1.

=CONCATENATE(A12, “ ”, B12)

The example above will concatenate two strings from cells A12 and B12 and give the joined string as a result.

e.g2.

=CONCATENATE(A12&” “&B12)

The above example is another method to join two strings and give the new merged string as a result.

25. CEILING

The CEILING function is very useful in that it returns rounded up numbers to the nearest multiple of significance. For instance, if we want to avoid decimal numbers in our table and take the round off of the numbers, we use the CEILING function that rounds the number to the relevant multiple of significance. When using the CEILING function, it rounds the number away from zero.

The syntax for the function CEILING is as follows:

=CEILING(number, significance)

In the above syntax, the argument number is that number which we want to be rounded and the significance is multiple to use when rounding. See the example below for a better understanding of the function.

For e.g.

=CEILING(2.5,1)

The above example rounds 2.5 for the nearest multiple of 1 which is 3.

26. FLOOR

The FLOOR function rounds the number down, from zero to the nearest multiple of significance. Let us better understand the use of the FLOOR function with the help of an example below:

For e.g.

=FLOOR(3.4,2)

In the above example, the FLOOR function rounds the number 3.4 down towards zero to the nearest multiple of 2 which is also 2. So, it will give 2 as a result.

27. POWER

The Power function returns the result of exponentiation of a number to a certain power. For example, you need to find the result of 5 raised to the power of 2, then you need to use the Power function and give these two arguments that will return the square of 5 i.e. 25. Let us see an example below:

For e.g.

=POWER(5,2)

The above example will return a result of 25 as the power 2 of 5 is 25. We can find the power of any number whether it is in fraction, it always gives an accurate result when we use the POWER function.

28. MODULUS

The MODULUS function returns the remainder of a number after division. The resulting number has the same sign as the divisor. It becomes very useful when we need to find the remainder of any number after division. Let us see an example below:

For e.g.

=MOD(5,2)

The MOD function will return a value of 1 because when we divide 5 by 2 it will give a remainder of 1 and the positive sign is because the divisor is also positive.

29. LEN

The LEN() function is useful when we need to find the total number of characters in a string. So, the LEN() function counts the number of characters in the string and returns it as a result. It also counts spaces and special characters. The example below shows how the LEN() function can be used.

For e.g.

=LEN(A7)In the above example, the LEN function will count all the characters in the string in Cell A7 and return the total number of characters as a result.

Conclusion

Microsoft Excel is a very useful and powerful application when we need to analyze data and reports for various purposes. The formulas and functions that we discussed in this article are of great importance in our daily lives, whether we want to do some simple calculations or analyze data or reports. The formulas and functions are of great use in our everyday life. In this article, we looked at text, numeric, date-time, and some advanced formulas and functions of Microsoft Excel. As you have seen the usefulness of these formulas and functions that we discussed in this article today, it will help you out whenever you are stuck in any calculations in Microsoft Excel.

The functions and formulas in Excel enable users to perform simple and complex calculations like finding totals for a row or column of numbers etc. These formulas and functions become very useful in more complex situations such as calculating math problems, solving engineering maths, creating financial models, calculating mortgage payments, etc. So, this is all about Microsoft Excel Formulas and functions in this article. Hope you may have learned something new. Looking for some more amazing Excel tips? We have got you covered.

If you found this article helpful, then share it with others too. Also, check out these free excel courses online to get started with Microsoft Excel.

SEO Powered Content & PR Distribution. Get Amplified Today.
PlatoData.Network Vertical Generative Ai. Empower Yourself. Access Here.
PlatoAiStream. Web3 Intelligence. Knowledge Amplified. Access Here.
PlatoESG. Automotive / EVs, Carbon, CleanTech, Energy, Environment, Solar, Waste Management. Access Here.
BlockOffsets. Modernizing Environmental Offset Ownership. Access Here.
Source: https://www.mygreatlearning.com/blog/excel-formulas/

Time Stamp: June 27, 2023

Time Stamp: Dec 26, 2022

Republished By Plato

What are the Documents required for US F1 Visa

A Comprehensive Guide to Reversing a String in Java

Average MBA Salary in USA – 2024

I am convinced that this course stands as a remarkable resource

M1 Visa: A Complete Guide

About Us

Vertical Search & Ai

Platform

Stay Connected

Account