Easier way to apply formulas to reduce file size?

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
Hey everyone,

I've managed to get all my spreadsheet/formulas working fine, but I'm running into an issue where the file size has just become huge.

One tab I've got raw data, and on another tab I've got all my formulas that, when the raw data is entered into the first tab, become populated with the filtered data that I'm interested in (i.e. raw data is in F and one formula is to convert it to C). I've made it so that these formulas on the second tab are applied to every cell in the column (all the way down to 250000 or what ever the limit is) because I'm unsure how much raw data I'm going to have, and wanted it so that any future data could simply be entered and transformed instantly. So as you can imagine, after a couple of rows, there's a lot of data being stored and a lot of cells with formulas not being used. I've also got pivot tables going on a separate tab, but they don't seem to be an issue in this case.

This has resulted in my spreadsheet being 20+mb in size.

Is there any way to make it so that the formulas on the second tab are created only when the raw data is entered? Is it possible to do this using a macro at all, remembering that in the future, additional data will be entered into the spreadsheet? Or do I just have to live with an excessively large excel file?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
How far down the second tab do the formulas need to end up going? Is it the same number of rows as there is data on the first tab?
If not, how would we work out how many rows are needed on the second tab?

Where do the formula start on the second tab? Is it row 2?

When you enter more data in the future, is it added below any existing data or do you clear out the old data and put your new data at the top of the first sheet again?
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
How far down the second tab do the formulas need to end up going? Is it the same number of rows as there is data on the first tab?
If not, how would we work out how many rows are needed on the second tab?

Where do the formula start on the second tab? Is it row 2?

When you enter more data in the future, is it added below any existing data or do you clear out the old data and put your new data at the top of the first sheet again?
That's the thing, I'm unsure how far the formulas need to go down because I'm not sure how much raw data I'm going to end up with. To give clarity, I'm looking at annual daily weather data for the year, so a minimum of 1 to a maximum of 366 data points per year. But that's for one station only, and we could be talking about thousands of stations with similar data. Additionally, the data includes items such as temperature, dew point, attributes etc. So, 366 columns and something around 28 rows is standard for one year.

The second tab has approximately 10 rows, but is inclusive of all the data. I've made it so the formulas go all the way down the sheet so any raw data instantly gets filtered.

When adding data, it gets added to the bottom. It doesn't matter what order it's put in as the pivot table has everything grouped by date.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
That's the thing, I'm unsure how far the formulas need to go down
I will re-state my question.

If there are 2500 rows of data on Sheet1, do you need 2500 rows of formulas on Sheet2?
Then if you paste another 1000 rows on Sheet1 do the formulas need to now extend to row 3500 on Sheet2?

If this is not the case, how will we determine how many rows of formulas are needed on Sheet2?


The second tab has approximately 10 rows, but is inclusive of all the data.
I don't understand what that means.
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43

ADVERTISEMENT

I will re-state my question.

If there are 2500 rows of data on Sheet1, do you need 2500 rows of formulas on Sheet2?
Then if you paste another 1000 rows on Sheet1 do the formulas need to now extend to row 3500 on Sheet2?

If this is not the case, how will we determine how many rows of formulas are needed on Sheet2?


I don't understand what that means.
Sorry, I'm replying on my phone so I may have jumped up what I'm trying to say.

1) Yes and no. Yes to the fact that for 1000 points of data I'll be applying formulas to them. Example a thousand rows of temperature data requires it all to be converted. Any additional raw data added must also be converted. So from 1000 points and an additional 2500 requires 3500 conversions.

But also no. So for example, if there are 28 rows of raw data, the second sheet only applies formulas to the data I'm interested in. It could be as simple as a unit conversion, or displaying values above X as blank cells whilst still showing accompanying data. However the raw data comes in a standard form, so the second sheet may only apply formulas to rows A, C, Z. But, for each data set I'm interested in, a formula must be applied to it. Most of this is for filtering. Example: Dew point for February is in row C, and February 1st is 999, which is the value that indicates no data, so my formula in sheet 2 applies a 'read cell if blank then blank, if cell>900 blank, if not then cell'. This is done for all of row C for every column. Similar for other sets of data.

I hope that made sense?

So in essence, I only have 8 rows of formulas, but every column requires a formula in it for the raw data I'm interested in.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

So you have ALL the required formulas stored on Sheet2 and they get applied to however much data is in Sheet1 ??
I'd sggest a SMALL sample of data with the formulas in their required locations, which I assume will be A,C & Z
Have a look at my tag to download XL2BB, to be able to post said sample.
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
O
I'm afraid that it doesn't to me, sorry.

BTW, A, C, Z are columns, not rows. Any confusion about that sort of thing can also make your request hard to follow.
H dear lord my bad. No wonder there was confusion. I'm terribly sorry!
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
So you have ALL the required formulas stored on Sheet2 and they get applied to however much data is in Sheet1 ??
I'd sggest a SMALL sample of data with the formulas in their required locations, which I assume will be A,C & Z
Have a look at my tag to download XL2BB, to be able to post said sample.
I wish I could use a small sample of data, but there could be 28 columns and 366 rows times by 100 stations for one year alone. I can always reduce the file size by doing half the sheet for now, and then the rest of the sheet when the raw data reaches that point, but then I run into the same problem later.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
But we only need to see a SMALL sample to try and acertain the best way to provide a solution! It doesn't need to be 28 columns and 366 rows times by 100 stations
So....rather than populating the entire columns on Sheet2 in advance, why not apply the formulas to the raw data whan it is pasted in Sheet2 via a macro.
If this is an option you will need to supply the formulas and which column / columns they go into ??
You don't say which version of Excel you are using, but there could be millions of redundant formulas on the sheet !!
 

Forum statistics

Threads
1,148,368
Messages
5,746,287
Members
424,006
Latest member
Metal_warrior

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