Efficient Calculations - which option is better

floored

New Member
Joined
Jun 26, 2002
Messages
16
I'm building a spreadsheet which is fed a series of data from another spreadsheet. The same data is reused several times within the file I'm building. I'm not entirley sure how Excel stores linked values. Does Excel look up a cell reference 1x's and store that as a variable which it recalls as needed, or does it look the reference up everytime it encounters the same reference?

The follow-up question :

Is it better to have each reference in my document link to the same cell in the external document, or am I better (from a calculation standpoint) to have one external link to that cell and have all the other cells in my file point to the cell with that external link. Which will take less calculation power from Excel?

Any help is appreciated.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
You can choose excel how to calculate by going to tools, options, calculations, then choose either automatic or manual.
In automatic calculation, it recalculates everytime you change the dependent cell. In manual , it only recalculates when you press f9. If you save or open a file, it recalculates whether you are in manual mode or not.

For your second question, which is best to have several cells link to external files or only one cells, i am not sure but i think one link to external is better than many links.
 

floored

New Member
Joined
Jun 26, 2002
Messages
16
hmm... guess I didn't phrase my question properly. My concern is about how to maximize the efficiency of a power spreadsheet.

I have maybe 10 different references to an external excel file. I use each of the ten data points at least 10-25 times each. These links need to be dynamic as the source file is updated monthly. I'm trying to figure out how to configure my reporting file to minimize file size and the calculation time. Is it better to have 1 link for each of these 10 references and have all my subsequent uses of the data point to the link which is internal to my file, or should they all point to the external file. If Excel stores external link values as a variable related to the cell reference, I am assuming that the second option would actually be more efficient. The problem is I don't know for certain if that is how excel handles external links.
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Made a test linking just cell "a1" of external source file to destination file then copy the reference internally from column a to k of the destination file. The process just took me about 1 minute. I closed the two workbooks.

Then i tried creating a new one with links to cell "a1" of external source file with all my cells from column a to k. My computer hanged after that.

Final Verdict, the more external link, the greater the process.
Better to link one external link and then reference internally .
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,879
Members
414,029
Latest member
mrwilker

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