Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I have data for all the historical sale prices of all houses on my street... so for each house I have a list of at least one date/£price point.

I want to enter this into an Excel spreadsheet so that on the same chart, I can get a separate line-graph for each property. For example #12 might have sold 3 times in 2000, 2003, 2010 whereas #30 might have sold only once in 2000.

I'm not sure the right way to tabulate this data and graph it as desired. Could I for example have column A as house-number, then column B, D, F, etc having sale date and C, E, G, etc having sale price? Or perhaps a simple 3-column setup where each row lists house-number, sale date, and sale price - can Excel let me explain all rows with the same house-number should be grouped into a separate line-graph?

share|improve this question
my first thought was: As long as you have your date and price in the same column it is much more difficult to do that. – nixda Feb 4 at 11:25
have you considered pivot charts? – tumchaaditya Feb 4 at 11:26
Use 3 column - Number, Data, Price ... and filter. – STTR Feb 4 at 12:07
@STTR I'm not expert at graphs, could you write up an answer with a bit more detail? – John Feb 4 at 14:22

1 Answer

up vote 2 down vote accepted

The best way I think you can do this is using 1st column for the house # and one column for each year. In the table cells you place the price. If you use bar graph, you can leave this as it is but if you want line graphs, you will need to fill the data cells repeating last price after 1st sale in the period you consider (to keep the line continous). A better option suggested by Brad in comments is to Right click on chart> Select Data...>Click on Hidden and Empty Cells>Connect Data Points with Line>Ok>Ok>. (the example data below is using the former option of repeating values but the resulting chart should be the same). You select the graph as bar or line and use data in lines. If you have too many houses (or too many years), you can filter the data in the spreadsheet with auto filter to plot only what you want (a single house history, a few houses for example).

Data table with cells filled to make continous lines

Chart of houses price acording to years

Another way using the same table, if you want to have the years as data series (and each house on the x axis) is to use data in columns (probably the default) but in this case it is better to use bar chars I think (and filling or not the empty years repeating last price depending on the result you prefer):

Bar chart of each house price for time period

share|improve this answer
1  
No true about needing to fill in the years without a sale with the last known data point's value to make a line. Right click on chart> Select Data...>Click on Hidden and Empty Cells>Connect Data Points with Line>Ok>Ok>Enjoy – Brad Feb 7 at 14:32
@Brad tks, I didn't knew this so it makes things easier as you don't need to modify the data. – laurent Feb 7 at 14:44
updated answer to add the option suggested by Brad – laurent Feb 7 at 14:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.