How many records can Pivot Table analyse using Excel 2016?
The number of records that Pivot Table can analyse is assumed to be slightly more 1 million, based on the how many records we can fill down the column of a worksheet. But nobody ever tested that limit. This is because Excel will run slower and slower as more records are added. It would take a long time to process the data when the number of records reached the half way mark at 500K rows of records. But if you really test the limit, you will discover that the Pivot Table can run up to 1 million rows. In fact, if you know how to use Macros to insert 3 million rows into Pivot Table, you will discover that Pivot Table can actually process all the 3 million rows. Although this is possible, most people would consider it too slow to process this number of rows in Pivot Table. In one of our projects, we tested the Pivot Table with 3 million rows and it took about 10 minutes to process and complete the update. For those who think that this is the limit of Pivot Table, you are in for a surprise. Micosoft has added a new function called PowerPivot since Excel 2010. PowerPivot looks like Pivot Table and behaves Pivot Table. But the way PowerPivot process the raw data is different from Pivot Table. Because of that, PowerPivot can process a lot more data than Pivot Table, up to 20 million rows, as tested in one of my recent projects. This is good news for Excel users and companies who have to analyse huge amount of data. We don’t have to buy any more expensive software to analyse those big data any more.
Due to the limitation of Pivot Table in creating formulas, a lot of calculations have to be done in the source before the Pivot Table can present the desired results. This added more demand for computer memory and further slow down the processing of the Pivot Table. One of the formulas that is commonly found is VLOOKUP. It is used to merge the data from 2 worksheets into one (usually the source worksheet for Pivot Table). Power Pivot works differently. You maintain the data in the worksheets as is. All you need to do is to insert the 2 worksheets into PowerPivot’s work area and with a simple drag, the 2 worksheets are linked, without VLOOKUP. This helps to release the memories used for worksheet functions like VLOOKUP and make PowerPivot more robust. It is also designed to perform complex calculations
The third reason is the change in the way PowerPivot Table process the data. It is designed to process data at super fast speed as compared to Pivot Table. In one of my projects, I was able to process 3 million rows of data in 1 min as compared to using 10 mins for Pivot Table. In another recent project, I was able to process 14 million rows of data in less than 10 mins. This is done with 64 bits Excel 2016. At the moment, most of us are using 32 bits Excel as this is the more common option to choose from. If you are curnching lots of data, you might want to consider 64 bits Excel. You don’t have to pay additional to use 64 bits Excel. It comes together with the license for 32 bits Excel. Just make sure you choose 64 bits Excel during installation and the rest of the steps work the same way as installing a 32 bits Excel. 64 bits Excel is more powerful than 32 bits because of the way our computer handles the calculation using 64 bits technology.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
