Using Excel Data from the Same Workbook

Perhaps the most common way to create a PivotTable is by basing it on data that already exist in an Excel workbook. The data can be in the same workbook as the PivotTable; this technique was used in Chapter 1. The data can also be in a separate workbook. Things are at their simplest when you are creating a PivotTable in the workbook in which the data are located. The data should be organized as a standard Excel list, or table, as follows:

• The first row contains the field or column names.
• The second and subsequent rows contain the data.
• There are no blank rows, although individual blank cells may be present.

If the cell pointer is anywhere in the data table when you open the Create PivotTable dialog box, Excel automatically determines the address of the entire table and enters it in the Table/Range field of the dialog box. If you want to explicitly tell Excel where the input data range is located, you can do one of the following:

• Select the data before you display the dialog box. The address of the data will be entered automatically in the appropriate place in the dialog box.
• Type the address of the data into the Table/Range field of the dialog box (see Figure 2-1).
• Use the Select button in the dialog box to select the data range

Figure 2-1: Specifying the data range in the Create PivotTable dialog box.

Actually, there is a fourth and preferred way to tell Excel where the data are—create a named range for the data. Using named ranges is more convenient than typing the address or selecting the data each time you want to refer to them. Named ranges also provide an advantage in that if you expand the range, perhaps to include additional data, the PivotTable report will automatically include the new data when it is refreshed. Here’s how to create a named range:

1. Select the data range.
2. Click the Define Name button in the Defined Names section of the Formulas ribbon. The New Name dialog box is displayed (see Figure 2-2).
3. Type the name for the range in the Name field. You should use something descriptive such as SalesData or SurveyResults. It’s best to avoid spaces, too; use an underscore if needed to separate words.
4. Click OK.

Figure 2-2: Defining a named range.

Then, when you are creating a PivotTable, simply enter the range name in the Table/Range field, as shown in Figure 2-3 for the range Name SalesData. Of course, if you enter a name that does not exist, it will not work. Excel displays an error message when you click OK. You’ll have to correct the range name you entered or use one of the other techniques to specify the source data range for your PivotTable.

Figure 2-3: Specifying the data range by entering a range name in the Create PivotTable dialog box.

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website