Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to plot the stock prices of certain companies VS the S&P500. I have all the price data downloaded in my excel sheet already.

I want to create a line graph in Excel. One line is fixed and that would be the S&P500, and the other would be the company I select. I know how to use data validation to create a dropdown list, but how could I make it in such a way that when I select company A, I would only see company A's price data but not company B, C, etc. Then when I select company B, I would only see company B's price data & the S&P price data. And so on. The S&P line will be visible all the time, but the various company's price line will only appear if it is selected.

Is there any easy way to normalize all sets of data in a way such that the starting point is always 100?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

In Excel, this is actually pretty easy to do.

Focus Chart2

  1. Set your data up correctly! In my sample, I have a column for the companies and a column for each date you want charted. Obviously, this could be any time increment from sub-hour to annual.

  2. Make your first data row the S&P (or whatever baseline you choose). This is the first gray row in my sample.

  3. Skip a row (we'll come back to it shortly). This is the second gray row in my sample.

  4. Add all of your companies to chart and compare.

  5. In your skipped row, in the first column (A3 in my sample), use a data validation list to pull all of your company names into a dropdown. Then, fill all of the remaining columns with a VLOOKUP to pull the selected companies data into their respective cells. My formula is

    =VLOOKUP($A$3,$A$4:$M$7,COLUMN(B3))

  6. Create a chart using the data from the S&P and Target rows and format as appropriate.

Now, you can choose any single company and have it highlighted (including correct labels, if you want) compared to the S&P (or any other benchmark). A few thoughts:

  • Named ranges will make this much easier to manage.
  • I like to add all of the series and format them a very light gray. This way, in addition to comparing to the baseline, you can also compare to all the other competitors (as shown in my sample).
  • If you want to normalize your data, then the easiest way is to convert it at the raw data point and then chart the normalized values. This will leave you with the raw data, the normalized data and the chart-again, named ranges will do wonders for helping you manage all of this.

EDIT: To use your data laid out horizontally, you'll need to change two things in the formula from Step 5. You'll change to the HLOOKUP function and you'll need to return the ROW value instead of the COLUMN. The new function will be:

=HLOOKUP($S$1,$T$1:$W$13,ROW(S2))

The gray Co. C still pulls the data from the other columns using data validation (although you can't see the dropdown in this image, its the same as the vertical version above). Here's how the data looks laid out:

HLOOKUP

share|improve this answer
    
hi, my data is vertical, how do I use the vlookup in this case? would I have to change the Column (B3) to something else? –  daysandtimes Sep 27 '12 at 12:13
    
or is hlookup more relevant in this case? –  daysandtimes Sep 27 '12 at 12:30
    
Without seeing your data and its exact layout, I'd guess that HLOOKUP is probably a good solution. It works (and its syntax) match VLOOKUP, so it shouldn't require too much modification. Worst case, you could TRANSPOSE your data into a vertical layout, but that shouldn't be necessary. –  dav Sep 27 '12 at 13:05
    
I asked my new question here: superuser.com/questions/480290/… Pls have a look! –  daysandtimes Sep 27 '12 at 13:07
    
You can also check the edit I made to the answer above. –  dav Sep 27 '12 at 13:22

Refer this link:
http://www.get-digital-help.com/2011/05/17/excel-charts-use-dynamic-ranges-to-add-new-values-to-both-chart-and-drop-down-list/
Its pretty detailed tutorial with good screenshots.

Few modifications for your case,

  1. Add one constant data range for S&P and one variable as indicated in the link
  2. Choose chart type as line(Quite obvious)

As with the bonus question,

Create another table in which, the first cell for each company will always contain 100 as required.
The subsequent cells should contain a formula as value = (100/first data point value)*concerned data point value
Now this will generate another table of normalized values from which you can plot the chart.
You can simply hide the columns if you don't want that extra data to clutter your worksheet.

share|improve this answer
    
to clarify - i actually don't mean 100, but i mean normalize the starting point to 100%, and everything will be in terms of that base line. –  daysandtimes Sep 11 '12 at 7:11
    
i am still not getting you..can you perhaps explain with some sample data? –  tumchaaditya Sep 11 '12 at 7:14
    
for example if the first data point is 2, it will be 100% and if the second data point is 2.2, it will be 110%. –  daysandtimes Sep 11 '12 at 7:30
    
still not clear....do you want all the data on Y axis to be as %age of the minimum data point? please update the question with a sample table screen shot and a sample graph.. –  tumchaaditya Sep 11 '12 at 8:33
    
got it..updated the answer.. –  tumchaaditya Sep 12 '12 at 2:30

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.