Microsoft excel is one of the important functions in any individual’s corporate life which requires working on either computer or laptop. Being such an important function, employers expect from candidates to have basic knowledge of the Microsoft excel.
Basics of Microsoft excel also includes proper know-how of some basic formulas, which serve as the first phase in any kind of data analysis. It is extremely important for the beginners to have knowledge of these excel formulas to even make their lives easier on digital world.
How to incorporate Basic Excel Formulas?
Before directly jumping to the excel formulas, let us first get into how to incorporate these formulas in excel. There are multiple ways to incorporate them and are mentioned below:
- Insert the FORMULA directly by using (Addition sign [+], Subtraction sign [-], Multiplication sign [*] and Division [/])
- Insert the FUNCTION directly by using “=” (for example; =sum[range], =average[range] etc.)
- Insert function through Excel tab; namely Formulas, then clicking to Insert Function and then clicking to the formula to be used
- Insert function using Formulas tab and clicking on AutoSum, Recently Used, More Functions etc. (Use below picture for reference)
List of prominent and basic excel Formulas for Beginners
1. SUM
The function is used for simple addition of multiple numbers. It is the very first function of excel to know and the function can be used in 2 ways:
a) Selection of overall range together after incorporating sum formula [=sum(B2:B8)] and then clicking enter
b) Individual selection of overall range after incorporating sum formula [=sum(B2,B3,B4,B5,B6,B7,B8)] and then clicking enter
2. SUMIF
The function is used to sum the filtered data. If we have a large data belonging to different categories then the function will give the sum of different categories.
For example; if a company is selling 2 products and wants to check each products sale; they can do so by using SUMIF function.
=SUMIF(range,criteria,[sum_range])
=SUMIF($A$1:$B$9,A13,$B$1:$B$9)
Similarly, we can also use SUMIFS if Categories are more than one (1). For example; if we need to find the sum of Product A and Product B individually sold to different geographies, we can use SUMIFS function to get the results.
3. SUMPRODUCT:
SUMPRODUCT function is used for adding various ranges which derived through multiplication of two numbers.
For example; if you need to find total marks of the overall class through addition of marks coming in different subjects.
=SUMPRODUCT(array1,array2)
=SUMPRODUCT(B2:B8,C2:C8)
Similarly, SUMPRODUCT function can be used for multiplication of various variables which can be even more than two (2).
4. COUNT:
COUNT function is used for counting the numbers of items in the selected cells. It is most common and easy function to use in excel.
=COUNT(range)
=COUNT(B2:B8)
5. COUNTA:
COUNTA function is used for also counting the cells with error in formula.
=COUNTA(range)
=COUNTA(B2:B8)
6. COUNTIF:
The function is used to count the selected data with criterias. If we have a large data belonging to different categories then the function will give the count of different categories.
For example; if a company is selling 2 products and wants to check each products sales count; they can do so by using COUNTIF function.
COUNTIFS is another function which can be used for counting selected data with more than one criteria.
=COUNTIF(range,criteria)
=COUNTIF($A$1:$B$9,A13)
7. AVERAGE:
AVERAGE function calculates average value of the data selected. The function can also be used for calculation of Mean of the given data.
=AVERAGE(range)
=AVERAGE(C2:C8)
8. AVERAGEIF:
AVERAGEIF function is used for calculation of average of filtered data with specific criterias. This works similar to SUMIF and COUNTIF function. And, you can also use AVERAGEIFS function similar to SUMIFS and COUNTIFS.
=AVERAGEIF(range,criteria,[average_range])
=AVERAGEIF($A$1:$B$9,A13,$B$1:$B$9)
9. IF:
IF function is a very useful function in the overall excel. From simple to complex problems can be solved using IF function. This acts as a tester function for two scenarios, IF TRUE or IF FALSE. If you need to logically test a number or a text through a formula and check the results in two different scenarios (if the logical test if true and if the logical test is false), then IF serves as the best function to check the same.
For example; If in a class total marks allocated to students are 700 and student who scores more than 300 passes the exam and if not, then he fails.
=IF([logical_test],[value_if_true],[value_if_false])
=IF(B9>300,”PASS”,”FAIL”)
10. MAX and MIN:
These are the most easy functions in excel. They help in finding the maximum and minimum number respectively from a large data base.
=MAX(range)
=MAX(B2:B8)
=MIN(range)
=MIN(B2:B8)
