From this point, until the pivot table appears in the worksheet, you are working in the PivotTable Wizard. The PivotTable and PivotChart Wizard?Step 1 of 3 dialog box opens, as shown in Figure 18.4. If the Office Assistant asks whether you want help with pivot tables, choose No. The data you need to enter in an Excel list.Ĭhoose Data, PivotTable and PivotChart Report. Now the active cell is within the list, and Excel knows that you want to use the data in the Excel list to create a pivot table. In Sheet2 in the Data Analysis workbook, type the data shown in the worksheet in Figure 18.3. Get ready to use the Data Analysis workbook you used in Hour 17, "Analyzing Your Data." To Do: Build a Pivot Table Perform the steps in the following To Do exercise to create a pivot table from an Excel list. This step tells Excel about the data you want to analyze with a pivot table. A diagram with the labels PAGE, COLUMN, ROW, and DATA appears, and you just drag field buttons onto the diagram. The wizard steps you through the process of creating a pivot table, and you get to see a basic breakdown of the data you have in your Excel list or database.
#LEARNING HOW TO USE PIVOT TABLES IN EXCEL 2003 SERIES#
If you want one field per column, and subtotals at the top, use the Outline layout.You can build a simple pivot table with the Data, PivotTable and PivotChart Report option, which displays a series of PivotTable Wizard dialog boxes.
However, because of its structure, the Tabular layout always shows subtotals at the bottom of a section. Pivot tables have an option to show subtotals at the top or bottom of a section. This fills in the data completely so you are ready to copy it to another location. Once you've applied the tabular layout, you can enable an option to repeat labels at the bottom of the Report Layout menu. The Tabular layout works best when you want to copy the results of a pivot table to another worksheet, so you can analyze the data further. The Tabular layout is similar to the Outline layout in that all row labels appear in their own column. The final layout option is the Tabular Layout, which is based on the original layout used by pivot tables up to Excel 2003. When I switch to the Outline layout, Customer and Product are placed into separate columns, and the structure of the pivot table resembles a traditional outline.īecause the outline layout doesn't group fields in a single column, each field can be filtered separately. One more thing to note about the Compact layout is that the field headings are pretty much useless, so you may want to hide these. When fields are grouped like this in the Compact layout, note that you can only filter on one field at a time.
If I add another field, say "Category", as the first row label, all three fields appear in the same column. So if you look closely at the first column, you'll notice that both the Customer and Product fields are included in the same column. Its primary purpose is to save horizontal space. The Compact layout is the newest layout in Excel, and is enabled by default for all new pivot tables. You probably noticed the pivot table changing when I applied a new layout, but you might be wondering what the differences are? Let's look at each layout more closely.įirst, the Compact layout. Once you have a pivot table, you can change layouts using the Report layout menu, on the Design tab of the pivot table tools ribbon.īy default, each new pivot table you create will use the Compact layout.īut you can easily switch to Outline layout, or to Tabular layout. In this video, we'll look at each layout. When you create a pivot table, you have your choice of three layouts. One of the more confusing aspects of Pivot Tables is layout options.
0 kommentar(er)
