As the de-facto statistics guru for the association I find myself getting tasked regularly to take large amounts of data and format them in a way which is easy to understand or to identify trends in data. One thing that I have found is by and far people don’t understand or underestimate is the value in pivot tables and pivot charts for data analysis.
In a nutshell PivotTables allow you to create multidimensional data views of data by dragging data headers and grouping them by row and columns and where it gets really powerful is by allowing you to draw conclusions from the data by using calculated fields.
To get started, simply open up a spreadsheet with a lot of data that you want to analyze. Something I do regularly is to download every listing for a given city on the MLS. Once you have the raw data decide on what data groups you want to work with. As a general rule I try to have minimum of three data groups for instance status, status date, and mlsnumber.
For example if was to take every listing in Pleasanton I would have 27,365 listings.
Now, lets say I wanted to know the average price for homes within Pleasanton by year since 2000. I could run 7 seperate reports and manually tally the results or I could run one query export the results to excel and from there create a pivot table that summarizes the information I want to pull from the large data set. So for my example I will run a query for sold detached properties in Pleasanton for the years 2000 - 2007. Running my query I get 6810 results for my query, that is a lot less then 27,365 but still a lot of data to manually dig through.
Now that I have my query I need to decide where I am going. At the end of the report I want to know how many listings sold in Pleasanton for a given year and the average price sold. To accomplish this goal I will have three columns mlsnumber, sold_price, and closing date.
After I have exported my data from the MLS I want to get started with making my pivot table first I want to tell the pivot table wizard what data I want to add to the pivot table, so I highlight the three columns of data. Next I click on Insert and then PivotTable when the PivotTable Wizard box appears I tell it put the pivot table into a new sheet and click ok.
The result is that Excel creates a blank PivotTable. To create my report I drag and drop the various fields from the PivotTable field list onto the appropriate column, row, or data section. As you drag and drop these items, the resulting report is updated. To get started I am going to drop the closing date value in the row label box, mls number and sold price in the values box. I will also change the sold price to an average value and the display type to currency no decimal places.
The final result is a table that displays the number of detached homes that sold in Pleasanton by year and the average price sold. The total time it took to run the query and output the report was less than five minutes.
Now granted the above example is a fairly simplistic PivotTable but I believe it is useful for showing the utility and functionality of this often overlooked tool. If you found this how-to useful and want to know more about pivot tables and how they can be used please leave a comment or drop me an email.
No comments yet.
Comments RSS TrackBack Identifier URI
Leave a comment
No Comments