Excel file too large to work?

**Ahouriani** · 02-24-2013, 04:10 PM

I've been having tremendous issues with my Excel file, mostly with it crashing or not responding. Would this be because my file has so much data in it? If so, is there anyway to make Excel process all the data without crashing so much?

Thank you

**Richard Buttrey** · 02-24-2013, 04:22 PM

Hi,

You'll need to tell us the size of your file, how much memory you have and other stats. like how many sheets and formula cells you have.
Are you running any macros?

The usual cause of problems like this is formulae being accidentally being copied to the last row on a sheet, i.e. row 1,048,576.
To identify the last used cell use the [End] [Home] keys on each sheet and see where the cursor jumps to.

**Ahouriani** · 02-24-2013, 04:26 PM

Hello Richard,

The file size is 13.5mb. It has 4 sheets, 2 of which go up to 1399 and 2000 rows with data. I'm not running any macros either

**TMS** · 02-24-2013, 04:35 PM

How many columns are used on each sheet? How many columns are just "raw" data?

Do you have formulas which "prepare" a row for data entry? For example: =IF(A1="","", ... some formula ...). If you do, how many rows and columns have formulas like that?

Regards, TMS

**Ahouriani** · 02-24-2013, 04:41 PM

Hey TMShuchks,

On the sheet with 1400 rows, I have 548 columns, and on the sheet with 2000 rows, I have 2 columns.

Regarding the formulas which "prepare" a row, I have a giant data table with roughly 600,000 data points (if that's what you mean). I also have several equations working together to create a final output. Hopefully this helps.

Thank you

**Marcol** · 02-24-2013, 04:48 PM

Check for volatile functions such as OFFSET(), INDIRECT().

TODAY() in a copied formula can really drag down performance.
Better to put =TODAY() in a fixed cell, say $A$1, and refer to that rather than build it into a formula.
e.g. in A2, Dragged Down

Formula:

Please Login or Register  to view this content.

Not

Formula:

Please Login or Register  to view this content.

Conditional formatting applied to whole Columns can also be expensive, C/F is also volatile.

**TMS** · 02-24-2013, 05:27 PM

If I fill 2000x2 cells on one sheet, and 1400x548 cells on a second with the formula: =ROW()*2000+COLUMN()*10 and save it in Excel 2007, the file size is 6,545 kb. If I copy all those formulas and Paste Special | Values and save the file, the file size is 3,897 kb.

So, a mix of formula and raw data would be somewhere in between ... I would guess. We have a fair few megabytes still to find.

What and where is this 600,000 data points?