Handy Excel Tips

This article shows how to locate cells in active workbook which contain links to external workbooks.

Read the rest of this entry »

Popularity: 13%

Sometimes we want to pull out data on one sheet from different worksheets, all of them having the same structure.We can do that using the vlookup or hlookup function as the case may be. However, while selecting the appropriate range we will have to go to each tab and select the range manually. This can be very time consuming especially if there are lots of tabs. Excel’s indirect function can help us get around this.

Read the rest of this entry »

Popularity: 16%

This article explains how we can have A-Z letters on a column without typing them in manually.

Read the rest of this entry »

Popularity: 24%

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: 24%

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: 13%

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: 13%

Identifying duplicate entries in a large list can be very time consuming but with conditional formatting duplicate entries can be identified very fast.You just need to select the cell range and then click “format” tab and then conditional formatting (Keyboard Shortcut is: Alt+O+D). From the condition1 drop down menu choose “Formula Is” and enter =A3=A2 (Assuming the selected cell range is A2:A23). Click the format button and choose any color and just hit OK.

However the above formula would work only when we have duplicate entries in adjacent cells. If instead we want to identify all the duplicate entries that are there in the entire column we can use the countif function instead of “=A3=A2″. So this time in the formula bar of conditional formatting you enter =COUNTIF($A$2:$A$23,A2)>1. We need to freeze the range( A2:A23) by using F4 key otherwise as we go down from A3 to A4 the range would become (A3:A24) which is not what we need. However cell A2 is an active cell and we want excel to take A3 and so on as move down that is why we have kept A2 as a relative reference instead of making it absolute(Dollar sign).

Popularity: 12%

Sumif(offset) Functions

Sumif function is useful as long as we want data only from one column as sumif only allows a single condition. However we can get around this problem by using a combination of sumif and offset as is illustrated in the example down below.

Suppose you work at some XYZ organisation and it launched product A and B in year 2001, C in 2002, D in 2003 and E in 2004. They have provided you data in one format but require the results to be presented in some other format as shown in the sheet below.

Now we can use the sumif function to get the results in the required format.

The syntax of the sumif function is:
SUMIF(range,criteria,sum_range) OR
SUMIF([Column to be evaluated],[Criteria],[Column to sum if criteria is met])

The main problem with using a plain sumif function here is you will not be able to drag the formula and you will have to change the name of the column from B to C to D to E as you move along 2001,2002,2003 and 2004 as shown below.

The offset function can resolve this issue as it allows multiple conditions and will pick the numbers from the correct column.

The syntax of the offset function is:
Offset( range, rows, columns, height, width)

Although the result is same using both options but using sumif along with offset would spare us from changing the formula as we move down.

Popularity: 16%