Pivot tables in Excel
|
|
|
|
1. Source data 2. Source values for Qtr3 Golf summary 3. PivotTable report 4. Summary of source values in C2 and C8 A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest. Use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. In the report illustrated above, you can easily see how the third-quarter golf sales in cell F3 stack up against sales for another sport or quarter, or the total sales. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages. In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. In the example above, the Sport column becomes the Sport field, and each record for Golf is summarized in a single Golf item. A data field, such as Sum of Sales, provides the values to be summarized. Cell F3 in the report above contains the sum of the Sales value from every row in the source data for which the Sport column contains Golf and the Quarter column contains Qtr3. To create a PivotTable report, you run the PivotTable and PivotChart Wizard. In the wizard, you select the source data you want from your worksheet list or external database. The wizard then provides you with a worksheet area for the report and a list of the available fields. As you drag the fields from the list window to the outlined areas, Microsoft Excel summarizes and calculates the report for you automatically. If you're using an Office Data Connection to retrieve external data (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) for your report, you can return the data directly to a PivotTable report, without running the PivotTable and PivotChart Wizard. Office Data Connections are the recommended method of retrieving external data for your reports when you don't need to combine data from more than one table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).) in the external database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) or filter the data to select specific records before creating the report, and for retrieving data from OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) databases. After you create a PivotTable report, you can customize it to focus on the information you want: change the layout, change the format, or drill down to display more detailed data. |
|
| creating a pivot table in Excel step by step continued |