Serious Excel 2007 speed/formula issues!

**adamhodgkins** · 01-02-2013, 11:06 AM

Hi all,

I'm working with quite a large spreadsheet - by my standards -, (~50MB) it has 12 tabs all with various sources of data in them, and one main tab which has original data and I am trying to bring all of the data together on and run comparisons between the sources.

I'm by no means an Excel guru but I've had a look on the Internet and all I have managed to find so far is that turning off automatic calculations speeds things up, but there is still the inevitable 10 - 15 minute wait for Excel to perform calculations.

Two typical formulae from the spreadsheet are as follows:

This one to see if a value exists in a data source, if so put an "a" in the box and after format as Marlett font type to get a tick:
=IF(ISNA(VLOOKUP(B2,Data_Source!$A$1:$A$100000,1,FALSE)),"","a")

This one is to see if a value in the original data source is present in another one, and if so, what information is there:
=IF(ISNA(VLOOKUP(B2,Data_Source!$A$1:$B$100000,2,FALSE)),"",VLOOKUP(B2,Data_Source!$A$1:$B$100000,2,FALSE))

I understand that these could be made a bit simpler but I would ideally like to incorporate something to prevent #N/A coming up in any blank cells and they are returning exactly what I need.

I've been looking at possibly using a different formula to do the same thing and make the spreadsheet faster, and if possible a bit smaller. I've come across the INDEX-MATCH command, but I am having difficulty in figuring out how that command would produce the same results as I have been getting with VLOOKUP.

Does anyone have any ideas on this please? Any help would be greatly appreciated!!

Thanks

**TMS** · 01-02-2013, 11:19 AM

Why is the spreadsheet so big? 50 Mb is quite a lot for 12 tabs. How many rows? How many columns? How many formulae? On each sheet?

If you have excessive numbers of rows with lots of formulae, simply waiting for data to be entered, you could do worse than deleting the excess rows and converting the data to tables.

Regards, TMS

**daddylonglegs** · 01-02-2013, 11:24 AM

What could considerably speed up your VLOOKUPS would be to sort your Data_Source sheet so that the lookup range - Data_Source!$A$1:$A$100000 is sorted ascending. If you can do that you can use the "binary search" versions of MATCH/VLOOKUP/LOOKUP which should be significantly quicker, i.e. for the first one

=IFERROR(IF(LOOKUP(B2,Data_Source!$A$1:$A$100000)=B2,"a",""),"")

and for the second

=IFERROR(IF(LOOKUP(B2,Data_Source!$A$1:$A$100000)=B2,LOOKUP(B2,Data_Source!$A$1:$B$100000),""),"")

If you are unable to sort then you can eliminate some of the duplication by using a single MATCH for each row and then using that single result in multiple INDEX functions to return values from the same row but in different columns

**adamhodgkins** · 01-02-2013, 11:24 AM

Hi TMS,

The tabs have anywhere from 10,000 to 100,000 rows in them and anywhere from 20 to 60+ columns in them. There aren't any excess rows in there - although they all contain similar information (i.e. there will be many with the same name in the first column) they all have different information that needs to be collated in one place, and comparisons made - if that makes sense?

**olasa** · 01-02-2013, 12:01 PM

To reduce the file size, try to save the file as Excel Binary Workbook .xlsb.
//Ola

To read more...
...this binary format is still more efficient for Excel to open and save, and can lead to some performance improvements for workbooks that contain a lot of data...