OK, so I am a numbers guy. I've been using MS Excel for as long as it has been a product (Excel 1.0).
I have been posting a lot of charts and graphs about the Tallahassee Real Estate Market in my blogs, and quite a few people have complemented me on my graphs.
But that wasn't enough for Adam Waldman, Nooooo, he wanted a tutorial. Well Adam, here is my shot at a MS Excel for Realtors Tutorial:
The first step is to download the data from your MLS that you would like to turn into a chart. Once you've done it, it should look something like this when opened in MS Excel (note, I use the newest version of Excel 2007):
The next step is to clean up the data. For example, I am doing a subdivision report. In our MLS, this is a field that the Realtors fill-in themselves, therefore, I have to make sure all the subdivisions are spelled the same and that there are no typo's. Every different subdivision is going to have it's own record, so we don't want the same subdivision spelled more than one way! The circled area in the next picture demonstrates what I am talking about:
You can see that Apalachee Ridge was Reported many different ways, so I need to "drag" down the correct spelling over all the incorrect ones, like in the picture below:
In this example, I had so much data and so many typo's to fix, it took me over an hour to get all 7000+ entries accurate (I recommend a simpler exercise for your first one!).
Next, we need to produce a report from our data. While this will seem somewhat complex, it will be rather easy for you after you try it a few times.
To produce a report, we need to do a "pivotTable" on our data. Click on Insert, then PivotTable.
When you do, your screen will look similar to this:
It selects the entire data set in the "Table/Range" box, but you can drag a selected area if you want only a piece of your data set. Once you choose "OK," you will be faced with the PivotTable field below. This is one cool geek tool and you should take the time to master its use!
The reason it is called a "PivotTable" is because you are going to select a column to "Pivot" around and create a table. Because this is a report on Subdivisions, I'll select that one and drag it down to the "Row Labels" field:
Now Drag Down....
Now that I have the Row Label, I can choose all the data fields that I like. I will just choose "Units for Sale" for the purpose of Adam's tutorial:
Then drag it down to the values box ....
When we hit "OK," we get our PivotTable Report like below: We can then rename the sheet to whatever we want by right clicking on the tab and selecting "Rename."
Now, we have data that we can graph. Below, I used a more comprehensive PivotTable to demonstrate the graphing, but it would have worked out just as well with the above data:
Now all we need to do is drag our mouse over the area that we want to turn into a chart, as below:
With your data highlighted, choose insert and then click on the chart type that you desire. Above, I'm choosing to Insert a Bar Chart.
Excel then expands the selection so that I can choose what type of Bar Chart that I want, I chose a Clustered Bar in 3-D Chart.
Once selected, it throws the new chart right in the middle of your page. I like it to be on its own page, so I "right click" over the chart and choose "Move Chart."
Excel then "Pops Open" another window and gives me the option of creating a new page with it:
After selecting "OK," you end up with a new sheet as below:
You can click on any Chart Style above the chart and it instantly changes the appearance of your chart:
The Title is easily changed by Clicking on the Title and Typing anything you want:
Finally, you can change the colors of any given bar by clicking on it and then clicking the color tool.
Now you have a chart that you can use in your blogs. I recommend that you play around with this and get the hang of it. You will be proficient in no time at all.
When my chart is complete, I use a screen capture program to save it as a picture file. There probably is a smarter way, but that would be another blog by someone smarter than I.....
As for you Adam, I expect a "5" for all of my hard work.....
Subscribe to this Blog:
Joe Manausa, MBA, CRB, CRS | Broker / Owner | Century 21 First Realty
2365 Centerville Road | Tallahassee, Florida 32308 | 850-386-2001 |
;)
;)
