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.
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.
7. IFStatements
Formula:
=IF(logical_
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 ...
