File Size Issue with Embedded Formulas

BrettOlbrys1

Board Regular
Joined
May 1, 2018
Messages
75
Office Version
  1. 365
Platform
  1. Windows
I need some help to resolve my issue. I have two files, one that contains all of the raw data (about 10mb), and a dashboard file that people have access to. The raw data file grows in size every day because of the sales that happen every day, but one sheet of data may grow by 100 lines and the other sheet of data may grow by 70 lines (because two different segments of the business).

This dashboard file pulls the raw data into it, and then using lots of formulas, displays the data in a consumable format.

I have 2 issues:

1) If all of my formulas look at the columns of data, lets say $A:$AL, my file size of this dashboard file grows to 156mb (compared to the 10mb of actual data)
2) If my formulas all reference a specific range ($A$1:$AL$10000), my file size is about 7mb, but when new data is pulled into the data sheets for analysis, the formula ranges change. One range may change from $A$1:$A$10156 and another range (which references the other set of data) may change to $D$1:$D$10500. Because these ranges now have different row counts, my sheets give me an error because the ranges in the formulas are different.

Question:

How do pull data into my worksheet, from two different sources, without affecting the formulas that look at that data so that the ranges don't change AND I keep my file size small?

Thanks

Brett
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,463
Office Version
  1. 365
  2. 2010
Make your ranges an Excel Table and refer to these tables in your formulas. it's quite easy to accomplish
 

BrettOlbrys1

Board Regular
Joined
May 1, 2018
Messages
75
Office Version
  1. 365
Platform
  1. Windows
Make your ranges an Excel Table and refer to these tables in your formulas. it's quite easy to accomplish
Thanks for your reply. My two main sheets of data are brought into my file, from the external data file, through a data connection. When they are brought into my file, they are already tables. If they are tables already, what is causing my file size to increase from 10mb to 150mb? Are you saying that because I am using a lookup of A:A, instead of Column1?
 

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,463
Office Version
  1. 365
  2. 2010
Are you sure they are already imported as Excel Tables, and not a simple range ?
 

BrettOlbrys1

Board Regular
Joined
May 1, 2018
Messages
75
Office Version
  1. 365
Platform
  1. Windows
Are you sure they are already imported as Excel Tables, and not a simple range ?
Yes, they area definitely tables. One thing I just discovered is that if I look at the properties of the tables, there is an option to "override existing cells with new data" instead of "insert cells for new data." My issue has been the insertion of data because it changes the formulas, so if I simply overwrite the data then the formulas should stay the same. I still don't understand though how 10 MB of data can grow to 156 MB of data simply through cell references.
 

Forum statistics

Threads
1,175,833
Messages
5,899,728
Members
434,797
Latest member
natejxoticpc

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