how do you use pivot tables in excel 2013

#How do you use pivot tables in excel 2013 update#

This will convert the table into regular tabular data. It will show a prompt asking if you want to convert Table to Normal Range. Go to the data source (table), select any cell and Go to Table Tools Design –> Tools –> Convert to Range.This will create the first pivot table.In the Create Pivot Table dialogue box, you would notice that in the Table/Range field has the name of the table.With any cell selected in the table, Go to Insert –> Pivot Table.It will create a Table with the name Table1. In the Create Table dialogue box, click OK.Click anywhere in the data source and go to Insert –> Table (or you can use the keyboard shortcut – Control + T).Here are 3 ways to create duplicate pivot cache while creating pivot tables from the same data source: Creating Duplicate Pivot Cache (with the same Data Source)

Note: If you are using different data sources for different pivot tables, Excel would automatically generate separate Pivot Caches for it. The way around these limitations is to force Excel to create separate pivot cache for different pivot tables (while using the same data source).

When you insert a calculated field/item in one of the Pivot Table, it shows up in all the pivot tables that are sharing the pivot cache.

For example, if you group dates by months, this change will be reflected in all the pivot tables.

When you group fields in one of the pivot tables, it is applied to all the pivot tables using the same pivot cache.

When you refresh one pivot table, all the pivot tables linked to the same cache gets refreshed.

While a shared pivot cache improves pivot table functioning and memory usage, it suffers from the following limitations: This is helpful as it avoids pivot cache duplication and in turn, results in less memory usage and reduced file size. Sharing the Pivot Cacheįrom Excel 2007 onwards, if you already have a pivot table, and you create an additional pivot table using the same source data, Excel automatically shares the pivot cache (which means that both the pivot tables use the same pivot cache).

When you use large data sets to create a pivot table, the workbook file size increases significantly. Since it’s a replica of the source data, when you create a pivot table, a copy of that data gets stored in the Pivot Cache. One downside of pivot cache is that it increases the size of your workbook. This is also the reason you need to refresh the pivot table to reflect any changes made in the data set. While you think that you are directly linked to the source data, in reality, you access the pivot cache (and not the source data) when you make changes in the pivot table. Pivot Cache enables this fast functioning of a pivot table.

#How do you use pivot tables in excel 2013 update#

You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results. Even when you have thousands of rows of data, a pivot table is super fast in summarizing it. The reason a pivot cache gets generated is to optimize the pivot table functioning. While you can’t see it, it is a part of the workbook and is connected to the Pivot Table. When you make any changes in the Pivot Table, it does not use the data source, rather it uses the Pivot Cache. It is an object that holds a replica of the data source. Pivot Cache is something that automatically gets generated when you create a Pivot Table. Improving Performance while Working with Pivot Tables.Creating Duplicate Pivot Cache (with the same Data Source).