Named Ranges & Calculation Times Query

  • Thread starter Thread starter Mathew P Bennett
  • Start date Start date
M

Mathew P Bennett

Good Evening Guys, Excel 2007
I have a query about Named Ranges and the associated calculation speeds.
I have a wb which uses a master sheet which runs to over 35,000 rows & only
15 Columns. I run another ss which has 5 vlookup columns referencing to the
master sheet.
Now I have been trying to use Named Ranges to make the vlookup's easier.(and
faster)
I have saved the same wb as three seperate workbooks, and have found the
following:

WB1: Named Ranges using offset & counta, file size
6.73mb Calc time >5 mins
WB2: Named Ranges using index & counta, file size
6.87mb Calc time 13 secs
WB3: No Named Ranges - Just Absolute Cell Referencing, file size 8.08mb
Calc time 04 secs

Is someone able to explain this, as I was under the mpession that it was
always advisable to use named ranges.
Thank you in advance.
Mathew
 
It is advisable from a maintenance aspect, but not from a performance
aspect. Whereas Excel will only evaluate formulae in cells that have
changed, or have precedents that have changed, formulae in names are
evaluated every time a calculation kicks in. Hence a performance hit.
 
Thanks Bob for you input again.
So what do you recommend considering the number of rows?
My only concern is that I would like the ranges to update to the final row
in master, automatically, as this data comes from a refreshable database
msquery.
Cheers Bob,
Mathew
 
The solutions are usually muti-fold. Helper cells used to hold common
calculations often help, and sometimes it is by way of UDFs. It all depends
upon the spreadsheet, the data, and the formulae being used.
 
Thanks Guys, food for thought. Cheers Biff for the link. Have looked. Seems
encouraging and interesting, Cheers again, Mathew
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Named Ranges Extremely Slw To Calculate 3
Naming a range 3
Dynamic named range reference from external workbook 2
Calculation Speed (Index/Match vs Vlookup)? 6
Updating macros with copied worksheet information 3
Lessons from optimizing a slow spreadsheet 2
How to fix Compatibility error 3
totally lost - named range and vlookup 13

Back
Top