Tips to increase your productivity in MS Excel

1. Use F4 (re-do) extensively!

There are many examples where this could come handy, one would be Page Setup.

Imagine that you have many worksheets with the very same data layout and now you went ahead and pain-stakingly formatted one page (page/margins/header/footer/sheet). Once done, you can select multiple worksheets by holding down the SHIFT (or CTRL) key and clicking on the worksheet tabs. Once done, hit F4 and the page formatting is done for all selected worksheets!

Note: if you did have the formatting done a week ago, all you have to do is select the formatted sheet, select File-Page setup from the menu. Simply click OK (as you are not doing anything to it..). Then select the worksheets and hit F4.

2. The right-click menu is always there!

MS Excel has some not well documented features that are awesome for a power user! For example, you can easily move rows/columns around without inserting and copying values by

- selecting the row(s) or column(s) (click the header of the row or column to select it.

- move your mouse pointer to the edge of the selection

- right-click and 'drag' the selection.

- the menu appears with some amazing features

- experiment! there are many things that you can do with this!

Self Promotion: AllRight! a right click mouse menu add-in that expands the right-click capabilities can be found on the UTILS page.

3. Use the fill-handle!

The fill handle is the little black square at the lower right corner of your selection. (i.e. select one cell and you will see the little square in the lower right corner of the selection outline). Move the mouse over the fill handle and the pointer should become a cross. Stop right there!

So what is so great about the fill handle? Well, imagine that you have a looong data block and in a new column in the first data row you entered a formula. You need to copy it down to the very bottom. There are many ways to do this, of course. The fastest way: move mose over fill handle and double click! Of course, you can also click on the handle and drag it down.

Bonus hint: try to drag the fill handle while right clicking (tip #2) on it. You will be given many autofill options in the right click menu.

4. Use named ranges

In Ms Excel, you can assign a range of cells (or one cell) with a name (Insert-Name-Define..).

This can save you some time when referencing ranges in your functions. One drawback of ranges (named or not named) that they do not automatically expand when you add a value to the bottom of the range (Excel tracks the beginning and end of the range, so if you insert a new row in your range, it will be ok). This could be annoying (and dangerous) when you use Pivot tables or when you have your own functions in a nicely setup application.

Solution: Use dynamic named ranges. I will not describe here how they work. There are many places on the web. (http://www.beyondtechnology.com/geeks007.shtml).

You can also download a free wizard from

http://www.rb-ad.dircon.co.uk/rob/excelvba/utils/index.htm.

This wizard takes out all the complexity, very easy to use and you do not have to remember the complex formulas!

5. VLOOKUP/HLOOKUP are always FALSE

Well, almost always...

The lookup functions (VLOOKUP/HLOOKUP) help you to lookup a value from a range (e.g., the unit price of a stock for a company). The function tries to lookup the value in the leftmost column of the range. The VLOOKUP has 4 arguments as:

VLOOKUP(value_to_lookup,lookup_range, column_from_which_to_return_value, range_lookup)

The last argument is optional and defaults to TRUE, if ommitted. Range lookup is a logical valu to tell Excel to return the CLOSEST MATCH found in the leftmost column! This means that unless your lookup table is sorted, it will return whatever it finds first that it considers closest to the lookup value, NOT the exact match! To return the exact match, you MUST specify FALSE as the last argument. The same applies to HLOOKUP.