Reading time: 5 mins
Top 15 Microsoft Excel Formulas
Excel workbooks are useful to us all, whether you’re a student, a business owner, or a data analyst. Excel offers a number of functions that can be performed with formulas. It is helpful to learn all the Microsoft Excel formulas, as they can save you a lot of time and energy. Let’s get started!
Top Microsoft Excel Formulas – Let’s begin with the easiest!
- Math time!
Doing basic math is easy with Excel, and you don’t even need fancy formulas. Simply use the basic operators such as +, -, * and /
Select cell C2, type =A2+A3, and press Enter.
Select cell C3, type =A2-A3, and press Enter.
Select cell C4, type =A2*A3, then press Enter.
Select cell C5, type =A2/A3, then press Enter.
P.S. A formula always begins with an equals sign (=).
Select the cell where you want your answer to be. In this case, it is cell B7. Enter =SUM(B2:B6) after selecting the range with the mouse. This will sum the values in cells B2, B3, B4, B5, and B6. Your answer should be 270.
The next step will be to try AutoSum. Select cell B7 and go to Formulas > AutoSum, and then select SUM. The formula will be automatically entered by Excel. Then press Enter to confirm. With AutoSum, you can perform most of the common calculations.
With the Average function, you can calculate the average of the selected cell values. You can find the average by typing =AVERAGE (B2, B3, B4, B5, B6)
Here, your answer will be 54.
Count calculates the total number of cells that contain a number in the selected range.
From the above example, simply type =COUNT(B2:B6) to count the number of cells. Your answer will be 5.
You count the number cells with texts or any other format, type =COUNTA()
To count Blank cells in a range of cells, simply type =COUNTBLANK()
To get the smallest number in a range of cells, simply type =MIN(B2:B6). Thus, your answer will be 27.
To get the smallest number in a range of cells, simply type =MAX(B2:B6). Thus, your answer will be 89.
With the help of the POWER function, you can find out the answer to a number raised to a certain power.
For example, to find out the answer to 10 raised to 4, simply type =POWER(base, exponent). In this case it will be =POWER(A4, 4)
The Concatenate function merges two strings of text into one. Simply type =CONCATENATE(A3&” “&B3) and voila! Your texts are merged.
The NOW function in Excel displays the current time and date on the system. Simply type =NOW() and you get the date and time. Easy right!
Similar to the NOW function, the TODAY function in Excel provides the current system date. Simply type =TODAY()
- DATE, MONTH, YEAR
If you want a specific Day, Month or Year, Simply use the following functions to get the desired results –
You can use =DAY(TODAY()) to find out a specific day from the date value.
and use =MONTH(TODAY()) to return the Month from a date value.
Use =YEAR(TODAY()) to return the Year from a date value.
- IF Formula
An IF function checks a condition and returns a value if it is true. If the condition is false, another value will be returned.
We are checking whether the value in cell B2 is less than 70 in the example above. The function will return “Yes” if it is less than 70; otherwise, it will return “No.” The answer is Yes.
There is no doubt that VLOOKUP is one of the most widely used functions in Excel, as well as one of the most beloved by users. Known as the “vertical lookup,” it is used to search for a value in the leftmost column of a table. When VLOOKUP finds a match, it returns information in another column to the right based on the value in the left column.
To find a particular value, simply type =VLOOKUP(B2,B2:B6,1,FALSE)
Here, we are trying to find out how much bread we have. So B2 has the lookup value, B2: B7 is the range you want to find from, 1 is the number of columns to the right you want to get the value of, and FALSE is the reference value.
Similar to the VLOOKUP function, HLOOKUP stands for “horizontal lookup.” It gives the value in the same column from a row you specify.
To find a particular value, simply type =HLOOKUP(B2,B2:D2,1,FALSE)
Here, we are trying to find out how much bread we had in the year 2020. So B2 has the lookup value, B2: D2 is the range you want to find from, 1 is the number of columns to the right you want to get the value of, and FALSE is the reference value.
There are many tools available for data analytics and data storage, but Microsoft Excel stands out as one of the best. Use these Microsoft Excel formulas and functions to easily navigate your way and perform tasks in a much more efficient and less time consuming way.
Want to know more tips, tricks, and shortcuts? Well, click here to learn more!