How do you get 4 million rows of data into Excel?

So firstly lets face facts.  Not many people need to bring 4 million rows of data into Excel.  Point accepted.

However, some do, and I recently needed to do this.

If you’ve ever checked  (Press End then the down arrow key) Excel has just over 1 million rows, but if you start adding a formula or two to those rows then you will soon have issues.

So how do you do it?

The Answer is PowerPivot!

Here is a screenshot to prove it.  4 million rows of data loaded into my data model from a year’s worth of transactions in a 1.7 GB text file.

 

 

This data had a column called Period and contained the numbers 1 to 12.   Which isn’t the nicest way to report data, much better to have the month descriptions.  So I then imported a Lookup Table containing the month names and linked these two data sets together (using a simple drag and drop).

 

   

 

That’s the equivalent of 4 million VLOOKUPs calculated instantly.

Finally a quick Pivot Table and done….

 

 

4 million records isn’t actually that many for PowerPivot (especially not on a 64 bit machine).

The final Excel file compacted down to 90MB due to PowerPivot’s brilliant compression.  And if I’d removed some of the unique fields such as Batch Number then the compression would be significantly better again.

Find out More

If you’d like to know more about manipulating and analysing large data sets, call us on +61 8 6210 8500 or leave your details below & we’ll be in touch shortly.