Using Data Fields: Changing the Total Field Name in Font

Generator PDF417 in Font Using Data Fields: Changing the Total Field Name

When your pivot table contains multiple data fields, the data field names are displayed at the top of the data columns. When there are no column fields and only one data field, the data field name appears at the top left of the pivot table body, and the word Total appears at the top of the data column (see Figure 6-2).

Figure 6-2. The pivot table with one data field has Total at the top of its data column. The pivot table with multiple data fields has field names at the top of its data columns.

You can t change this behavior, and the text of the Total heading label can t be changed. You could format the label with white font color so that it doesn t appear.

You added a second field to the data area, and now the data fields are listed vertically with the row fields. You want them arranged horizontally, with the data field names across the top of the pivot table as column headings.

To arrange the fields horizontally, drag the Data field button onto the cell that contains the word Total (see Figure 6-3.). Or, if you don t see a cell that contains the word Total, drag the Data field button into the column area of the pivot table.

You hid a field that was in the data area, and now you d like to show it again, but it s missing from the Data field dropdown list.

When data fields are hidden, they re removed from the pivot table layout. You can drag the field back to the data area from the field list.

When you remove a checkmark from the dropdown list in a row field or column field, you re hiding an item in that field. Later, you can show the item again by adding a checkmark to the item. When you remove a checkmark from the dropdown list in the Data button, you re hiding a data field, not an item. When any field is hidden, it s removed from the pivot table layout. To show the hidden field again, you can drag it back to the pivot table from the field list.

Your source data contains several columns of numbers, such as Quantity, Total Sale, and Deposit Amount. These numbers get summed as zero in the data fields instead of showing the correct totals.

If you copied the data from another program, such as Microsoft Access, Excel may be seeing the data as text. There are several techniques that you can use to convert the text to numbers in the source data so that they can be summed correctly in the pivot table.

Converting with Paste Special 1. Select a blank cell, and on the Excel Standard toolbar, click the Copy button. 2. Select the cells in the source data that contain the numbers. 3. On the Excel Worksheet menu bar, choose Edit Paste Special. 4. Click Add, then click OK.

Converting with Text to Columns 1. Select the cells in one column of the source data that contain the numbers. 2. On the Excel Worksheet menu bar, choose Data Text to Columns. 3. In Step 1 of the Convert Text to Columns Wizard, click Finish.

Converting Dates with Replace All If dates are formatted with slashes, such as 5/5/06, you can convert them to real dates by replacing the slashes: 1. Select the cells in the source data that contain the dates. 2. On the Excel Worksheet menu bar, choose Edit Replace. 3. For Find what, type a forward slash: /. 4. For Replace with, type a forward slash: /. 5. Click Replace All, then click OK to confirm the replacements. 6. Click Close to close the Find and Replace dialog box.