Top 5 must-know Excel Formulas for beginners
Free guide to master MS-Excel
Getting started with MS-Excel
Microsoft Excel is a tool that is used in almost all industries from hospitality to social media to data analytics and mastering this tool can help you become more efficient and can save you hours of your work. In this beginner’s guide, we walk you through the 5 must-know excel functions and formulas. So let’s begin.
Inserting a formula
In MS-Excel you can insert formulas in two ways – by using your mouse or by using your keyboard. In the first method, all you have to do is navigate to the functions menu wherein you can find all the formulas categorized under different heads like logical, financial, text, and so on.
All you have to do is navigate through this menu and select the formula of your choice and the same will be inserted in the desired sheet. On the other hand, you can use the most popular way of inserting a formula i.e. a keyboard. To enter a formula in a cell you’ve to select a cell and enter an equals sign ”=”, this sign in excel is used to represent the beginning of a formula and then you can enter your desired formula. Excel has an inbuilt feature that will show you all the relevant formulas beginning with the character entered by you.
For example, if you type “=S” you’ll get a dropdown containing SUM, SUMIF, SUMIFS, SUMPRODUCT, and so on.
While some formulas can have a shortcut, we recommend you check out our Must-know Excel shortcut list to learn more about it. Now that we know how to insert a formula let’s explore the Top 5 Must know excel formulas –
As the name suggests when used it will give you the sum of all the values in the selected range. This is widely used and works only with numbers. The syntax for the SUM function is
=SUM( number 1, number 2, number “n”)
If you wish to make the sum feature dynamic, you can select cells instead of entering individual numbers and excel will fetch the data from the mentioned cells. Take a look at the syntax:
The same can also be entered as:
=SUM(C2:C5) – this will give you the same result as the previous example.
The IF function is a logical function. This is used to test a particular condition and then further process the value or display a custom result. For eg – If the salesperson has done sales over 100 units she/he is eligible for a bonus. So our condition will be if sales are greater than 100 then show final value as Eligible for a bonus. The syntax will be
=IF(value to test, value if true, value if false)
In our case, it will be
=IF(Sales>100, “Eligible for bonus”, “No bonus”)
IF function can be used with other functions to perform further calculations as well as can be used to show some custom value too. IF function can also have another IF function inside it and a combination of such functions is called nested IF. Average Most frequently used Excel function is Average and so it has found a place in our list of excel formulas for beginners.
The average function is used to calculate the aggregate of the given numbers or cells. The syntax for the average function is:
You can also use the same function with different cells:
In both, the cases values in respective cells will be added and then divided by the total number of observations.
The count is a unique function, it helps a user to count the number of values contained in a selected range of cells. The COUNT function counts only numeric data and ignores other kinds of data. This function is usually deployed to either check the number of times any numeric value has appeared in the given range of data. The syntax for the COUNT function is:
5. Maximum and Minimum
These are two different functions that help us identify the extreme values in a given range of data.
The Maximum function finds out the maximum value in the given set of data while the Minimum function identifies the lowest value in a given set of data. These functions only work with numeric values. The syntax for the Maximum function is:
While the syntax for the Minimum function is:
Also do remember, excel formulas and functions can be entered in any case i.e. in upper case, small case, or sentence case. While if you wish to test a specific text condition it is better to use the same case as the desired result. These were the excel formulas that a beginner needs to know. For more interesting articles on excel do check out our Excel Resources, you can also check our Learner’s resource for all other articles. If you are keen to learn excel from scratch check out our workshops.