The INDIRECT function in MS Excel

The INDIRECT function is very useful in case you have derived or calculated in a cell the cell reference from where you want the data from.

For Example, if cell A3 has a value C5 (May Be calculated based on some formula or logic) and you want to refer C5 from A3 to get the value of cell C5:

=Indirect(A3)

Remove Leading Spaces in MS Excel

Many times =Trim(Text) and =Trim(Clean(Text)) does not work in MS Excel to remove and clean leading spaces created due to non-breaking space (Char(160)) specially if you are copying data from web or bullet list in MS Word into an Excel Worksheet.

This problem can be resolved by :

=TRIM(CLEAN(SUBSTITUTE(Old Text,CHAR(160)," ")))

Create Bullet in MS Excel

To create the bullet character, press ALT+0149 (Press Alt and type 0149 on the numeric keypad and release Alt).

MS Excel: Get preceding Zero of fixed width text field

When you open csv file in MS Excel, for any text field as number with preceding Zero, excel treats them as number and remove the preceding Zero.

Let us say, that field is 5 characters long:

00542

Excel will open it as (Assume in cell A1):

542

You can use the following formula to get back the Zero:

=Text(A1,”00000”)

See the example below:

MS Excel: Lookup instead of Vlookup or Hlookup

The limitation of vlookup is that you can only lookup in the first column of array and bring back results from it or any column right of it. 

Similar limitation in Hlookup where you can only lookup in first row of array and bring back result from it or any row below it.

To get over with this limitation, use lookup instead of vlookup and Hlookup:

=lookup(Lookup Value, Lookup Vector, Result Vector)

See the examples below:

Use of Lookup for Vlookup

Use of lookup for Hlookup