2

Let's say I have a data table like

╔════════╦══════════╦═══════╗
║ Stock  ║   Date   ║ Price ║
╠════════╬══════════╬═══════╣
║ Stock1 ║ 1/1/2017 ║  17.0 ║
║ Stock1 ║ 1/2/2017 ║  17.4 ║
║ Stock1 ║ 1/3/2017 ║  16.9 ║
║ Stock2 ║ 1/1/2017 ║  78.9 ║
║ Stock2 ║ 1/2/2017 ║  77.4 ║
║ Stock2 ║ 1/3/2017 ║  79.3 ║
║ Stock3 ║ 1/1/2017 ║ 130.6 ║
║ Stock3 ║ 1/2/2017 ║ 128.5 ║
║ Stock3 ║ 1/3/2017 ║ 124.7 ║
╚════════╩══════════╩═══════╝

I want to use Excel to draw one single line chart, x-axis should be the dates, y-axis should be the value of stocks. And each stock has its own line in the chart.

1

1) Convert your data to an Excel Table.
2) Create a Pivot Table based upon your data table from Step 1, with:
column (legend/series) labels = Stock
row (axis/categories) labels = Date
values = Price
3) Create a Line Pivot Chart from your data

enter image description here

  • In the pivot table how do you set "values = Price"? I have Excel 2013, I only can have Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp to calculate values. The next tab "Show Values As" seems to calculate %. – hardywang Jun 13 '17 at 13:17
  • 1
    Assuming one value (price) for each row (date/stock combination), Sum, Average, Max, and Min will all return the same value, so you can select any of them and it will work. If you have more than one price (e.g. opening and closing), you can either aggregate using one of the above values, or create additional rows by adding another variable (e.g. date/time/stock or price type), and then aggregate the new single value. – dav Jun 13 '17 at 15:03
0

Put all Stock data in separate columns, select the whole table and create X-Y chart as usual:

enter image description here

  • If I have many stocks, it is a huge manual work to pull them to separated columns. Any convenient way? – hardywang Jun 13 '17 at 13:23
  • @hardywang: sure, but this is important condition you should mention in the question. How many? Also, is the dates (x-readings) are the same for all stocks, as in the example? We can use a worksheet function to reorganize the data. – g.kov Jun 13 '17 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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