How to pivot with huge dataset

[brijendraydv]

Dear Experts,

I have huge data set containing almost 1 million records and when i try to create pivot table out of it it throws me an error saying cannot complete the task with available resources.How to deal such situations as i want to show both data and pivot out of it.
I'm using excel 2013 and also tried using power pivot but still throws memory issues.

Thanks for your support.

Thanks,

 

[bobhc]

The available resources could mean anything but most probably your computer resources, ie ram. Close all other programs and try again you are asking your machine and excel to deal with a huge amount of data.

 

[Deepak]

In addition fo bobhc suggestions.

NOTE Adding tables to the Data Model increases the file size. If you don’t plan to create complex Data Model relationships using many data sources and data types in your workbook, uncheck the Add this data to the Data Model box when you import or create tables, pivot tables, or data connections.


https://support.office.com/en-za/ar...6ab-9f5e-14a2833b6a70?ui=en-US&rs=en-ZA&ad=ZA

 

[jeffreyweir]

How many columns in your dataset? Do you need all of them? My suggestion: extract a subset of the records keeping just the columns you need. And if you're going to be aggregating up dates from say daily or transactional to monthly, perhaps work out how you can pre-aggregate the data that you feed your pivot. That will significantly reduce the size of the PivotCache.