Handy Excel Tips

This article talks about formatting and borders.

Read the rest of this entry »

Popularity: 11%

This article basically compares excel’s vlookup and sumif array functions and explains which one to use, when and why?

Read the rest of this entry »

Popularity: 52%

If you hit F2 on any cell, you can see the formula of that particular cell only.This article gives you a shortcut with which you would be able to see all the formula’s present on your spreadsheet all together.

Read the rest of this entry »

Popularity: 8%

This article talks in great detail about excel’s Left and Right formula’s and explains how by combining “Find” and “Len” functions with Left and right we can easily handle more comlicated situations which may arise.

Read the rest of this entry »

Popularity: 26%

Unlike microsoft word, Excel does not have an option wherein you could Format text to change case. But don’t worry, excel does have “change case” functions, so in case you have incorrectly typed everything in one case than another, you won’t need to rekey them.
To change case you can use upper, lower or proper formula.
Upper would change everything to upper case. Similarly lower would put them in lower case.Proper case would capitalise the first letter, keeping the rest in lower case.

Suppose you wish to change the contents of column A to lower case instead of upper, you can do that as follows:

Popularity: 16%

The Excel’s Concatenate function can be used to join up contents of multiple cells into one cell.

The syntax for the CONCATENATE function is
=CONCATENATE(text1, text2)

When can “Concatenate” function come in handy??

When say first name is given in column A and last name in column B and you want to combine the two.
Concatenate function can help you do this as shown below:

But notice there is no space between the two so if you want to insert a space you can modify your concatenate function as follows:

Points to remember:
Concatenate function uses contents of other cells(like A and B here). So if the linked data is changed, the concatenated cell( Say C1) would change as well. And if you delete the related columns then you will get a reference error in the concatenated cell.To overcome this, you can either hide column A and B or simply copy the contents of column C and use paste special values command under the “Edit” tab.

An alternative to concatenate function:

The ampersand (&) operator can also be used instead of the CONCATENATE function to do the same task.

So in our example above if we enter =A1&B1 in cell C1, it would give the same value as =CONCATENATE(A1,B1) as shown below:

Popularity: 17%

Suppose you have the marks of each student and you want to generate a frequency distribution ie total number of students who obtained marks within a given interval.
You can do this using either Excel frequency function or by applying logic.
One of the con of using set functions like “Frequency” is that you have to familiarize yourself with the syntax first.
Like the syntax of the Frequency function is:
Frequency(Data_Array,Bins_Array)
So you should know what are data and bins array.

Data_array is an array of or reference to a set of values for which you want to count frequencies. Bins_array is an array of or reference to intervals into which you want to group the values in data_array.
The following example would make it clear.
Here is a list of marks and the interval for which you need a frequency.

So you want to find out how many students got marks below or equal to 20, how many got between 21 and 30 and so on.

Since it is array formula, so we first need to select the cells where we want the frequency i.e. E5:E10, then enter the data and bin array and hit CTRL+SHIFT+ENTER.

So, 2 students got marks below or equal to 20, 2 got between 21 and 30 and so on.

We can also do this using SUM(IF()) array formula.

This function uses two criteria and “* “in array formula denotes the second condition. So it says sum column B if marks are greater than 0 and less than or equal to 20. As you would notice, the answer is same using both options.

This function can also handle multiple frequencies which “Excel frequency function” can not. For eg. if 3 students got the same score 12 and 4 students got 47. Then if we want to do the above exercise using frequency function we will have to enter the same marks thrice or four times whatever be the case. Whereas if we use the above mentioned SUM(IF()) array formula we can simply enter these in column B as shown below:

Popularity: 35%

Sometimes when you enter text in excel, it automatically converts it to some other format. This can be very annoying. Like if I want to enter age bands as 11-20,21-30 etc then excel would read 11-20 as 20-Nov.

We can get around this problem by adding a single quote in front. This quote won’t get displayed or printed but just tells excel to treat the entries as text. However if you press F2 and try and edit the values, you will see a single quotation mark in front.

But make sure you enter the quote before hitting enter i.e. if excel has already made it 20-Nov and now if you put a quote in front then it won’t change.

This can also come in handy when you have to enter more than 15 digits in a single cell and you want to keep the format as text. If you don’t enter a quote in front or change it to a text format, excel would display it in scientific notation like 4545121215241541514 would be shown as 4.54512E+18.

Popularity: 15%

If you want to select adjacent sheets then you can do that by holding down the shift key and clicking on the next sheet tab.For e.g. There are six worksheets - sheet 1 to sheet 6. And you want to select one to four then simply select the first sheet, hold down the shift key and click on the forth sheet.You will notice this will automatically select sheet 2 and 3 as well.
However if you want to select non adjacent sheets then hold down both the shift and control keys together and choose the desired sheet.

If you want to do a mouseless selection, just hold down the shift and control keys and press “Page up” or “page down”.

If you want to select all the worksheets at once then you can also right click on one of the sheet tab and choose “Select all sheets” option.

Popularity: 6%

We can select non-adjacent cells by holding down the control key and then simply choosing the desired ones by a single click of a mouse.
However this can be very frustrating at times, because if you have mistakenly released the control key and you are not through with your selection yet,then you will have to start all over again.
We can get around this problem by just pressing the (shift + F8) keys once and then highlighting the cells that you need,using the mouse.
When you are through with your selection, you can press the same keys again or just hit the “Esc” key.

Popularity: 19%

Once we have applied filter to our data using the excel’s “AutoFilter” option and we want to sum only the non-hidden range, then we can’t use the simple “sum” function as it adds both the hidden and visible cells. In that case we can use the excel’s subtotal function as it only sums the visible range.

The syntax of the Subtotal function is:

SUBTOTAL(function_num,range_reference1, range_reference2….)

We can perform 11 different functions in conjunction with subtotal.

Function_Num - Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Suppose you have a list of 4 different products - A, B, C and D along with their quantity sold.

And you wish to add up only the quantity sold of product A. Then if you apply filter and sum the range using “Sum” function then it will give the total for all the products

Whereas if you use subtotal function you will only get it for product A as shown below:

Popularity: 11%

Whenever you have some text in one column and you wish to break it into several ones then instead of retyping the text in the next column, you can use the “Text to Columns” option under the “Data” tab. For e.g. Both the names and marks of the students are in the same column(say Column A) and you want to separate them in different columns as shown below:

The first step is to highlight the column that needs to be separated. Then click “Data” and choose “Text to Columns” option.

Then choose the delimited option.

Select the “space” check box, keeping others unchecked. So whatever text is after the space will be put into the next column.

As soon as you click the “Next” tab you will see the preview of your data. And you will notice that it has put marks into the next column.

Now just click the “Finish” button and it will be done.

Popularity: 6%