 |
||
|
SUPPORT/ |
|
|
||
|
 Documentation |
|
|
How-To Guides |
|
|
 Reports |
|
|
Data Functions |
|
|
Data Exports |
|
| Saved Searches |
||
| Customizations | ||
|
Add-Ons |
|
|
 OPAS Forum (hosted by Artifax) |
|
Excel Pivot Tables
Pivot Tables summarize data and allow you to examine it from many different points of view without having to create a new Excel document.
Excel pulls information directly from the OPAS database and consolidates it into a table. Each row and column can be modified to include or exclude data, and fields can be dragged on and off the table in different order. Each time you open an Excel document with a Pivot Table, it will auto-refresh and pull in the latest data from OPAS.
This page provides a brief overview as to how Pivot Tables are used to display and analyze OPAS data. It does not profess to be a comprehensive guide on Pivot Tables nor explain every available feature – we recommend any of the numerous Internet resources and Excel guides to further explore the abundant possibilities.
You must use Excel 2000, 2003 or 2007 for Pivot Tables to work properly. The screen pictures here come from Excel 2003.
NOTE – you do not need to have OPAS open for any of the following steps.
Contents |
|
Opening the Excel Document
This guide uses a simple Activity Analysis workbook as an example.
When you open the Excel document for the first time, you will see a warning that the document contains a macro – this macro is 
required to create the initial connection with your OPAS database and is run only one time. Click the “Enable Macros” button to proceed (if you do not see this message, click here for instructions).
Click the RUN button to establish the connection between Excel and OPAS and create the table.
After the macro is finished, you will have a Pivot Table in the Excel document. At this point you can save the document to any location (your own PC or a shared drive on your network). Each time you open the document, it will auto-refresh and pull in the latest information from OPAS.
Pivot Table Menu / Toolbar
Whenever you click anywhere in a Pivot Table, Excel may display the Pivot Table Field List menu shown here (the 'floating' blue box).
You can close this menu as it is rarely, if ever, used.
Restricting Data
One of the most common ways to modify a Pivot Table is to restrict the data it displays. You do this by clicking the little drop-down on any row or column field heading and un-check the data you wish to remove (the information is actually still there; this just hides it from the table).
Note that the table auto-refreshes to show only the selected data. You can restrict information from multiple rows and columns at the same time.
To include information, just re-tick the appropriate box.
The fields in the upper left corner of the spreadsheet are called “page fields.” They can likewise be restricted by clicking the drop-down and choosing a single item by which the table will be restricted.
To select multiple items, double-click the page field. On the next screen, hold down the CTRL key on your keyboard while you click the items to be included. Then click OK and the page will auto-refresh.
Reorganizing Data
You can re-organize the data hierarchy in the Pivot Table by dragging and dropping to re-order the column and row fields.
For example, column data in the Activity Analysis spreadsheet is organized first by Season and then by Performance/Non-Performance. To reverse that order, click Performance and drag it to the left of Season as shown above – the table will auto-refresh. The same procedure can be used for row fields.
Re-Arranging Data
Data fields can be moved between columns, rows and page fields. This is the true power of a Pivot table as it gives you an almost unlimited array of possibilities by which your information can be displayed and analyzed.
For example, suppose instead of viewing the activity analysis by Project, you wish to see it by Venue and Activity. Simply…
- Drag Project from the column fields up to the page fields – this will cause the Pivot Table to become a single total line (as you’ve removed all column criteria)
- Drag Venue from the page field area down to the columns
- Drag Activity from the page area down to the column area, to the right of Venue
The Pivot Table will auto-refresh at each step.
This same procedure works when dragging data fields:
- From the page field area to columns (the example shown above)
- From the page field area to rows
- From a column field to a row field
- From a row field to a column field
- From a column field to a page field
- From a row field to a page field
Excel will warn you if you try to move a field that would invalidate the Pivot Table (for example, you must have at least one column field).
Sub-Totals
To change whether or not a column or row field displays subtotals.
- Right-click the row or column heading
- Choose Field Settings…
- Set the Subtotals property to None and click OK
Re-Ordering Data Fields
Most column and row fields are sorted chronologically or alphabetically by default. You can over-ride this setting by:
- Right-clicking the row or column heading
- Choose Field Settings…
- Choose Advanced
- Set the Auto-sort option to Manual
You can now drag the columns / rows within that data heading into any order you like.
Refreshing Data
The Excel document will refresh each time it is opened, pulling in the most recent data in OPAS. You can also manually refresh the Pivot Table at any time by clicking anywhere in the Pivot Table and then clicking the Refresh icon (the icon will be on the Pivot Table toolbar that appears at the top of the screen).
Formatting the Pivot Table
If you rearrange columns or rows, the table may need to have the panes re-frozen.
- Select Window | Un-Freeze panes
- Click your mouse where you would like the new column/row freeze
- Choose Window | Freeze panes
You can reformat the pivot table by clicking anywhere in the table then clicking the Auto Format button (the button will be on the Pivot Table toolbar that appears at the top of the screen). This will bring up a list of Excel-defined auto-format options.
Entering other data in Excel
While you cannot manually edit any information inside the Pivot Table, you can enter information above the table (you may need to insert some blank rows first) or on other pages in the Workbook.
Pivot Tables can be printed just like any other Excel document.