Creating Pivot Tables
A pivot table is an interactive worksheet table. You can create a pivot table to quickly summarize, organize, and analyze large amounts of data. You will also add fields to and delete fields from a pivot table. The data that you use to create a pivot table is called the source data. Source data can come from Excel’s data, or an external database file.
E.g. The source data for the pivot table in this workbook is contained in the Sales worksheet. There is a large amount of data in this worksheet. The data represents sales projections for the Bem T. Group’s technology journals. This worksheet has nearly 1,200 rows of information. Getting specific information about a particular year, month, or product could be quite involved if your only options were to cut, paste, and write formulas. Fortunately, these are not your only options. Activate the Pivot Table sheet. This pivot table summarizes the data from the Sales sheet. The pivot table very clearly shows earned income for each month and year, for all the products. Excel also provides a special toolbar for manipulating pivot tables. To specify what portions of the source data to use in the table, you choose fields and items. Fields are categories of data, and items are subcategories in a field. For example, 1988 would be an item in a field called Year.
There are two types of fields: row/column/page fields, and data fields. Row/column/page fields usually contain a limited set of text values; for example, Year contains 1998, 1999, and 2000. Data fields usually contain numeric data used for summarizing, for example, sales amounts. The data presentation is called a pivot table because you can rotate the row and column headings to create different data configurations. You can also move the page data into the table to create different configurations as well.
E.g. creating a pivot table. Choose the cell that contains the name Year, choose Data, Pivot Table And PivotChart Report. (The PivotTable And PivotChart Wizard – step 1 of 3 will display and you need to confirm that the source data is an Excel list. This option is selected by default). Click on Next. In step 2 of 3, the wizard will requires you to specify the location of the data. Because you selected cell A4 before starting the wizard, Excel automatically selected the range of contiguous cells surrounding the active cell. Click on Next. In step 3 0f 3, the wizard will ask you whether you want the pivot table in a new or existing worksheet. The default is a new worksheet. Click on Finish. Notice that the field names from the list all appear at the bottom of the toolbar. You drag these onto the various areas of the pivot table grid to create the pivot table. Callouts have been added to explain the purpose of the various areas, but the best way to understand a pivot table is to build one.
Click on the Simulation button to observe the Year field being dragged from the PivotTable toolbar to the Drop Column Here area of the pivot table grid. You can also simply move the copies field out by deleting it from the table, and then move another in to add it. Point to the Sum of Copies field in cell A3, and drag it to cell A2. Observe the mouse pointer as you drag. You will remove any field from a pivot table by dragging it to any cell outside of the table. E.g. Drag the Income field from the PivotTable toolbar to the Drop Data items Here area. Drag the Product field from the PivotTable toolbar to the Drop page Fields Here area of the pivot table. Click on the Product drop-down arrow, select 2022, and click on Ok. consider what it would take to derive this level of detail by writing your own formulas.
Modifying Pivot Tables
After you create a pivot table, you’ll probably need to adjust it to make it work for your specific requirements. Here you will change and reorganize pivot table data, alter pivot table field information, and refresh pivot table data. An advantage of using pivot tables to analyze information is that pivot tables can be manipulated and changed easily. Drag the Product field from the Page Field area to the Row Field area, as indicated. Drag the Month field from the Row Field area to the Drop Page Fields Here area. In the Month drop-down box, select Jan. click on Ok.
When you create a pivot table, Excel formats the data by using the number format included in the Normal style for the worksheet. E.g. Right-click on the Sum Of Income field button and choose Field Settings. The PivotTable Field dialog box is displayed. From here you can change several settings for the selected field. In the Name box, type Total Income click on the Number button. Format the cells as Currency with 0 decimal places. Click on OK twice.
If your source data changes after you have created a pivot table, you will need to refresh the pivot table to reflect the changes to the source data. E.g. The value in cell B5 contains the January 1999 total income, which is $4,748. Activate the Sales sheet. Select the cell--- and change the value to 2000. Activate Sheet1 once again. On the PivotTable toolbar, click on the Refresh Data button.
Regards,
BEM T. CELESTINE
Social Plugin