vlookups slowing down my computer

Retrum

New Member
Joined
Aug 10, 2006
Messages
27
I have created a spreadsheet with mulitple tabs each tab has vlookups back to a main data tab. I have added quite a few tabs with a lot of vlookups. All of the sudden Excel is really slowing down and in the bottom right corner it does calculating cells with a %. It does this everytime I do something on my spreadsheet. Takes about 30 seconds to do. :devilish:

I have tried breaking my spreadsheet up into 2 different files and get the same results.

Any suggestions?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,424
Office Version
  1. 365
Platform
  1. Windows
If you have that many relationships that you have a lot of sheets and a ton of VLOOKUPS, this project might be better suited to be done as a relational database, like in Access or SQL.

In the meantime, you could always set your calculation mode to manual. Then when you make changes, it won't recalc the formulas every time (until you do so by hitting the F9 key). This can be done under Tools | Options | Calculation | Manual.
 

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
If it is not necessary for all the fields to be recalculated after every change, you can set calculation mode to 'Manual'. To do this, choose "Options..." from the "Tools" menu. On the "Calculation" tab choose "Manual". You may also want to choose "Recalculate before save". To recalculate the workbook at any time, simply press F9.

Hope this helps.
 

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
Another thought... if the data on the main tab does not change frequently, you can copy and paste the values only. To do this, select the range where the VLOOKUPS are stored, and choose Edit | Copy. Then, while the range is still selected, choose Edit | Paste Special... | Values.
 

Retrum

New Member
Joined
Aug 10, 2006
Messages
27

ADVERTISEMENT

I will make the change to manual calc.

What I have is a ms query on the 1st tab that pulls data form Access then vlookups on each tab to do reporting. So each value needs to be looked up. Thought about doing it in Acess but the reports are much easier to create and send out in Excel.
 

Retrum

New Member
Joined
Aug 10, 2006
Messages
27
is there a way to stop it from calculating while it is doing it?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,424
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Since you already have the data in Access, I would strongly recommend looking at converting to Access altogether. There are no need for VLOOKUPS in Access, the relational aspect of the database takes care of that inherently if you set up the table/query relationships correctly. Also, Access's reporting capabilities are much more powerful than Excel's.

Of course, it would take a little time and effort to do the conversion, especially if you are not real familiar with Access...
 

Retrum

New Member
Joined
Aug 10, 2006
Messages
27
I am pretty familiar with access. One ? I have is how do you send out reports that you create in access via email?
 

Retrum

New Member
Joined
Aug 10, 2006
Messages
27
Thanks I will check it out. The manual calc is working much better thanks.
 

Forum statistics

Threads
1,137,335
Messages
5,680,893
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top