File Size issue Maximum row issue

[mrzoogle]

Hi all,


I was wondering is there any way to decrease the size of the spreadsheet with 300,000 rows of data and increase calculating speed ?


I've used very simple formulae to ensure the increased performance but its ever growing data is causing big problem.


Also what if I reach the maximum row of 1048576? How can I combine data from different spreadsheet as one ?


Thanks for your time.


Regards,

Z.

 

[SirJB7]

Hi, mrzoogle!


You can try saving your file as .xlsb type if you use a 2007 or later Excel version. Check this link for further information:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/what-is-xlsb-file-is-there-is-any-advantages-what/716be9d8-3de0-45d7-b0b6-4ea59114d61d?msgId=2009ae77-f00e-4f16-a2c8-b1fc731057bc


About how to combine data in more than one spreadsheet, I'm afraid that it can't be performed in Excel unless you can split your data in several sheets, arrange partial totals and create a summary worksheet.

As a matter of fact I'd recommend you to migrate your Excel workbook to an Access database if handling such number of rows.

Increasing your PC's RAM will also not only speed calculations but reduce paging/swapping.


Regards!

 

[Hui]

MrZoogle


The only way to handle more than 1048576 records is to have your data stored in a Database like Access or Sql etc or in Multiple Spreadsheet or Text Files.

I'd recommend Databases not multiple files, but Excel can import data quiet readily from Multiple files.


These can then be accessed by Excel and the results summarised as required.


Powerpivot is designed to enable just this style of data volume and can eat through tens of Millions of records without blinking. You can see more about Power Pivot here

http://office.microsoft.com/en-us/excel/powerpivot-for-microsoft-excel-2010-FX101961857.aspx

or

http://www.microsoft.com/en-us/bi/powerpivot.aspx

 

[mrzoogle]

Thank you SirJB7 & Hui for Awesome tips !!