Pivot Table Interview Questions and answers in Excel. These top interview questions helps analysts to go through all Pivot table related questions and answers.
Excel Pivot Table Interview Questions:
Let us see all the Pivot Table Interview Questions.
What is a Pivot Table?
Pivot Table is a powerful built-in special tool in MS Office Excel. The We create Pivot Table from Range or Table or External Data Source, or Data Model. Pivot Table is an object. It is a member of Pivot Tables collection. It consists of rows, columns and data fields.
When we use Pivot Table?
We Use pivot table to analyse large amounts of data sets and quickly summarize large data using aggregate functions. The aggregate functions are sum, Average, Count, Maximum, Minimum, Median, Mode, etc.
We apply a filter to the pivot table to view required summarized data, create reports and key insights. It helps the team or higher-level management to take decisions in the organisations.
What are the different sections in a Pivot Table?
The Pivot Table has 4 sections.
- Filter Area: It is an optional area. It is used to further drill down values from the dataset.
- Row Area: The left heading of the values area.
- Column Area: The top heading of the values area.
- Value Area: This is the area where the values are present.
How to refresh Pivot Table in Excel?
We can refresh Pivot Table in two ways.
1st Method: Select any cell in the Pivot Table, right click. Now click on Refresh option from the available list.
2nd Method: Here are the step by step instructions.
- Select Pivot table.
- Now you can see the Pivot Table Analyze tab on the Excel ribbon.
- Select the Pivot Table Analyze tab.
- Click on Refresh button from the data group.
- Now, we can see changes in the Pivot Table after .
What is the Pivot Cache?
Pivot Cache is created automatically when you create a Pivot Table. It replicates or clones the source data and connected to the Pivot Table. When you make any changes to the Pivot Table, it does not use the data source, instead uses the Pivot Cache. It helps to optimize functioning of the Pivot Table and update results instantly.
Can we create pivot table from multiple tables?
Yes, we can create single pivot table from multiple tables from multiple sources are different worksheets in the same workbook. But there should be connection between tables.
How to use calculated field in Pivot Table?
Pivot Table calculated field” is an option to create our own custom calculations that can refer to other fields in the data set for our reports.
How do you use dynamic data range to create pivot table?
Set up the data range as a dynamic range by using one of the options below.
- Named Table
- Formula-Based Dynamic Range
If you add either new rows or new columns to the pivot table source data, the pivot table is not updated even when you click on ‘Refresh Data’. Why and what is the solution?
When the newly added data is outside the range of Pivot table’s underlying data. To solve this either provide dynamic range to the Pivot Table or manually update the pivot table’s source data.
Can we repeat row headings in the Pivot Table?
Yes, this option is available in only Excel 2010 or later version.
Is it possible to display text in the Value or data area of Pivot Table?
No, it’s not possible. We could only show text either in ‘Row heading’ or ‘Column heading’. We can show only text records count value area.
How to retain formatting like the column width, color etc upon Pivot table refresh?
It can be stopped by simply changing the pivot table options. Go to “Pivot Table Options” and turn on the “Enable Preserve Formatting” and disable the “Auto Format” option.
Can you change the default data summary function like COUNT to SUM?
We can’t change default settings of the data fields. Default COUNT or SUM summary function used in the data area.
How would you avoid created GETPIVOTDATA formula?
By disabling the ‘Generate GetPivotData’ option. It is applicable only in 2007 or later version. Manually giving the cell reference instead of using the mouse pointer to locate the cell in the earlier version.
How would you enable automatic refresh in Pivot Table upon opening the workbook without using macros?
This can be done from Pivot Table Options. Go to “Table Options” –> Data Group –> Select ‘Refresh data when opening the file.’
How can you hide the error values in data field of Pivot Table?
This can be done from Pivot Table Options. Go to Table Options –> Layout & Format –> Enable For error values show and provide the value to be shown for error values. Leave it empty for Null values.
Which 3 report formats for Pivot Tables are available in Excel 2007 or later?
1. Compact Layout: Available from Excel 2010 on-words. This layout optimizes for readability and Keeps related data in one column. But further analysis is difficult.
2. Outline Layout: Includes Field headers in each column. It can repeat all item labels and reuse the data of the Pivot Table to a new location for further analysis in classic Pivot Table style. It takes Takes too much horizontal space.
3. Tabular format: It includes field headers in each column. It can Repeat all item labels and see all data in a traditional table format used in Pivot Tables since their invention. We can reuse the data of the Pivot Table to a new location for further analysis. It takes too much horizontal space. Subtotals can never appear at the top of the group.
How can you disable automating sorting in Pivot Tables?
Go to ‘More Sort Options’ after you right click on ‘Pivot Table’ and choose ‘Sort’ menu. Then click on ‘More Options’ and finally UN check the ‘Sort automatically when the report is created.
Which event do you use to check if a Pivot Table is modified?
Event ‘PivotTableUpdate’ in worksheet containing that ‘Pivot Table’.
Which option is used to add columns in Pivot Tables to compute the values in run-time?
We can use ‘Calculated Field’ to dynamically insert formulated values.
How will you check the memory consumed by your Pivot Table in Kilobytes using VBA?
We can display the memory used by a pivot cache, we use WePivotCaches.MemoryUsed VBA property of Pivot Table. It returns the total memory used in bytes. We divide the result by 1024 to get final result in Kilobytes.
What is a Pivot Chart?
Pivot charts are one of the best ways to present and visualize the report. These visualization reports help us in understanding and analyzing the data in a simpler way. Pivot charts in Excel give us a visual representation of data in various different ways.
What are slicers
Slicers in Excel are software filters used along with excel tables or pivot tables over a large amount of data. Not just filtering out the data, but slicers also help you with an easy understanding of the information being extracted and displayed on the screen. Microsoft Excel Slicers is compatible with Windows and Macintosh operating systems.
What is the difference between Pivot Table and Pivot Chart?
Pivot Table provides us a way to summarize large data in a grid-like matrix. You can choose the fields you wish to use in the table for rows and columns.A pivot table can make slice, summarize and displays meaningful results from the data. Usually, in excel, after summarizing the data, we apply graphs or charts to present the data graphically to tell the story visually. The pivot table does not require your special charting techniques, build its own chart using its own data.
The pivot chart provides us with a graphical representation of the pivot table. You can choose from multiple layout and chart types.
This chart also summarizes the data. You can create both a pivot table and chart for a data source and handle them simultaneously. The changes made in the pivot table will reflect in the chart and vice versa. Pivot charts work directly with the pivot table and visualize the data in the most effective way.
Watch at Youtube:
You can also watch at youtube.
Pivot Table:
Let’s see complete tutorial of the Pivot table.
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog
