Going with the belief that you are aware of or have working knowledge on Pivot Tables, I've prepared a set of questions which might help you in your upcoming Excel Interviews. Please do not take it as the only guide to crack the interviews, instead treat this as refresher before you go for interview. As I had already mentioned in 3 things to remember before you go for an Interview, always remember technical attitude matters more than the technical knowledge.
This set of Oral questions on Excel Pivot Table will help both the Interviewer and the Interviewee (You). Do not forget to read a comprehensive collection of 50 Excel VBA Oral Interview Questions.
Question 01:
Interviewer: How do you provide Dynamic Range in 'Data Source' of Pivot Tables?
You: We follow the steps in following order to provide Dynamic Range in 'Data Source' of Pivot Tables:
- Create a Named range using Offset function.
- Base the pivot table using the Named range created in Step 1.
Interviewer: 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?
You: This happens because the newly added data is outside the range of Pivot table's underlying data. To cure this either provide dynamic range to the Pivot Table or manually update the pivot table's source data.
Question 03:
Interviewer: Is it possible to make Pivot Table using multiple sources of data?
You: Yes, if the multiple sources are different worksheets in the same workbook.
Question 04:
Interviewer: By any means can you repeat 'row headings' in the Pivot Table?
You: Yes, however this option is available in Excel 2010 or later.
Question 05:
Interviewer: Is it possible to display the text in the data area of Pivot Table?
You: No, it's not possible as we could only show text either in 'Row Labels' or 'Column Labels'. However, if needed, we can show count of text records.
Question 06:
Interviewer: Upon refreshing a Pivot Table, it always loses the formatting like the column width. How this can be corrected?
You: Format loss in Pivot Table 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.
Question 07:
Interviewer: Can you change the default summary function for data from COUNT to SUM?
You: Unfortunately, we can't change default settings of the data fields. However, by default, if any cell in the selected range is blank or text, it will be default to COUNT else to SUM.
Question 08:
Interviewer: When you link to a pivot table cell, a GETPIVOTDATA formula is created. How would you avoid this?
You: In Excel 2007 or later, by disabling the 'Generate GetPivotData' and in previous versions by manually giving the cell reference instead of using the mouse pointer to locate the cell.
Question 09:
Interviewer: How would you enable automatic refresh in Pivot Table upon opening the workbook without using macros?
You: This can be done from Pivot Table Options. Go to "Table Options" --> Data --> Select 'Refresh data when opening the file.'
Question 10:
Interviewer: How can you hide the error values in data field of Pivot Table?
You: 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.
Question 11:
Interviewer: Which 3 report formats for Pivot Tables are available in Excel 2007 or later?
You: Compact, Report and Tabular.
Question 12:
Interviewer: How can you disable automating sorting in Pivot Tables?
You: Go to 'More Sort Options' after you right click on 'Pivot Table' and choose 'Sort' menu. Then click on 'More Options' and finally uncheck the 'Sort automatically when the report is created."
Question 13:
Interviewer: Which event do you use to check if a Pivot Table is modified?
You: Event 'PivotTableUpdate' in worksheet containing that 'Pivot Table'.
Question 14:
Interviewer:Which option is used to add column(s) in Pivot Tables to compute the values in run-time?
For eg. if the underlying source table contains cost price and sales price, how would you compute 'Profit'.
You:We can use 'Calculated Field' to dynamically insert formulated values.
Question 15:
Interviewer: How will you check the memory consumed by your PivotTable in Kilobytes?
You: You can display the memory used by a pivot cache, by using the following VBA properties of Pivot Table. PivotCaches.MemoryUsed - It returns the total memory used in bytes. Divide the result by 1024 to have it in Kilobytes.
Now, do not forget to read and GOOD LUCK for the interview:
- 3 things to remember before an Excel Interview
- 50 Excel VBA Interview Questions
- 15 Essential Logical Puzzles (VBA)
Comments
Thank you