excel worksheet
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:
1. Create a Named range using Offset function.
2. Base the pivot table using the Named range created in Step 1.
Question 02:
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
You: 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 02:
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