Now, let’s try and answer the questions by using the Pivot Table we have created.Ĭlick hereto download the sample data and follow along.
In the example below, Columns area contains the months (highlighted in red):įilters area is an optional filter that you can use to further drill down in the data set.įor example, if you only want to see the sales for Multiline retailers, you can select that option from the drop down (highlighted in the image below), and the Pivot Table would update with the data for Multiline retailers only.
The headings at the top of the Values area makes the Columns area. In the example below, the Rows area contains the regions (highlighted in red): The headings to the left of the Values area makes the Rows area. In this example, it has the total sales in each month for the four regions. The area highlighted in orange is the Values Area.
The Values Area is what holds the calculations/values.īased on the data set shown at the beginning of the tutorial, if you quickly want to calculate total sales by region in each month, you can get a pivot table as shown below (we’ll see how to create this later in the tutorial). Read More: What is Pivot Cache and How to Best Use It.
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.
Note: One downside of pivot cache is that it increases the size of your workbook. 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 the data. The reason a pivot cache gets generated is to optimize the pivot table functioning. When you create different views using a Pivot Table, Excel does not go back to the data source, rather it uses the Pivot Cache to quickly analyze the data and give you the summary/results. Excel takes a snapshot of the data and stores it in its memory. To use a Pivot Table efficiently, it’s important to know the components that create a pivot table.Īs soon as you create a Pivot Table using the data, something happens in the backend. Now before we jump into analyzing data using this Pivot Table, let’s understand what are the nuts and bolts that make an Excel Pivot Table. All you’d see is the Pivot Table name and a single line instruction on the left, and Pivot Table Fields on the right. While the Pivot Table has been created, you’d see no data in it. Else, a new worksheet is created with the Pivot Table.Īs soon as you click OK, a new worksheet is created with the Pivot Table in it.