Formatting Most formatting—including chart elements that you add, layout, and style—is preserved
when you refresh a PivotChart. However, trendlines, data labels, error bars, and other changes to data sets are
not preserved. Standard charts do not lose this formatting once it is applied.
Although you cannot directly resize the data labels in a PivotChart, you can increase the text font size to
effectively resize the labels.
Creating a PivotTable or PivotChart from worksheet data
You can use data from an Excel worksheet as the basis for a PivotTable or PivotChart. The data should be in
list format, with column labels in the first row, which Excel will use for Field Names. Each cell in
subsequent rows should contain data appropriate to its column heading, and you shouldn't mix data types in
the same column. For instance, you shouldn't mix currency values and dates in the same column.
Additionally, there shouldn't be any blank rows or columns within the data range.
Excel tables Excel tables are already in list format and are good candidates for PivotTable source data.
When you refresh the PivotTable, new and updated data from the Excel table is automatically included in the
refresh operation.
Using a dynamic named range To make a PivotTable easier to update, you can
create a dynamic named
range, and use that name as the PivotTable's data source. If the named range expands to include more data,
refreshing the PivotTable will include the new data.
Including totals Excel automatically creates subtotals and grand totals in a PivotTable. If the source data
contains automatic subtotals and grand totals that you created by using the Subtotals command in
the Outline group on the Data tab, use that same command to remove the subtotals and grand totals before
you create the PivotTable.
PivotTable Options
Use the PivotTable Options dialog box to control various settings for a PivotTable.
Name Displays the PivotTable name. To change the name, click the text in the box and edit the name.
Layout & Format
Merge and center cells with labels Select to merge cells for outer row and column items so that you can
center the items horizontally and vertically. Clear to left-justify items in outer row and column fields at the
top of the item group.
When in compact form indent row labels To indent rows in the row labels area when the PivotTable is
in compact format, select an indentation level of 0 to 127.
Display fields in report filter area Select Down, Then Over to first display fields in the report filter area
from the top to the bottom, as fields are added to it, before taking up another column. Select Over, Then
Down to first display fields in the report filter area from left to right, as fields are added to it, before taking
up another row.
Report filter fields per column Type or select the number of fields to display before taking up another
column or row based on the setting of Display fields in report filter area.
Format section
For error values show Select this check box, and then type text, such as "Invalid", that you want to
display in the cell instead of an error message. Clear this check box to display the error message.