Monthly Archives: December 2013

vlookup with Match

Hard-coding the col_index_num in the vlookup formula is not a wise choice as you might have to look into the formula and make changes every time you copy and paste your formula.  An alternative approach of marking column index on … Continue reading

Rate this:

Posted in Formula | Tagged , | 8 Comments

Tips in constructing vlookup

Tips in constructing vlookup We talked about the syntax of vlookup and we had written a vlookup formula successfully in the previous post.  YEAH! Now we want to ride on the formula by copying it DOWN and to the RIGHT … Continue reading

Rate this:

Posted in Formula | Tagged | 5 Comments

The basic of vlookup

vlookup – Something you should know about The basic of vlookup Definition of LOOKUP (http://www.merriam-webster.com/dictionary/lookup) :  the process or an instance of looking something up;especially:  the process of matching by computer the words of a text with material stored in memory v, in the … Continue reading

Rate this:

Posted in Formula | Tagged | 6 Comments

Excel Basic – Calculation Options

Excel Bugs:  my worksheet does not calculate when I change the variable of a formula??? Don’t panic! It’s most likely that the Calculation Options has been turned to Manual. Calculation Options – Automatic vs Manual In Excel 2007 or above, … Continue reading

Rate this:

Posted in Excel Basic | Tagged | Leave a comment

Use Format Painter to decorate your spreadsheet

Christmas is all around.  Perhaps you are busy with decorating your Christmas tree with lots of joy. However it’s no joy to decorate your Excel worksheets in your daily work.  It could be really time-consuming to apply the same set … Continue reading

Rate this:

Posted in Excel Basic, Format | Tagged , | Leave a comment

Where did I save this file???

One day, a colleague suddenly asked me: “do you know where I saved this file?” by showing me her monitor with the file opened… Well, the first thing came to my mind was…… well……, input this formula in any blank … Continue reading

Rate this:

Posted in Excel Basic, Excel Tips | Tagged | Leave a comment

Excel Basic – Looks can be deceiving

Once upon a time, I was questioned about the accuracy of calculation in Excel just because one saw a formula “3% x 100” (A1*B1) yielding a result of 2.5 (C1).  WHY???? As a basic but important rule, DO NOT trust … Continue reading

Rate this:

Posted in Excel Basic | Tagged | 1 Comment

Where is the formula referring to???

This is an extension of my post – Teleport in Excel I don’t remember  how many times I want to know where a formula is actually referring to, esp. when I need to spot what’s the formula doing.  If the … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 2 Comments

Quickly delete/hide records (rows) with Strikethrough format by using Find and a couple of simple techniques.

Quickly delete/hide records (rows) with Strikethrough format by using Find and a couple of simple techniques. I had encountered a file with thousands of records (rows) that keeps track of details of different jobs.  Finished jobs were identified by Strikethrough … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 10 Comments