Simple Charting with MS Excel

It has been said that a picture is worth a thousand words.  This is even more true when that picture reflects the value trends in the marketplace.  With the markets across the nation in transition, it’s more important than ever to be able to relay to our clients just exactly what the market is doing.   One of the best methods of illustrating complex market trends is to graphically illustrate them with a chart.  It’s very simple to illustrate these trends using the built in charting functions of any spreadsheet program.  While making charts is rather simple, charts provide very dramatic results that communicate more effectively than words alone.

One of the most important aspects of charting is choosing what data you intend to graph.  Based on your assignment problem and the scope of work you have selected, you may want to graph an entire zip code or just a narrow range of the neighborhood.  At the very least the date of sale, sale amount and GLA must be exported. The following steps will walk you through creating a chart from raw market data. 

This document is not intended to teach basic Excel procedures and assumes the reader knows the basics.  For basic Excel training, Microsoft offers excellent training articles.  http://office.microsoft.com/en-us/training/default.aspx

The best place to start is to export your data from MLS or other data provider such as RealQuest.  The preferred export format is comma delimited or *.csv format for working with Excel.  Contact your MLS technical service if you need assistance with exporting your data.

Data for this demonstration is from the MLS system Rapattoni.  It allows the creation a custom data export.  All examples that follow are for data that was exported from Rapattoni and imported into Microsoft Excel.

One of the most important parts is choosing what data you intend to graph.  Based on your assignment problem and the scope of work you have selected, you may want to graph and entire zip code to just a narrow range of the neighborhood.  At the very least the date of sale, sale amount and GLA must be exported.

For this demonstration, the community selected is a waterfront city comprised of ¼ acre lots.  The average home is a ranch style home built around 2000 consisting of 1,500 Sq.Ft of living space, has a two car garage, three bedrooms and two baths.  The search conducted for a one mile radius of off-water homes ranging in age from 1997-2007 and with a gross living area of 1,350 -1650 Sq.Ft.    This search was saved in Comma Delimed format (*.csv) by Rapattoni.

When first opened in Microsoft Excel the data appears as follows.  (Click graphics for a larger view.)

A completed chart in MS Excel.
At the top of this window make note that the file name ends in .csv, and that the sheet name near the bottom has punctuation market in it.  These must first be changed to an Excel format before any charts may be created.  Select File > Save As from the menu.  Save as File Type “Microsoft Excel Workbook(.xls)”.  Give the worksheet an appropriate name, and click Save.

A completed chart in MS Excel. Next you must rename the worksheet name to remove any punctuation marks.  To rename the sheet Select the sheet tab, .csv]9209705_RESI_FE4[1] in the demonstration.  Double click on it.  You may now change the name.  In this demonstration the name is named "Raw". Type in the new name and Press Enter.

Preserve the raw data for future reference

A completed chart in MS Excel. Next create a copy this raw data and copy it to the next sheet.  This new sheet will be used for all calculations thereby preserving the initial search on the raw page.  To do this, Right mouse click on the tab labeled "raw" at the bottom of this sheet > Select "Move or Copy" from the menu.  Select "(move to end)" from the menu and check the "Create a Copy" checkbox.  Click OK.

A new sheet is created with a carbon copy of the data and it is named "Raw (2)".  Rename this to "Cleaned" by first selecting this tab, then double clicking the name.  Change the name to “Cleaned” and press enter.

Now is the time to clean up the data.  Resize the cells and remove records that don't fit the model that you intended.  You will now have something that looks like follows.  Note the Sheet names “Raw” and “Cleaned” below.

A completed chart in MS Excel. Calculate sales based on $/Sq.Ft.

First, insert a column in which to calculate the $/Sq.Ft..   It makes the charting process much simpler if the $/Sq.Ft.  column is immediately to the right of the "date of sale" column.   To do this, select the letter associated with the field immediately to the right of the field that contains the date of Sale column on the spreadsheet.  This is E on my Example.  This will highlight that entire column.  Type Cntrl + (Control plus the + sign), or, select Insert > Column from the menu.

Move to the first cell in this newly created column.  Type the label name for this new column.  For the example it is “$/Sq.Ft”, press enter.  You should now be in the first empty row below this new name.  Type "=" (equal sign) then select the cell that contains your sale price, type "/" for divide and select the cell that contains the GLA.  The formula is =F2/J2 in the example.  Press enter.  This cell should contain your first rows sold price at $/Sq.Ft.  If it does your 90% there!

A completed chart in MS Excel. Oops… Does your formula look like a date?  To format that column as a number, select Format > Cells from the menu.  Then select number from the pop-up window.

To copy this formula to the rest of the data, double click on the lower right hand corner of the box that surrounds this new formula.  There is a funny little box in that corner (see yellow arrow below).  When double clicked Excel will copy this formula down the column for the rest of the spreadsheet.  It will stop at the first blank row it comes to.

Double click on the lower right corner as pointed out by the arrow. A completed chart in MS Excel.

The formula is copied to the last row in the column. A completed chart in MS Excel.

Sort Data

A completed chart in MS Excel.
It's nice to have the data sorted by the date sold.  As you can see in the example above the dates are not in order.  To sort on the date field type Cntrl A to select the entire spreadsheet.  Then from the menu select Data > Sort.  A menu will open. 

First select the "Header row" radio button near the bottom of this menu.  Select from the "Sort by" drop list the field name that has the sales date.  It is “Selling Date” in the example.  Select the corresponding "Ascending" Radio button and click "OK"

Hopefully you now have a worksheet with your data sorted by date of sale and the $/Sq.Ft.  is calculated for each row.   Now is a good time to save your work again.  File > save from the menu.

A completed chart in MS Excel. Now you are ready to chart!

Select the cells that contain the date of sale, "Sale Date" in the example, and "S/Sq.Ft." data for all cells.  This may be done by dragging the mouse from the first cell to the bitter end on the second cell.  A quicker way is to select the top row cell that contains "Selling Date" (Or whatever your export named it) Then Click Cntrl Shift ↓ (down arrow) all three of these at once.  Release all keys.  Now hold down the shift key and press the (right arrow).  You should now have highlighted the entire date and $/Sq.Ft.  columns.   If that didn’t work for you use your mouse to select these two columns.

A completed chart in MS Excel. There should be an Icon that looks like a bar chart on your menu.    Clicking that will open the chart wizard.  Or, select Insert > Chart from the menu
The chart Wizard will open.  Select "XY (Scatter)" from the left window.  A completed chart in MS Excel.

Select Next > Next.  In the Chart Title box type a name for this chart. A completed chart in MS Excel.

Select the Legend tab and select the "Top" radio Button.  Click Next then finish.  Tada!!! 

Here's your chart.  Not very pretty, huh?

Adding a trendline and formatting the chart.

Right mouse click one of the 'dots' (data points) on your chart.  Select "Add Trendline" from the menu. A completed chart in MS Excel.

From the Add Trendline menu select Polynomial.  Change the order to 3.  Press OK. A completed chart in MS Excel.

Your chart should look like as follows.  Experiment with different orders on the trendline.  A two will straighten the graph and a four may make it more wavy.  The Linear works in some cases also.

Formatting the chart

To make the chart clearer you will need to change some of the formatting.  To make the date cleaner (X-Axis), right mouse click over one of the dates and select “Format Axis”.  A menu will load to allow you to change the number formatting and scale.  Select the number format Tab, from the right window pane select the format of “Mar-01”.  Select the scale tab.  Change the Major Unit to a value that will allow some readability this is the number of days, the example is set to “120”, set the Minor Unit to “30”.

Clicking near the top in open spaces on your chart will give you numerous options for further formatting your chart.  You can add gridlines and change the color by right mouse clicking anywhere in the body of the graph.

Below the (Y-Axis) scale was changed to expand the curve, the colors of the chart and trendline were changed and the trendline was change to order-4.

A completed chart in MS Excel.
As you can see the formatting options are endless.

The final step is to insert these charts in your reports.  Just right mouse click your report and select copy, to copy it to your clipboard.   There is nothing like a chart to show indisputable evidence as to what the current market conditions are.  Market Conditions demonstrated by a chart that is easy to understand to virtually any reader of your report.