Excel Magic Trick 1158: Get Millions of Rows of Data into PivotTable without PowerPivot, Excel 2013

See how to Get Millions of Rows of Data into Excel 2013 for a PivotTable without PowerPivot:
1. (00:10 minute mark) Problem Setup for Excel 2013 when you don’t need Group by Date
2. (01:06 minute mark) Data Ribbon, Get External Data, then in Import Data dialog box: select “Only Create Connection” and check “Add this data to the Data Model”
3. (01:59 minute mark) Create PivotTable and in the Create PivotTable dialog box select “Use an external data source” and then in the Tables tab select “Tables in Workbook Data Model”.
4. (02:43 minute mark) No GROUPING BY DATE with data Model
5. (02:52 minute mark) PivotTable with Slicers

Get Big Data Into Excel (Data Model). Millions of Rows into Data Model For PivotTable.

[excelisfun]

Excel Magic Trick 1158: Get Millions of Rows of Data into PivotTable without PowerPivot, Excel 2013
See how to Get Millions of Rows of Data into Excel 2013 for a PivotTable without PowerPivot:
1. (00:10 minute mark) Problem Setup for Excel 2013 when you don’t need Group by Date
2. (01:06 minute mark) Data Ribbon, Get External Data, then in Import Data dialog box: select “Only Create Connection” and check “Add this data to the Data Model”
3. (01:59 minute mark) Create PivotTable and in the Create PivotTable dialog box select “Use an external data source” and then in the Tables tab select “Tables in Workbook Data Model”.
4. (02:43 minute mark) No GROUPING BY DATE with data Model
5. (02:52 minute mark) PivotTable with Slicers

[pmsocho]

We can also do such thing in Excel < 2013. We import data through Data tab and we do not load the data into a worksheet but only create a connection. Next step is creating a pivot table that uses that connection :)

[ManishKumar-zkdy]

Awesome trick.
However, I ran into a problem where I have created an excel report already using SQL server view. But after some time I had t include a few more columns in view and so in the excel pivot. However, the column which I have included in the view is not reflecting in the excel after refreshing the connection. Please share some workaround to resolve it. Thanks

[samkab]

Can we do that for example for 3 files and add them together as one to the data model ?

[skylark]

It's really usefull. Thanks for video :-)

[abhaygadiya]

Awesome trick...few questions: 1) Can't i use timeline slicer to get month / year wise grouped data 2) is it only dates i can't group or it is applicable for other fields also e.g. grouping few store ids?

[LorisAyoub]

I keep adding data to 1 sheet, and I am up to 1Million, how do I add more lines? It is not about importing data but I always get new data and add to that one sheet...what can I do? Thanks

[SahidulIslam-]

how can i add about 40 lacs data in one excel file from 25 excel file. please help me

[yvettemaynard]

Excel Magic Trick 1158: Get Millions of Rows of Data into PivotTable:  When trying to execute the above to pull 1.1M+ rows of data, I get the following error: Could not start the process that loads the data provider. Exception of type was thrown.  The system cannot find the file specifiedCan you assist with identifying what this error means, and how to overcome it?