Monthly Archives: December 2013

vlookup with Match

Posted on December 31, 2013 by MF

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

Posted on December 29, 2013 by MF

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

Posted on December 27, 2013 by MF

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 | 10 Comments

Excel Basic – Calculation Options

Posted on December 23, 2013 by MF

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

Posted on December 22, 2013 by MF

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???

Posted on December 19, 2013 by MF

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 | 2 Comments

Excel Basic – Looks can be deceiving

Posted on December 12, 2013 by MF

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???

Posted on December 10, 2013 by MF

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.

Posted on December 5, 2013 by MF

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 , | 13 Comments