Step 1: Excel Pivot Tables :: Organise your raw data |
Step 1: Excel Pivot Tables :: Organise your raw data |
This section shows you;
- What kinds of data you can use in an Excel Pivot Table report
- How to organize and prepare your source data.
Once we have organized our data correctly we can create an Excel Pivot Table report using the built-in Excel Pivot Table wizard.
Types of source data. |
Excel Pivot Tables can use the following sources of raw data to base its reports upon; (As seen in Step 1 of 3 of the ‘Pivot Table and Pivot Chart Wizard’)
 |
- Microsoft Office Excel list or database
- External Data Source
- Multiple Consolidation Ranges
- Based on another Excel Pivot Table or Pivot Chart Report.
Microsoft Office Excel list or database. |
- Ensure the first row of your list contains column labels - these will be used as field labels in the Excel Pivot Table.
- Ensure each individual column contains the same data type – e.g. A column that contains monetary values does not include text in any of that column’s cells.
- Ensure you remove subtotals - the Excel Pivot Table report will calculate the subtotals and grand totals for you.
- Use the Advanced Filter command - If you want to use filtered data use the Advanced Filter command rather than Auto Filter. i.e. Data > Filter > Advanced Filter from the main menu, and then click ‘Copy to another location’. This extracts the filtered data to another worksheet - you can now use this filtered data as the raw data for your Excel Pivot Table report. N.b. Using AutoFilter or filtering your source data only hides the data - the Excel Pivot Table report will still include all the data regardless.
Correct
 |
Incorrect :: Common Errors
- Missing Field Labels.
- Blank Columns.
- Inconsistent data types and values.
- Sub Totals nested in Excel List.
 |
External Data Source. |
You can use a database file, text file, or a source from the Web as the raw data for an Excel Pivot Table To organise external data correctly check the following;
Install the required tools and drivers -
- Install the open database connectivity (ODBC) drivers or data source drivers you need..
- Microsoft Query, (including Drivers and Help), is installed automatically when first used. When creating an Excel Pivot Table report with the Excel Pivot Table Wizard, you'll use Microsoft Query to retrieve the external data.
Retrieve the data before you start the wizard, if required.
- When using the Excel Pivot Table and PivotChart Wizard to retrieve data, you cannot use ‘Query’ if the data retrieval method is one listed below.
- Office Data Connections (.odc)
- Query Files
- Query files (.dqy, .oqy, or .rqy) and Report Template (xlt)
- Parameter Queries
- Web Queries
- Prior to starting the Excel Pivot Table and PivotChart Wizard follow the instructions below to insert the data in an Excel workbook;
Office Data Connections or odc ::
Office Data Connection, or .odc, files are the recommended method for retrieving external data for your Excel Pivot Table reports. You can use odc. Files;
- Whenever you're retrieving data from a single database table or
- from an OLAP database
This stops the need to either;
- Combine data from more than one table in an external database or
- Filter the data before you create the report.
Query Files ::
(.dqy, .oqy, or .rqy)
and Report Template (xlt) ::
If you need to use a query file to retrieve the data, open the query file in Excel. To use a report template (.xlt) that doesn't already include an Excel Pivot Table report, open the template.
Parameter Queries ::
You must first create the parameter query in Microsoft Query before you can use a parameter query to retrieve the data. This is so that it returns the data to Excel. N.b. A Parameter query cannot be used to retrieve source data for an Excel Pivot Table from OLAP databases. see Help in Microsoft Query on how to create Parameter Queries.
Web Queries ::
You must first create a Web query and retrieve the data into Excel, prior to actually using the Web query to request data over the Internet. To create a Web query, go to Data > Import External Data > New Web Query.
Multiple Consolidation Ranges |
The source data for an Excel Pivot Table can be based upon data from several different Excel lists. This can be achieved by using;
- ‘Data Consolidation’ functionality in excel: Data > Consolidate…
- Using a central spreadsheet as the source of data which references multiple columns via formulas.
- Copying and pasting filtered data from elsewhere.
To organise data from multiple Excel lists correctly, check the following: (Also see ‘common errors’ illustration above)
- Ensure the first row of your list contains column labels - these will be used as field labels in the Excel Pivot Table.
- Ensure each individual column contains the same data type – e.g. A column that contains monetary values does not include text in any of that column’s cells.
- Ensure you remove subtotals - the Excel Pivot Table report will calculate the subtotals and grand totals for you.
- Use the Advanced Filter command - If you want to use filtered data use the Advanced Filter command rather than Auto Filter. i.e. Data > Filter > Advanced Filter from the main menu, and then click ‘Copy to another location’. This extracts the filtered data to another worksheet - you can now use this filtered data as the raw data for your Excel Pivot Table report. N.b. Using AutoFilter or filtering your source data only hides the data - the Excel Pivot Table report will still include all the data regardless.
Based on Another Excel Pivot Table Report |
If you are going to be using the same data to create several Excel Pivot Table reports, you can minimize disk space by copying and pasting an existing Excel Pivot Table and using this for the basis of a new report. The copied Excel Pivot Table report automatically links to the original Excel Pivot Table saving space. N.b. Refreshing the data in the original Excel Pivot Table report refreshes the new reports it is based upon and vice versa. To organise the data in the mother Excel Pivot Table report correctly check the following;
Ensure both Excel Pivot Table reports are in the same workbook.
Copy and paste the original (mother) Excel Pivot Table report if it is in a different workbook.
Check the page field options for External Data settings::
If your original Excel Pivot Table report page field settings are set to retrieve external data for each page individually. You will need to change the page fields so they retrieve external data for all Page Field items at the same time.
To check the settings of the Page Field - double click the relevant Page Field in the Excel Pivot Table and select the Advanced button.
See "Structure of an Excel Pivot Table report," for more information on Page Fields.
| Previous Section :: Excel Pivot Tables : Overview | Next Section :: Step 2 : Create an Excel Pivot Table Report |