everydayExcel Newsletter Header

Is It a Good Idea to LOOKUP One Million Rows in Excel 2007?

In our Hidden Secrets in Data Analysis course, we usually ask our participants to accurately define the range they are referring to. We learnt that for some participants who had used VLOOKUP before, they were previously taught to LOOKUP the entire column from another worksheet/range/workbook. And they often asked us if there is any difference between the two options, referring to the specific range only or the entire column. If you are working with Excel 2003 and below, looking up the entire column basically means referring to only 65,000 rows. In Excel 2007 and beyond, it is looking for 1 million rows.

The verdict? It is now okay in Excel 2007 to specify the entire column of 1 million rows when you use VLOOKUP. Microsoft has changed the way Excel works in version 2007. It will only calculate up the last used cell even if you refer to the entire column.

While this may be true for VLOOKUP, it may not be true for some other formulas, known as volatile formulas as well as conditional formatting. Therefore, we still highly recommend that you identify the specific range for your formula instead of selecting the entire column for the sake of convenience. You may work against you in the end.

Another improvement they have done to Excel 2007 is the speed of calculation. In version 2007, Excel will calculate formulas that are linked to a particular cell instead of recalculating the entire worksheet when the cell changes value.

Ask us an Excel question and get your question answered by our team of Excel experts

Question: Pivot Table help needed

I have a matrix with survey sites as rows and sponge species as columns. Cells have the sponge abundance for each site and species. I want to extract the 10 most abundant species for each site, with the corresponding abundance, and put these values into a new matrix.... 

Click on this link to read the full question and answer.

Upcoming Courses

With the newly introduced PIC bonus, you are now paid (not paying) 60% of the course fees for sending your staff to our courses. Check out now in this IRAS PIC Link.

Hidden Secrets in Data Analysis with Excel 2007/2010 - 1 & 2 Aug 2013 

Interactive Dashboard Reporting with Excel 2007/2010 - 25 & 26 Jul 2013 

Learning the Magic of Macros 2007 (Excel VBA) - 15 & 16 Jul 2013 

Breakthrough Performance for HR in Excel 2007/2010 - 29 & 30 Aug 2013 

Dynamic Real-Time Forecasting with Excel 2007/2010 - 5 & 6 Sep 2013

For the details of the courses, please refer to our Excel courses page.

Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.

Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.

Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"


Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.