Excel spreadsheet help with Pivot Tables

Useful as they may be, when a person attempts to add calculated items to a Pivot Table, Excel may slow down and may even hang when there are thousands of records in the specific data set. Excel spreadsheet help with Pivot Tables is provided below to show you how to deal with this.

First the slowdown occurs because the Pivot Table takes up a lot of resources as the data needs to be analysed and cut before the table can be created. The time taken to do this depends on the amount of data in the PivotTable.

Excel spreadsheet help with Pivot Tables to speed things up

You need to change the way that the program works with the data to generate the Pivot Table. The secret is in modification of the settings as this will ensure a faster response from the program. It is not a fool proof solution, but is a starting point. Save memory by using a closed original data source or if possible one, rather than multiple data sources when you want to create several Pivot Tables. This will save memory and speed up the response time.

The problem may also occur when you use multiple consolidation ranges to be the data source of your Pivot Table. When the data is in an active sheet there will be two copies of that data in the memory when the program attempts to generate the Pivot Table. You need to minimize the usage of memory and thus need to close the workbook that contains the original data. Create the Pivot Table in a separate rather than the same workbook as this will ensure that only cached data will be kept in memory.

For more Excel spreadsheet help, you can visit our training pages where numerous topics are addressed. You will also find a step by step Excel spreadsheet help video for Pivot Tables.