Tips for setting fields in Excel 2007 tables

Excel is often used in our work life of an office software, mastered the Excel table in the field settings skills can greatly improve your productivity.

Use the Excel 2007 Field Settings dialog box to control the various formatting, printing, subtotals, and filter settings for fields in a PivotTable report. The source name displays the name of the field in the data source.

The custom name displays the current field name in the PivotTable report, or the source name if no custom name exists. To change the custom name, click the text in the box and edit the name.

Layout and printing

The Layout section

Show item labels as outline Click this option to display the field items in outline form.

Display labels for the next field in the same column (compact form) Select or clear this option to show or hide labels for the next field in the same column or compact form. It toggles between turning on or off compact forms for the selected field. This check box is enabled only if you selected show items as outline.

Display subtotal selection at the top of each group or clear this option to show or hide subtotals at the top of each group. This check box is enabled only if you selected show items as outline.

Display item labels in tabular format Click this option to display the field items in a tabular format. This setting affects only the fields in the row label area.

Insert a blank line after each item Select this option to insert a blank line after each item to provide greater spacing for items such as subtotals. When this check box is cleared, field items with no blank lines are displayed.

Note You can apply character and cell formatting to blank lines, but you cannot enter data in them.

Show empty data Items Select or clear this option to show or hide empty data items.

Note This check box is not available for OLAP data sources.

The print section

Insert page breaks after each item Select or clear this option to insert or not insert page breaks after each item when you print the PivotTable report.

Subtotals and filters

Subtotals section

Select one of the following options:

Auto: Use default functions for fields.

None: Displays fields with no subtotals.

Custom: Allows you to select one of the following functions as a subtotal.

Function description

The sum of the sum value. It is the default function for numeric values.

The number of Count values. The Count summary function works the same way as the COUNTA worksheet function. Count is the default function for values other than numbers.

The average of the Average value.

Max Max value.

Min min value.

The product of the product value.

Number of Count nums values. The Count Nums summary function works the same way as the Count worksheet function.

StDev estimates of the overall standard deviation, where the sample is a subset of the entire population.

STDEVP the overall standard deviation, where the overall is all the values to be summarized.

Estimated value of the Var population variance, where the sample is a subset of the entire population.

Varp the variance of the population, where the overall is all the values to be summarized.

Note Custom functions cannot be used with OLAP data sources.

The filter section

Include a new item in a manual filter Select or clear this option to include or exclude any new items in the applied filter in an Excel 2007 PivotTable report.