how to faster Large files

nf24eg

Board Regular
Joined
Nov 15, 2009
Messages
152
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
I'm working on an Excel file that it's size is about 90 M.B and that makes it very slow to deal with ... is there anyhow to make it faster ???
i use in it alot of formulas and conditional formats
thank u
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
The sheet itself being 90mb doesn't have to mean a slow performing sheet. You just have to minimize the number of "constantly updating calculations" in your sheet.

1) Conditional formats on a large scale cause sheet performance to degrade.

2) Array formulas (Control-shift-enter or SUMPRODUCT()-type formulas) evaluating HUGE datasets in one cell...very big performance hit. Keep array formulas to the smallest range of data possible and the fewest number of these formulas in your sheet as possible.

If you find you think you need these "array-type" calcs bringing data evaluated across tens of thousands of rows of data back into one cell...eliminate the need for arrays by adding more columns. Let these helper columns do some of the calcs for you on a row by row basis, then sum/count/evaluate the helper columns to reduce calcs.

Believe it or not, adding 20,000 new formulas in a column may increase the size of the workbook and greatly increase the speed of the workbook at the same time.
 
Upvote 0
Thank you all for the fast reply ... but i'm sorry i didn't got what u mean by the (Control-shift-enter or SUMPRODUCT()-type formulas)
so here is the workbook
http://rcpt.yousendit.com/776279428/4851a82ca6c87727489a4a15444520ef
and i hide the some months hoping that it can work faster this way ..
this month i work in OCT sheet ... i fill the mobile number cell and it reads the data from the LIST sheet ..
thank you again

The sheet itself being 90mb doesn't have to mean a slow performing sheet. You just have to minimize the number of "constantly updating calculations" in your sheet.

1) Conditional formats on a large scale cause sheet performance to degrade.

2) Array formulas (Control-shift-enter or SUMPRODUCT()-type formulas) evaluating HUGE datasets in one cell...very big performance hit. Keep array formulas to the smallest range of data possible and the fewest number of these formulas in your sheet as possible.

If you find you think you need these "array-type" calcs bringing data evaluated across tens of thousands of rows of data back into one cell...eliminate the need for arrays by adding more columns. Let these helper columns do some of the calcs for you on a row by row basis, then sum/count/evaluate the helper columns to reduce calcs.

Believe it or not, adding 20,000 new formulas in a column may increase the size of the workbook and greatly increase the speed of the workbook at the same time.
 
Upvote 0
1) INDIRECT()

This is a volatile function. It updates constantly. Every cell with an INDIRECT() formula will recalculate every time you change anything anywhere.

That makes INDIRECT() a tough choice when you're putting it 1000s of cells. You have two FULL columns of them on the OCT sheet.

==========
TO REMOVE INDIRECT():

On your LIST page, I created a new named range:
ALLMONTHS
=List!$E$3:$CG$600


This is a table of all your possible months. With this, we will us a standard 2-dimensional INDEX/MATCH/MATCH to do the look with no INDIRECT().

You can erase IT2 and IT3.

In E4:
=IF($C4="", "", INDEX(AllMonths, MATCH($C4, List!$D$3:$D$600, 0), MATCH($IT$1, List!$E$1:$CG$1, 0)))

In F4:
=IF($C4="", "", INDEX(AllMonths, MATCH($C4, List!$D$3:$D$600, 0), MATCH($IT$1, List!$E$1:$CG$1, 0) + 1))

Copy those two formulas down.

Do this on all the sheets where you have those INDIRECT() still operating, as do many of your hidden sheets. Better...flatten the previous sheets by removing ALL formulas from them so you know the values won't change for prior months.

1) Highlight the entire sheet
2) Press CTRL-C
3) Select EDIT > PASTE SPECIAL > VALUES

Now the data is permanent and no more calcs from this sheet will slow down sheet performance.
===========
Another way to do M4:
=IF(COUNT(C4:I4)<>7, "", G4 + MIN(I4,E4) + MIN(F4,H4) + K4)

===========
On the LIST page you have a whole column COUNTIF() formula in the Conditional formatting of column A....worse...you've also entered the same CF formula for two separate conditions, that means it is fully checking TWICE every time it fails, for every cell!

Also, you applied that CF to the entire column...65k cells instead of just the 500 cells that make your database. If you want to go a few 100 rows past, that's one thing, but to go 65k rows past...don't do that.

Also, your CF formula is doing a COUNTIF() on the entire column. It would be best to limit that as well, perhaps to 600 rows, that should be plenty.

Same thing on the TELEPH_LIST sheet. The CF should only be applied to about 300 rows, and then change the CF formulas to the same 300 rows.

The CF formulas on the CHECKING sheet are much better..except they are applied to the entire column.
===============
You've colored the background of every cell on the INDEX sheet. Color just enough to get the display you want.

===============
On the REPORT page you don't need to run the ENTIRE formula twice in D5 to test for the ISNA(), you can just test the MATCH().

D5:
=IF(ISNA(MATCH(B5,List!$A$1:$A$500,0)),"",INDEX(List!$E$1:$E$500,MATCH(B5,List!$A$1:$A$500,0)))

===========

Your sheet does not have 90mb worth of data. You have "sheet bloat" that is frequently caused by formatting applied to entire sheets. Your sheet is actually only about 9mb.

I went through all the sheets and removed all the formatting from a few 100 rows past your data to the ends of the sheets. You dropped 82mb in size.

;)

I've attached a copy of your sheet with most of the stuff we went over here so you can see.

https://sites.google.com/a/madrocke...WTELEPHONES-W-Macros--.zip?attredirects=0&d=1
 
Upvote 0
1) INDIRECT()

This is a volatile function. It updates constantly. Every cell with an INDIRECT() formula will recalculate every time you change anything anywhere.

That makes INDIRECT() a tough choice when you're putting it 1000s of cells. You have two FULL columns of them on the OCT sheet.

==========
TO REMOVE INDIRECT():

On your LIST page, I created a new named range:
ALLMONTHS
=List!$E$3:$CG$600


This is a table of all your possible months. With this, we will us a standard 2-dimensional INDEX/MATCH/MATCH to do the look with no INDIRECT().

You can erase IT2 and IT3.

In E4:
=IF($C4="", "", INDEX(AllMonths, MATCH($C4, List!$D$3:$D$600, 0), MATCH($IT$1, List!$E$1:$CG$1, 0)))

In F4:
=IF($C4="", "", INDEX(AllMonths, MATCH($C4, List!$D$3:$D$600, 0), MATCH($IT$1, List!$E$1:$CG$1, 0) + 1))

Copy those two formulas down.

Do this on all the sheets where you have those INDIRECT() still operating, as do many of your hidden sheets. Better...flatten the previous sheets by removing ALL formulas from them so you know the values won't change for prior months.

1) Highlight the entire sheet
2) Press CTRL-C
3) Select EDIT > PASTE SPECIAL > VALUES

Now the data is permanent and no more calcs from this sheet will slow down sheet performance.
===========
Another way to do M4:
=IF(COUNT(C4:I4)<>7, "", G4 + MIN(I4,E4) + MIN(F4,H4) + K4)

===========
On the LIST page you have a whole column COUNTIF() formula in the Conditional formatting of column A....worse...you've also entered the same CF formula for two separate conditions, that means it is fully checking TWICE every time it fails, for every cell!

Also, you applied that CF to the entire column...65k cells instead of just the 500 cells that make your database. If you want to go a few 100 rows past, that's one thing, but to go 65k rows past...don't do that.

Also, your CF formula is doing a COUNTIF() on the entire column. It would be best to limit that as well, perhaps to 600 rows, that should be plenty.

Same thing on the TELEPH_LIST sheet. The CF should only be applied to about 300 rows, and then change the CF formulas to the same 300 rows.

The CF formulas on the CHECKING sheet are much better..except they are applied to the entire column.
===============
You've colored the background of every cell on the INDEX sheet. Color just enough to get the display you want.

===============
On the REPORT page you don't need to run the ENTIRE formula twice in D5 to test for the ISNA(), you can just test the MATCH().

D5:
=IF(ISNA(MATCH(B5,List!$A$1:$A$500,0)),"",INDEX(List!$E$1:$E$500,MATCH(B5,List!$A$1:$A$500,0)))

===========

Your sheet does not have 90mb worth of data. You have "sheet bloat" that is frequently caused by formatting applied to entire sheets. Your sheet is actually only about 9mb.

I went through all the sheets and removed all the formatting from a few 100 rows past your data to the ends of the sheets. You dropped 82mb in size.

;)

I've attached a copy of your sheet with most of the stuff we went over here so you can see.

https://sites.google.com/a/madrocke...WTELEPHONES-W-Macros--.zip?attredirects=0&d=1

Thank you so much .. it was great to lean new thing ..but ur mes. includes too much info. for me but i will do my best to get it all to not do the same mistake again ...

thank u so much for helping me and have a good day

best regards
 
Upvote 0
is there somehow to change the title of this thread to SOLVED?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top