# what is best solution if number of rows exceeds 1048576?



## yabi100 (Monday at 11:50 AM)

In a database I have rows around 1.8 million rows. I am not familiar with other database except excel. Is there any way to solve this limitation? I will be happy to hear any soluton other than excel.


----------



## Waimea (Monday at 11:53 AM)

I think you could over PowerPivot and the PowerPivot data model.

Google for PowerPivot.


----------



## yabi100 (Monday at 11:57 AM)

Waimea said:


> I think you could over PowerPivot and the PowerPivot data model.
> 
> Google for PowerPivot.


Thanks for your prompt reply. I have heard PowerPivot and know little bit about it. Do you mean to produce many tables and merge them using PowerPivot?


----------



## Joe4 (Monday at 11:58 AM)

yabi100 said:


> In a database I have rows around 1.8 million rows. I am not familiar with other database except excel. Is there any way to solve this limitation? I will be happy to hear any soluton other than excel.


The issue is that Excel is not really a database - it is a spreadsheet program. 
Even if you do manage to get it to work in Excel, performance may not be great.

It would be better to use a database program, like Microsoft Access, SQL, Oracle, or MySQL.
If you are stuck using Excel, then I would recommend looking into using PowerPivot, as Waimea suggested, though you may have to get a little creative in splitting up the data because, as you said, a single Excel sheet cannot exceed more than 1048576 rows.


----------



## dmt32 (Tuesday at 3:33 AM)

yabi100 said:


> In a database I have rows around 1.8 million rows. I am not familiar with other database except excel. Is there any way to solve this limitation? I will be happy to hear any soluton other than excel.



Hi,

Notwithstanding what others here have already said, it is possible in a very clunky way to make a workbook act as a flat file database (single table) The issue of what to do once the worksheet is filled can be addressed by first checking that there are enough rows to post your data to & if not, add another worksheet to the workbook & name it in sequence e.g.

Database

Database-1

Database-2



About 15 + years ago, I did such a thing for my daughter who worked for a large charity where staff (about 250) submitted timesheet data weekly. My daughter was not permitted to use a database application like Access on corporate network so Excel was her only option. I created for her an Addin that she distributed to staff that connected to the master workbook (database)

At that time, Excel worksheets only had 65,536 rows so as can imagine, the database would fill very quickly but the workaround seemed to perform ok & my daughter would archive historical data from the database to prevent it from becoming too large. (255 worksheet limit in xl2003)

So, in answer to your question “Is there any way to solve this limitation” depending on your circumstances & with just using Excel – MAYBE but have to agree with others, try if can to use an application that is better suited to the task.



Hope Helpful



Dave


----------

