babewhe.blogg.se

PivotTable Helper is a free add-in for Excel 2000 and later that extends the capabilities of the PivotTable Auto-Format XL add-in. PivotTable AutoFormat XL records and applies custom formats for your PivotTables. Verify that the New Worksheet option is selected and click Finish. To duplicate the layout seen in Figure 4-29, drag the Week, Department, and Day fields (in that order) to the Row area, the Hour field to the Column area, and the Sales field to the Data area. The order of the field headers determines how Excel will group the PivotTable’s data. Then select the cells from your worksheet, click the Expand Dialog button at the right of the field, and click the Next button to display the third page of the PivotTable Report Wizard.)ĭrag the field headers to the desired positions in the PivotTable. (If the data range in the Range field is not correct, click the Collapse Dialog button next to the Range field. Verify that the proper data range appears in the Range field and click the Next button to display the third page of the PivotTable Report Wizard, as shown in Figure 4-28. Select the “Microsoft Excel list or database” option and click Next. Select any cell in your data list and choose Data → PivotTable Report.

It wouldn’t make any sense to have the two rows shown in Figure 4-27. In this example, the first four columns (Week, Day, Hour, and Department) combine to form a unique value, or key, for each row in the column. It’s absolutely vital that each row provides a unique data point. Each row in the data list corresponds to a cell in the Pivot-Table. The next row provides the sales total for the Week (1), the Day (1), the Hour (10), the Department (Cats), and so on, row by row. This row provides the sales total for the Week (1), the Day (1), the Hour (9), and the Department (Cats).

As an example, consider the data in row 2 of Figure 4-26 (the row just below the column headers). Please note that each row denotes a unique bit of information. That means you must have either the left edge of the worksheet or a blank column adjoining the list on either side, and you need at least one blank row at the bottom. There should be no extraneous data in cells neighboring the list.

There can be no blank rows and no blank columns in the list.