Blog

As I started working with Power Pivot and Power BI I was aware that it allowed large amounts of data to be manipulated by Excel, but just how large? and how does it do that ? Just how big was the “Big Data” being talked about. I spent a little while digging to find the answers.

I was confused, how could Excel handle “Millions of rows”? Did the Excel Rows and Columns limits increase to new limits or was there some other way?

The end of the Excel World Cell XFD, 1048575 – Click to Enlarge
As I’m sure you know Excel 2013 has a hard limit of 16,386 columns by 1,048,576 rows.

(Try it on Excel 2013, in a blank workbook press Ctrl & Right-Arrow to go to the furthest column, then Ctrl & Down-Arrow to go to the furthest row)

This is big, but not as big as I was led to belive Power Pivot provides.

The Data Model

With Power Pivot, Excel has another area where data is held, called the Data Model. It is this Data Model that can be populated by Power Query, and analysed by Power Pivot. It may be old-fashioned, but I think of the Data Model like a Database attached to Excel.

It is this Data Model that can be huge. The “limitations” here are that you can have 2,147,483,647 tables in this “Database” and each of those tables can have a maximum of 2,147,483,647 columns and 1,999,999,997 rows. So here are the Millions of Rows and Columns I was hearing about.

It is this Data Model that is analysed and manipulated by Power Pivot and Power View and the result displayed on the slightly smaller grid of Excel that we’re used to. So you can have millions of rows feeding into a Pivot Table, calculated and aggregated then output to the Excel grid.

That’s very good, in fact, it’s great, but the Data Model is included with Excel when you save it, it can’t be broken out separately.  What about saving these monster spreadsheets?

Saving Large Spreadsheets

Well, it turns out that “Standard” Excel (32-bit) has a file size limit of 2 gigabytes (2,147,483,647 bytes), so the total size of the Data Model, the Spreadsheet data and any other Excel Add-ons must be below this figure. In practice, this may limit the Data Model to less than a Gigabyte. But 64-bit Excel has no limit on file sizes, other than the Memory and Disk space limits on the machine it’s running on. (Actually, the maximum number you can hold in 64 bits is 9,223,372,036,854,775,807, Microsoft calls this “effectively unlimited”, and I agree with them).

Sharing Large Spreadsheets on Power BI for Office 365

So you can save your monster Spreadsheet locally on your PC (Assuming you’re running 64 bit versions of Windows and Excel, and have got lots of memory and hard disk). But what happens when you want to share this monster? How about Uploading it to Power BI for Office 365?

Well, it turns out there are some limitations here too. Power BI runs on SharePoint Online, and that has a file size limit of 10 MB for and Excel File. However when you add Power BI to SharePoint Online that file size limit grows to a maximum of 250 MB for Workbooks that contain a Data Model and where content in the Workbook outside the Data Model totals 10 MB or less.

This 250 MB limit is for Excel files to be opened and viewed in a Browser, without the need for Excel to be installed on the PC. Above 250 MB users can open the workbook in Excel 2013 if they have that installed on their PC.

So again the Data Model is special, it is the key to sharing large datasets on Sharepoint with Power BI without having everyone run Excel on their PC.

You can find out a lot more abort Excel limits with some of these links

Data Model Specification (Microsoft)

Power Pivot Capacity Specification (Microsoft)

How much data can PowerPivot really handle?

Want to know more about Power Pivot and Excel? We cover this in our Advanced Power BI course and more. Find further information about the course here and book your place on our next session.

If you would like a more tailored and customised training specifically on Pivot Tables, then we run a specialised, on-site training course that can be booked at your convenience. Give us a call on 0800 0199 746 to find out more about the course and to arrange a convenient time for your training.