Excel pivot tables Interview Questions & Answers

Question 1. How Do You Provide Dynamic Range In 'data Source' Of Pivot Tables?

Answer :

We follow the steps in following order to provide Dynamic Range in 'Data Source' of Pivot Tables:

1. Create a Named range using Offset function.
2. Base the pivot table using the Named range created in Step 1.

Question 2. 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?

Answer :

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 3. Is It Possible To Make Pivot Table Using Multiple Sources Of Data?

Answer :

Yes, if the multiple sources are different worksheets in the same workbook.

Question 4. By Any Means Can You Repeat 'row Headings' In The Pivot Table?

Answer :

Yes, however this option is available in Excel 2010 or later.

Question 5. Is It Possible To Display The Text In The Data Area Of Pivot Table?

Answer :

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 6. Upon Refreshing A Pivot Table, It Always Loses The Formatting Like The Column Width. How This Can Be Corrected?

Answer :

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 7. Can You Change The Default Summary Function For Data From Count To Sum?

Answer :

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 8. When You Link To A Pivot Table Cell, A Getpivotdata Formula Is Created. How Would You Avoid This?

Answer :

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 9. How Would You Enable Automatic Refresh In Pivot Table Upon Opening The Workbook Without Using Macros?

Answer :

This can be done from Pivot Table Options. Go to "Table Options" --> Data --> Select 'Refresh data when opening the file.

Question 10. How Can You Hide The Error Values In Data Field Of Pivot Table?

Answer :

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. Which 3 Report Formats For Pivot Tables Are Available In Excel 2007 Or Later?

Answer :

Compact, Report and Tabular.

Question 12. How Can You Disable Automating Sorting In Pivot Tables?

Answer :

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. Which Event Do You Use To Check If A Pivot Table Is Modified?

Answer :

Event 'PivotTableUpdate' in worksheet containing that 'Pivot Table'.

Question 14. 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'.

Answer :

We can use 'Calculated Field' to dynamically insert formulated values.

Question 15. How Will You Check The Memory Consumed By Your Pivot Table In Kilobytes?

Answer :

You can display the memory used by a pivot cache, by using the following VBA properties of Pivot Table.

Pivot Cache.Memory Used - It returns the total memory used in bytes. Divide the result by 1024 to have it in Kilobytes.