Solutions to Pivot Table Questions?
From ‘what is a pivot table’ to ‘can you explain the Pivot table cache mechanism’, these are the type of questions on Pivot tables that students of our online Excel Course “Workplace Excel” have asked us over the years. In this article we are going to provide solutions to the 20 most common Pivot Table questions
What is a Pivot Table ?
A pivot table is a special Excel tool that allows you to summarize and explore data interactively. It is it without doubt the most powerful analytical tool in Excel. Below are the most common questions you might encounter when using Pivot tables. Use it as an additional to your Pivot table Tutorial.
How to add new data to your Pivot table?
You want to add new records to your dataset and have your pivot table or pivot tables update with these new records. Remember your Pivot table will not update automatically unless you have made your dataset into an Excel Table.
Follow the steps (see Figure below)
- Paste the records below your original data set.
- Click on any cell on the PivotTable.
- Follow links PivotTable Tools > Options > Change Data Source > Change Data Source.
- Define the new range. you can do so by simply selecting the data range (including the newly added data rows) in the sheet.
- Click OK
- Click the Refresh button to update the Pivot Table with this newly added data or click Refresh All button if you have more than one pivot table.
How can I change pivot table to different layouts?
Pivot tables are used for summarizing data. The simplest form of pivot table may summarize the data using two fields only. For example in Figure 1, only two fields are used in Pivot Table. i.e Branch and Sale.
If we add more fields in the pivot table by checking the Fields in Pivot table field list Dialog Box ( Figure 2), then these additional fields start forming nested data in the columns. This nested data in columns can be expanded/hidden using buttons (Figure 2)
Pivot Table Examples:
Figure 1 shows a Pivot Table with two fields (Branch and Sale)
Figure 2 shows a Pivot Table with three fields (Branch, Sale and Item)
Figure 3 shows a Pivot Table with four fields ( Branch, Sale, Item and Customer).
HOW TO CHANGE THE Pivot Table LAYOUT
This layout of pivot table may not be very useful at times. But you may want to change the layout of Pivot table.
- The layout of pivot table can be very conveniently changed by simply dragging/moving the fields from the Pivot Table Field List- Dialog box to different Boxes (i.e Columns Label, Row Labels, ∑ Values).
- The fields may also be drag/moved from one Box to another.
- Furthermore you can also swap the positions of the Fields within their own box to change the layout of the Pivot Table.(Figure 6)
How can I convert my pivot table to ordinary data ?
You can convert your Pivot Table to Ordinary Data by simply using the copy /paste Value. Follow steps below (see Figure Below)
- Select the Range in Pivot table you want to convert.
- Copy using CTRL C
- Paste using the links HOME > PASTE > PASTE VALUES
Important Note: Use Paste Values only, Otherwise the entire Pivot table will be copied as another pivot table, if you use simple paste by CTRL V.
Where did the Pivot table get that number from ?
You can see the details of any entry of a Pivot Table, by ‘double clicking‘ that particular entry, or do following ( see figure 7.1)
- Right Click the entry in Pivot Table for which you want to see details
- Select Show Details
- The details of that particular entry are automatically inserted in a new sheet.
- The new sheet is inserted on the left of your current sheet. ( Figure 7.2
Note that any changes in entries must be made in original data and not in the newly inserted sheet. As the newly inserted sheet is copied from the original data but is not hot linked to original data.
How can I summarize my Pivot table data using three conditions?
It is possible to add further conditions in the Pivot Table Summary of your data.
- See the Figure 4.1. You can see the data and a Pivot Table which is summarizing the sales of Items in each outlet/branch.
- Suppose you want to add the customer details in you summary; you can do so by simply dragging the Customers names Field in the Row Label Box. Figure 4.2
- In Figure 4.2, you can see that there are two levels of the Rows in the Pivot Table Summary. The Level 1 displays the Items. Whereas the Level 2 displays the customer’s name. You can hide/expand this second sub-level using the buttons as shown in fig 4.2
How to create a unique list with a pivot table?
It is very easy and quick to create a unique list using a pivot table.
Just follow these steps.
1. Click into your data and Select insert, Pivot Table. Click OK.
2. Click your customer field in the pivot table field list dialogue or drag it to the Row section.
3. Now just copy the unique list of data from column A and then Paste Values to put them where required.
How to do calculations outside your Pivot Table that refer to values in the pivot table?
Consider the pivot table below.
If you click on cell C5 and then look in the formula bar , you will see the GETPIVOTDATA Function. This Function can often be volatile , particularly if you change your pivot table .There are two ways to handle this situation.
The most effective way to calculate a value from a pivot table is to click in any individual cell and type the formula =(c5/300)*100. Do not use your mouse or arrow keys.
The other method is to permanently disable the option of GETPIVOTDATA.
To do this Click File >> Options>> Formulas. In the check box Use GetPivotData Functions For PivotTable References, turn it off.
How to create two Pivot Tables that do not share the same Pivot Table cache ?
A Pivot Table cache is a virtual memory where the data of the worksheet is stored when Pivot Tables are created.
Since it is a virtual memory, access to cache is quick . This is what makes Pivot Table fast. To save memory, Excel also allows different pivot tables to share same cache.
1. Create the first Pivot Table
2. While creating second pivot table, select one cell in original data.
3. Press Alt+D then P. Step 1 of the old pivot table wizard will be displayed.
4. Click Next in step 1.
5. Ensure the validity of data range in step 2.
6. Click Next and then Click No.
7. In Step 3, choose the location for pivot table. Click Finish.
Now you may group the second pivot table without affecting first pivot table.
Are 2007- 2010- 2013 Pivot Tables compatible with previous versions?
Pivot Table Compatibility:
Follow the steps below to make 2007/ 2010/2013 Pivot tables compatible with previous Pivot Table versions?
1. Create the pivot table in compatibility mode if you want other users with older versions to use it. Compatibility mode allows a program that is created and saved in latest version of Excel, to be run and manipulated in the earlier versions.
Option One: To use a Pivot table in Excel 2003 and Excel 2007/2010/2013 .Create the pivot table in Excel 2003 and save as 2003 .Use it in 2007/2010/2013 and save it back as an Excel 2003 file.
Option Two: Open Data in 2010. Save it as an Excel 97-2003 file. Close and Re-open the data set. Now you can create the Pivot Table in comparability Mode.
How to create a Pivot Table that shows the Min, Max, Average and count for my data?
To find the Minimum, Maximum, Average, and Count for the data in a Pivot table, follow the steps below?
1. Select one cell in the pivot table.
2. In the option ribbon tab, Click Field Setting.
3. Select any function from the eleven given.
When you select a function, for example the Max function, then the Custom name becomes Max of Earning.
4. While using Excel 2010/2013 you may use the Sum, Average,Count or Max functions by clicking on the Summarize values in the option ribbon tab.
How to use the ‘show value as’ feature in Pivot tables?
You can use the ‘show value as features’ in Pivot tables, to view your data from different perspective.
1. Consider the pivot table above.
2. Select a cell in each column and click show value as. In Excel 2007 use field settings then show value as.
3. For columns like above use % of column Total Setting. On using this function Excel will output all the values in each column as a percentage of the total for the column.
4. For columns like above the % percentage difference form is used. On using this,Excel will show values as the percentage difference from the value of the Base item in the Base field.
5. Parent Row Total and the other “Parent” calculations express the amount as a percentage of the total amount. This is not valid for older versions of Excel.
6. On using % Running total Excel will show the value for successive items in the Base field as a running total.
How to create a report/Pivot table for every customer/product instantly?
Using Report Filters may be a tedious option if you want to generate a report for each field in the data. i.e you may have to filter for each customer or product and then generate and print a report for each customer or product.
Fortunately you can automate this process with Excel’s Pivot tables. See the example below.
- Figure 29.1 shows sample data and a corresponding pivot table.
- Suppose you want to generate a filtered report for every customer in the data.
- Drag the Customer field from the ROW LABELS box to the REPORT FILTER box. (fig 29.2)
- Follow the links OPTIONS > Options >Show Report Filter Pages (Fig 29.2)
- Five new sheets will be inserted in the Excel workbook for each customer.
How to create charts in pivot tables?
To insert a Pivot Chart, follow the steps below.
- Follow Links Insert > Pivot Table Drop down > Pivot Chat (Fig 30.1)
- Define the Data Range ( A1:D20) and Target cell (F2) for the Pivot Table.
- A Blank Pivot Chart and Blank Pivot Table will appear with their dialog boxes.
- Select the Fields Customer and Sale in the pivot Chart (fig 30.2)
- You can see that the pivot Chart is ready for use.
- You can play with the chart by selecting different fields in Pivot Table Field List.
What does the Report Filter area of the Pivot Table do?
The Report Filter area of the Pivot table dialog box is used for adding filters to your Pivot Table. See the example below.
- Figure 28.1 shows our sample data and a corresponding pivot table.
- Suppose you want to make a report of certain item only. To do so, Drag the Item from ROW LABELS box to the REPORT FILTER box. (Fig 28.2)
- You can see an additional filter added at the top of your Pivot Table
- You can use this filter to create selected/filtered reports.
How to see the top items in a Pivot Table?
You can see the Top 5 or any other top/bottom filtered values in your Pivot Table ( for example Top 3,10,20… or Bottom 3,5,10,20 … etc). See the example below for details.
- Figure 25.1 shows our sample data and a corresponding pivot table derived from this data.
- To get the top 5 list, Click the drop down button in the cell F2. Follow the links VALUE FILTERS>TOP 10… (Fig 25.2)
- A Top 10 Filter Dialog Box will appear. Use the Spin Button to reduce the value of ten to 5.
- Click OK
- The Top 5 List can be seen in Figure 25.3
How to sort data in a pivot table?
Data can be sorted quiet easily in a Pivot Table. See our example Below
- See Figure 22.1, with our sample data and a pivot table derived from this data
- To sort the data click the drop down button in the cell F1. you can see different options for sorting (Fig 22.2)
- Select BRANCH in the FIELD option.
- You can change different Sort Options as per your requirement
- Click OK
- You can see in Fig 22.3 that the Pivot Table Branch entries have been sorted in alphabetically.
In Pivot tables how to replace blanks with zeros ?
You can replace blanks in Excel Pivot Tables with zeros or any other character. Here’s how?
- You can see in Figure 12.1; sample data which has blank entries in both the original data and the Pivot Table
- To replace the Pivot Table blanks with zeros, follow the Steps (also see Figure 12.1)
- Click anywhere on the Pivot Table to display Pivot Table Tools on the ribbon
- Follow the links (Figure 12.1) OPTIONS > Options > options
- Check the box: For Empty Cells Show and
- Enter zero ‘0’ in: For Empty Cells Show
- Click OK
You can see in Figure 12.2 that the blanks in Pivot Table are replaced with zeros.
Note that the replacement has not been made in the Original Data, it is only in the Pivot table