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:

1. Insert the FORMULA directly by using (Addition sign [+], Subtraction sign [-], Multiplication sign [*] and Division [/])
2. Insert the FUNCTION directly by using “=” (for example; =sum[range], =average[range] etc.)
3. Insert function through Excel tab; namely Formulas, then clicking to Insert Function and then clicking to the formula to be used
4. 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)

Comparing two columns in excel is on of the most basic and important step of data analysis. There could be two cases while we are comparing the data points in a column, as you can see in table of content below –

To find the columns which have same value in a row

To check if both the columns have same value in a row, we can apply a simple “=” to check. If the values are same, it will return “TRUE” else it will return “FALSE”.

Suppose we are comparing column A and B in Row 1, the exact syntax would be –

=A1=B1

For example, view the video below –

To find the values of one column which are also present in the other column

If you want to compare two columns and check if values in one column exists in the another column and vice versa you can check with following ways:

1. Using MATCH formula

MATCH formula is used to determine if a particular value is present in an array. It returns the row number in which the value is present. The exact syntax is as given below –

=MATCH(lookup value, lookup array, [match type])

• Lookup value- the value you want to look up
• Lookup array- The array where you want to find
• Match Type- Exact or Approximate (0 is used for exact match)

To compare the two columns using MATCH formula, we will select a particular value in Column 1 as ‘Lookup Value’ and find out whether it exists in Column 2, which will be selected as ‘Lookup Array’ in this case.

This would return us with the Row number in the Column 2 where the value exists. If it doesn’t exist, then it would return an error.

For example, view the video below –

2. Using Conditional Formatting

You can also use conditional formatting to determine if the two columns have same values or not as demonstrated in the video below.

Often times, while dealing with data we get duplicate values. These duplicate values can lead to double counting in data and hence give wrong results. This makes it important to remove the duplicates to get the desirable results.

To remove duplicates, select the Array where you want to remove duplicates and then press ALT + A + M

For further steps, completely read the article till the end.

Basic Rule : Suppose there is an array which has duplicate entries in Rows 1,2, and 5. While removing duplicates, excel will just keep the first instance of the duplicate, that is Row 1.

Removing Rows with completely duplicate entries

Consider the example below, where Row 2 and 3 are exactly identical i.e. Name, City and Age, all are similar in these two Rows.

If you wish to remove the rows which are completely duplicate i.e. they have the same entries under all columns (Name, City, Age) then you can simply press ALT + A + M and tick mark all the columns of the dialogue box that appears, as shown below

After you click OK, the Excel will keep only the Row with first instance of the entry and remove all the other Rows, which are completely duplicate to it, as shown below.

Removing Rows with duplicate entries under certain columns

Consider the example below, where Name and Age in Row 3 and 5 are identical. However, the City column in these two Rows are different.

If you wish to remove the rows in which certain values are duplicate i.e. they have the same entries under specific columns, then you can simply tick mark those columns in the dialogue box which you want to consider for removing such duplicate rows .

In this example, we want to consider only duplicate entries under ‘Name’ and ‘Age’, as shown below.

After you click OK, the Excel will keep only the Row with first instance of the duplicate entries and remove all the other Rows, as shown below.

We hope that you found this article informative.

In this article, we will learn about basic Logical Functions of MS Excel, namely IF, OR and AND. We will also learn how to use them in combinations such as IF with AND & IF with OR

Logical functions are the functions that check for a given condition. If the value satisfies the condition, the logic will display results according to the condition.

IF Function

The first function is IF function. If the value/ formula satisfies a particular condition it will give results as per condition satisfaction.

Syntax of IF function

IF(logical_test, [value_if_true], [value_if_false]) wherein

• logical_test: The condition that need to be evaluated

• value_if_true: The value that you want to print if the condition is satisfied

• value_if_false: The value that you want to be returned if the condition is not satisfied

For Example –

AND Function

The second function is AND. AND function returns “TRUE” or “FALSE”. If all the conditions in the formula is satisfied, it will return “TRUE” else it will return “FALSE”.

Syntax of AND function

AND(logical1, [logical2], …) wherein

• logical1: The first condition that you want to check that can be either TRUE or FALSE

• logical2, logical3 …: Additional conditions that you want to check that can be either TRUE or FALSE. You can check maximum of 255 conditions

For Example –

OR Function

The Third function is OR. OR function returns “TRUE” or “FALSE”. If one of the conditions in the formula is satisfied, it will return “TRUE” else it will return “FALSE”.

Syntax of OR function

OR(logical1, [logical2], …) wherein

• logical1: The first condition that you want to check that can be either TRUE or FALSE

• logical2, logical3 …: Additional conditions that you want to check that can be either TRUE or FALSE. You can check maximum of 255 conditions

For Example –

Usage of IF with AND

IF AND combination is used when there are multiple conditions which we want to test and if all the conditions are true it gives us the result as per condition satisfaction

Syntax of IF-AND Combination

IF(AND(logical1, [logical2], …), [value_if_true], [value_if_false]) 

For Example –

Usage of IF with OR

IF OR combination is used when there are multiple conditions which we want to test and if one of the conditions are true it gives us the result as per condition satisfaction

Syntax of IF-OR Combination

IF(OR(logical1, [logical2], …), [value_if_true], [value_if_false]) 

For Example –

Mean, median, and mode are different measures to determine average in a numerical data set. They each try to summarize a dataset with a single number to represent a “typical” data point from the dataset.

Mean

The “average” number is found by adding all data points and dividing by the number of data points.

Example: The mean of 9, 5, and 1 is (9+5+1)/3 = 15/3 =5

How to find Mean using Excel?

The mean of a dataset in Excel can be found it by applying the formula “Average” to the data set. Also if you want to calculate the mean quickly you can just select the range. In the bottom right corner, you can find average of the array.

For example:

Median

Median also; The middle number is found by ordering all data points in ascending order and picking out the one in the middle (or if there are two middle numbers, taking the mean of those two numbers).

Example: The median of 9, 5, and 1 is 5 because, when the numbers are put in order (1,5, 9) the number 5 is in middle.

How to find Median Using excel?

The median of a dataset in Excel can be found it by applying the formula “Median” to the data set.

For Example:

Mode

Mode is the number which occurs most frequently in data set    

Example: The mode of (4, 4, 2, 4, 3, 2 ,2 ,2) is 2 because it occurs four times, which is more than any other number.

How to find Mode using Excel?

The mode of a dataset in Excel can be found it by applying the formula “Mode” to the data set.

For example:

Standard Deviation

Standard deviation is a measure of the amount of variation or dispersion of a set of values. A low standard deviation indicates that the values tend to be close to the mean of the set, while a high standard deviation indicates that the values are spread out over a wider range.

How to find standard deviation using excel?

The median of a dataset in Excel can be found it by applying the formula “STDEV” to the data set.

For example:

We hope that you liked this article and found it informative.