Row the what columns from the data will be in the left-hand – “row” determines columns of the table. You can have as many rows as you have columns in the spreadsheet of data. Column the determines the names of the columns going across the – “column” pivot table. The diagram to the right shows the relationship between rows and columns on the pivot table. in Excel 2003 (see purple box) is now What was called “Pages” called “Report in Excel 2007. Filters” The pivot table layout screen is slightly different from that in Excel 2003. It has built-in formatting for Rows. To see the layout screen in the Excel 2003 version, click on in the top left-hand corner. Click on the tab and the Pivot “Options” “Display” “Classic Table box. Then click OK. layout” check The column names (Pivot Table Field List) from the data spreadsheet are shown on the right-hand side of the screen. To put any column in the Row Labels section, click on the box next to the column name. The rows will appear in the Row Labels box in the order shown in the list. If you want to re-arrange them, you can and the fields in that section into the desired order. “drag drop” You can also drag and drop field names to any of the areas shown instead of clicking on the box. Any of the spreadsheet columns can also be put in the The most “Values” area. common use is to present the total of an amount field, such as transaction or purchase order amount. If Excel detects a numeric column, it should automatically choose the Sum of that amount for the Value. For any non-numeric columns, Excel defaults to Count of the rows. The area would be a good place to put the field in order “Report Filter” “Program Name” to be able to filter on one Agency or University at a time. For Compliance audits, the vendor name from the 19D query would be a good candidate for a “Report Filter”. Here, we have put the State Entity name and the vendor name, in that order, as the rows going down and the sum of the transaction amounts as the data. The total in column C will be the amount of spend for the State Entity (column A) at the merchant (column B). The Report Filters will be above the pivot table to allow filtering.
Row – the “row” determines what columns from the data will be in the left-hand columns of the table. You can have as many rows as you have columns in the spreadsheet of data. Column – the “column” determines the names of the columns going across the pivot table. The diagram to the right shows the relationship between rows and columns on the pivot table. What was called “Pages” in Excel 2003 (see purple box) is now called “Report Filters” in Excel 2007. The pivot table layout screen is slightly different from that in Excel 2003. It has built-in formatting for Rows. To see the layout screen in the Excel 2003 version, click on “Options” in the top left-hand corner. Click on the “Display” tab and the “Classic Pivot Table layout” check box. Then click OK. The column names (Pivot Table Field List) from the data spreadsheet are shown on the right-hand side of the screen. To put any column in the Row Labels section, click on the box next to the column name. The rows will appear in the Row Labels box in the order shown in the list. If you want to re-arrange them, you can “drag and drop” the fields in that section into the desired order. You can also drag and drop field names to any of the areas shown instead of clicking on the box. Any of the spreadsheet columns can also be put in the “Values” area. The most common use is to present the total of an amount field, such as transaction or purchase order amount. If Excel detects a numeric column, it should automatically choose the Sum of that amount for the Value. For any non-numeric columns, Excel defaults to Count of the rows. The “Report Filter” area would be a good place to put the “Program Name” field in order to be able to filter on one Agency or University at a time. For Compliance audits, the vendor name from the 19D query would be a good candidate for a “Report Filter”. Here, we have put the State Entity name and the vendor name, in that order, as the rows going down and the sum of the transaction amounts as the data. The total in column C will be the amount of spend for the State Entity (column A) at the merchant (column B). The Report Filters will be above the pivot table to allow filtering.
PivotTables_Excel2007.docx