Essential Excel Skills for Data Analyst

Microsoft Excel is such application that every professional swear by whether you are working as Data Analyst, an MIS person or you are preparing for an interview.

A work profile that requires you to deal with data in large or small quantities, Microsoft Excel does a great job of simplifying it and providing it to you in the exact way you need it. An instrumental role in this process is played by the multitude of formulas or functions that Excel offers its users, making their lives infinitely easier.

In this article I bring to you 9 formulas that you must know to make your working with Excel easier.

1. VLOOKUP

Formula:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Do you need to fish out data from a large table to use in a smaller spreadsheet?

In this case, it is quite impossible for you to actually look through the entire table to find out the small bit of information that you need.

The VLOOKUP function comes in extremely handy in such a situation.

Imagine you have a table of Student Names and Test Scores for 100 students in three subjects and you need the data only for 15 students and in one subject. In your new sheet, all you have got is the names of the 15 students. It is completely impractical for you to look for the test scores of these students individually and hence the VLOOKUP function can be used to get the required information on to the new spreadsheet.

If you want tolearn more, check out this...

2. SUM

Formula: 

=SUM(25, 25) or =SUM(A1, B1) or =SUM(A1:B5)

Sum is the easiest and the most widely used function in Excel. It can be used to find out the sum of two numbers, two cell values or even a range of cells. Like we can see in the example above, they can use cell references when separated by a comma and even when you select a range of cells and returns you the sum of the numbers in these cells.

The shortcut of SUM function is Alt++.

If you want to learn more, check out this...

3. COUNT & COUNTA

Formula: 

=COUNT(A1:A10)

=COUNTA(A1:A10)

The COUNT and the COUNTA function are essentially the same. They count the number of cells in a range.

But while the COUNT function returns you the value of the number of cells that have numbers in them, the function of the COUNTA function is a little different. The latter returns the value for the number of non-empty cells in a range.

So, to sum it up, the COUNT function returns the number of cells that have only numerical value whereas the COUNTA function returns the number of cells in a range that are not empty.

If you want tolearn more, check out this...

4. LEN

Formulae: 

=LEN(A1)

The LEN formula is useful when you want to find out the number of characters in a cell. The formula is fairly simple. But what you need to keep in mind is that this counts the space as a character as well. You, therefore, might get incorrect results if your value in the cell has spaces in between.

If you want tolearn more, check out this...

5. TRIM

Formula: 

=TRIM(A1)

The TRIM formula gets rid of spaces in a cell except for the single spaces between words. This formula is especially useful because sometimes there are extra spaces between words or after them. This formula seamlessly gets rid of them making your work much easier as opposed to finding space between words using the CTRL+F (Find and Replace box) shortcut and then replacing the extra spaces with single space.

If you want tolearn more, check out this...

6. RIGHT,LEFT, MID

Formula: 

= RIGHT(A1, number of characters)

=LEFT(A1, number of characters)

=MID(A1, start number, number of characters)

These set of formulae return you a specified range of texts from a text string or a series of numbers.

Ø         The RIGHT formula returns the specified number of characters from the right of the string.

Ø         The LEFT function comes back with a specified number of characters from the left of the string.

Ø         The MID function, however, has a little adjustment. It requires you to specify the number of characters from which you need to trim the value in the cell. It returns you the number of characters starting from the specified value till the number of values you have specified.

If you want to learn more, check out this...

7. IFStatements

Formula:

=IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

The IF statements are another set of the most widely used formulae in Excel. The IF statement returns a value only if the value or the range of values that you look at matches the criteria specified in the IF statement. This is especially handy when you need to take different action on different values based on whether they meet a certain criteria or not.

If you want tolearn more, check out this...

8. SUMIF,COUNTIF, AVERAGEIF

Formula:

=SUMIF(range, criteria, sum_range)

=COUNTIF(range, criteria)

=AVERAGEIF(range, criteria, average_range)

These formulae work the same way as the IF statements – the only difference being that they actually perform a function if they do meet a criteria.

So for any of the formulae, you need to specify the range of values they need to check and then specify the range of values or cell sequences on which they need to perform the function.

For example, if in a spreadsheet for marks in Maths, 10 out of 20 students are found to have the value “Passed” in their ‘Results’ column, the AVERAGEIF function will find the average of their scores only and not for all the 20 pupils. This is especially helpful if you need to find the sum or average or need to count the number of values which meet a particular criteria.

If you want tolearn more, check out this...

9. CONCATENATEor CONCAT

Formula:

 =CONCATENATE(Text1, Text 2, Text 3,….)

= Text & Text 2

The CONCATENATE formula basically brings together two or more values from different cells together in a cell. It can be used in two ways.

Firstly, we can just use the CONCATENATE formula as specified above or

we can simply separate the two values with a “&” sign to bring them together.

If you want to learn more, check out this...

These 9 are just a few of the numerous formulae that Excel allows you to work with. Use these judiciously and your work with Excel becomes infinitely simpler.

Generally these most essential and useful functions are asked in the Interview if you are not hardcore Data Analyst. These functions are also very handy in our day to day life within the organization or in home, if you need.

Hope you have enjoyed ...

Keep reading & Stay blessed ...

MOD Function Reminder of a Division

So far we discussed about how to get the INTEGER portion from a division.

To get the INTEGER portion from a division this, we used QUOTIENT function and INTEGER function.

In this article, we will discuss How to get the reminder part of a division in Excel.

Microsoft Excel provides MOD Function. MOD function is very simple and very easy to use.

Let’s discuss it.

Description

The Microsoft Excel MOD function returns the remainder after a number is divided by a divisor.

Note : The basic mathematics says that if we try to divide any number by 0 (zero), the result will be an indeterminate form.

Therefore, if the divisor is 0, then the MOD function will return the #DIV/0! error.

The MOD function is categorized as a Math/Trig Function and can be entered as part of a formula in a cell of a worksheet, i.e., as nested function.

Syntax

The syntax for the MOD function in Microsoft Excel is,

=MOD(number, divisor)

Parameters or Arguments

number

A numeric value whose remainder you wish to find.

divisor

The number used to divide into the number parameter. If the divisor is 0, then it will return the #DIV/0! error.

Returns

The MOD function returns a numeric value.

If the divisor is 0, then the MOD function will return the #DIV/0! error.

Example :

Suppose we need to get the reminder part of a division, the divisor is 5 and the dividend is 17.

The formula will be =MOD(17,5) or you can give cell reference as the below image shows.

Please look at the below image, which displays how to use MOD function.

How to use MOD Function in Excel

Please note :

1.   QUOTIENT Function is used to get the Integer portion of a division.

2.   Whereas MOD Function is used to get the Reminder part of a division.

3.   The division result without reminder is simply use a ‘/’, without quotes in a formula.

I hope you have enjoyed this small article. Take care…

Thank you for reading…

Integer portion of a division – INT Function – The Problem & Solution

In my last article, I had discussed about Quotient Function in Microsoft Excel to get the integer portion of a division.

But there is another function which can also give the same result if applied, INT function.

But there is differences between these two, therefore, please take caution before applying INT function to get the integer portion of a number which we will discuss at the end of this article.

Let us first discuss INT function.

Description

The Microsoft Excel INT function returns the integer portion of a number.

Syntax

The syntax for the INT function in Microsoft Excel is very simple

=INT(expression)

Parameters or Arguments

expression

A numeric expression whose integer portion is returned. You nay directly enter numbers with decimal or you can refer cells e.g., for division, =INT(A2/A3).

Note :

If the expression is negative, the INT function will return the first negative number that is less than or equal to the expression.

Returns

The INT function returns an integer value.

Example :

=INT(65.35)   Or    =INT(5/2)   or   =INT(any formula nested)

As the name suggest generally it is used to get the integer portion of a number, the result is same as QUOTIENT function, but there is a technical difference between INT & QUOTIENT function.

INT function rounded down the number, e.g.,

=INT(55.70) the result will be 55 and if you enter =INT(55.39) 

the result will be same as QUOTIENT function.

But what if the number is a NEGATIVE number ?

The result will be wrong. As INT function, will round down the number and rounding down for negative number will increase the Integer portion of a number.

INT Function & QUOTIENT Function

Therefore, if you need to get the integer portion from result of a division, always use QUOTIENT Function.

Please note : INT will return wrong integer if the number is negative.

I hope you have enjoyed this small article. Take care…

Thank you for reading….