Share in Facebook


20 November 2018

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.











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.

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++.


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.

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.

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.













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.


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.


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.


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.

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 ...

Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)