In Excel, this is actually pretty easy to do.
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.
Make your first data row the S&P (or whatever baseline you choose). This is the first gray row in my sample.
Skip a row (we'll come back to it shortly). This is the second gray row in my sample.
Add all of your companies to chart and compare.
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
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.
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:
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: