When is too much too much?

[Page0727]

It seems some data I am working on is too much for excel to handle. Yes I am limited to Excel 2010 32 bit for the moment, mainly due to those who will be reviewing the data are restricted to to this. I have about 300K rows, I have removed all special formatting, and have turned into a table for the purpose of turning the data into pivot tables. Excel is telling to choose less data and I am now pulling my hair out. Any suggestions?

 

[alansidman]

What seems to be the issue. 300K doesn't sound like much. How many columns? How many formulas. Lots of recalculations with every entry? Is it a speed issue?

 

[Page0727]

Hi, thanks for getting back to me. Yes 300k rows isn't much but when you add that its spread across 91 Columns it all adds up. I also have a couple of macros running related to pivot tables I think it but with or without those I get problems. I have set it so it isn't saving the Pivot Cache and updates upon opening but unfortunately it tells me to choose less data.

 

[Page0727]

Does anybody know if pulling the data into an access database and using excel as a front end would work? Has anybody tried this? Thanks

 

[alansidman]

I use Access and find that it can process queries very quickly. I also have links to excel spreadsheets from the queries and it works very well. I will be honest though. None of my data base tables have anywhere near 300K lines. You can no longer create PTs in Access. Data needs to be exported to Excel first.

 

[Michael M]

Some info and resolutions for Pivot Caches might be wortrh a look

https://www.contextures.com/xlPivot11.html

Also, have a look in your Excel help and type specifications and limits......Pivot data limits are listed in there

 

[Page0727]

I went through and filtered out blanks, this seemed to do the trick in reducing the file size. As was mentioned 300K in lines is not the issue, it's the size of the data. Access was not an option, It turned into a monster and was very slow. Thank you everyone for your feedback you all helped, especially as it helped just having other people acknowledge my issue.

 

[Michael M]

Some other suggestions from a previous poster

1. Save the file as Excel Binary workbook (XLSB), its 3rd one when you save a file, it will immediately reduce file size to around 50%. Practically there is no difference whether you work in XLSB format or XLSX format.

2. If your formulas refer to whole column like A:A then change it to exact range like A1:A300000 as Full column is referring to more than 1 million rows.

3. Instead of SUMPRODUCT, use SUMIFS or any other formula as using SUMPRODUCT multiple times on a big range reduce the performance. If you share your sumproduct formula here,then some one can suggest an alternate formula which would calculate fast results.

4. You may break some of your complicated formulas by inserting helper column.

5. You may change the calculation mode of your workbook to manual (Formula/Calculation options/Manual) , whenever you need to calculate just press F9, or SHIFT+F9 only to calculate the working sheet. So when you have many parameters to change, then just change all the parameters and press F9 so that calculation take place. In case of Automatic format, excel calculates internally even when you put filter or change to even a single cell.

6. Use minimum or no formatting in your database sheets.

7. Press End+Home Button and ensure it takes active cell to the cell which is down word right most corner cell of your data in the sheet. If its not delete excess columns/rows. Sometimes excel take in to account blank column/rows in memory while saving the file.

 

[Page0727]

Thank you Michael for expanding further on this.