Pivot Tables offer simple ways to understand the relationships between Origin and Destination Zones. The steps below walk you through how to export data from your Project downloads and quickly create a table highlighting O-D patterns.
How to Create your Pivot Table
Step 1: Run an O-D Analysis Project. Learn more on how to create an O-D Analysis.
Step 2: To download the Metrics and make your own analyses in Microsoft Excel, navigate to the Manage Projects section. Then click the “All Projects” tab. Each of your completed Projects should be visible. Click on the “Download” icon in the Actions Column.
Step 3: Select which files you want to download. For O-D Projects, the critical download is the O-D Metrics .csv file. You can choose the other options as you see fit. Using the default settings is the fastest way to download your results.
Step 4: Open the compressed (“zipped”) folder you’ve downloaded to view the .csv and shapefiles you selected. Note that you will also have two helpful README files that explain all the fields and files included in the downloaded folder. Within the excel file “Madison OD Personal Example Analysis”, the tab titled Madison_Summer_2308_od_personal shows the most popular pairs of origins and destinations.
Step 5: Once the .csv file is open, follow the steps below.
5a. Highlight all cells (both rows and columns) in the tab titled Madison_Summer_2308_od_personal.
5b. Once cells are highlighted click “Insert” > “Pivot table”. A window will pop up that says “Create PivotTable”, click “ok”.
5c. From your list of Pivot table fields, drag the Day Type and Day Part fields into the “Filters” section.
5d. Drag your Origin Zone Name field into the “Rows” section and the Destination Zone Name field into the “Columns” section.
5e. Drag your O-D Traffic (StL Index) field into the “Values” section.
Your selections should result in a PivotTable similar to the one below. Index values represent the relative volume of trips traveling between the Origin Zone listed in the rows of the table, and the Destination Zone listed in the columns of the table.
Step 6: Select “Conditional Formatting” and “Color Scales” to see the cells highlighted from green to red based on the StL Index values.
Pro Tip: If you’ve added Calibration Zones to your Project, add your StL Calibrated Index to your “Value Field” so that your Matrix reflects estimated counts.