Excel 2019 Bible

Part IV: Managing and Analyzing Data

Excel attempts to guess the range, based on the location of the active cell. If you’re creat-

ing a PivotTable from an external data source, you need to select that option and then click

Choose Connection to specify the data source.

If you’re creating a PivotTable from data in a worksheet, it’s a good idea first to create a table for the range. (Choose

Insert ➪ Tables ➪ Table.) Then, if you expand the table by adding new rows of data, the PivotTable will automatically

adjust to cover the whole table without the need to indicate the new data range manually.

Specifying the location for the PivotTable

Use the bottom section of the Create PivotTable dialog box to indicate the location for your

PivotTable. The default location is on a new worksheet, but you can specify any range on

any worksheet, including the worksheet that contains the data.

Click OK, and Excel creates an empty PivotTable and displays a PivotTable Fields task pane,

as shown in Figure 29.9.

The PivotTable Fields task pane is typically docked on the right side of the Excel window. Drag its title bar to move it

anywhere you like. Also, if you click a cell outside the PivotTable, the task pane is temporarily hidden.

Laying out the PivotTable

Next, set up the actual layout of the PivotTable. You can do so by using of the following

techniques:

■ (^) Drag the field names (at the top of the PivotTable Fields task pane) to one of the
four boxes at the bottom of the task pane.
■ (^) Place a check mark next to the item at the top of the PivotTable Fields task pane.
Excel places the field into one of the four boxes at the bottom. You can drag it to a
different box, if necessary.
■ Right-click a field name at the top of the PivotTable Fields task pane, and choose
its location from the shortcut menu (for example, Add to Row Labels).
The following steps create the PivotTable presented earlier in this chapter (see “A
PivotTable example”). For this example, drag the items from the top of the PivotTable Fields
task pane to the areas in the bottom of the PivotTable Fields task pane.

1. Drag the Amount field into the Values area. At this point, the PivotTable displays
   the total of all the values in the Amount column.


2. Drag the AcctType field into the Rows area. Now the PivotTable shows the total
   amount for each of the account types.