Join Richard Harrington for an in-depth discussion in this video Using a stock chart, part of Excel 2013: Working with Charts and Graphs.
- View Offline
- Another chart that's available to you is the ability to convey financial data, now, particularly stock information. Remember, in the stock market a price of a stock varies throughout the day. People often want to see the high and the low point, as well as know where the stock closed at, and that's what I've captured here. For a week, we've tracked the performance of stocks, and what I want to be able to convey is what happened. So during the sale of the stock each day, what was the high and the low, as well as where did it close.
It's important that you put the information in this order, high point, low point, and final price. All right, let's select this here. I'll put in date as well, and we'll convert this to a table. Highlight all of the cells and press control + t for table. I'll tell it to use the table as headers and click okay. Now with that selected, you could choose to insert the chart.
I'd choose the Recommended Charts option and go to All Charts view. You'll see Stock as a choice. Now you'll note that there are four types here. The one I have in this case is High, Low, and Close. This is useful if I want to see where things started. What was the high point, the low point, and where did it close. On the other hand, you can show different information. Maybe you want to add a fourth value. What did the stock price open at for bidding? In other words, what was the confidence of investors at the start of each day versus where they ended up.
You'll see in this case I can't pre-visualize this chart, because I don't have all four columns available. Additionally, you could see the Volume compared to the High, Low, and Close and all five pieces of information laid out. Because we're just comparing a High, Low, and Close, I'll choose this first option and click okay. I recommend after you do this you immediately go into some of the Style options. Notice the default value was not super-clear, but as we go through and change, it becomes much easier to see what's happening.
I like this view here. The line shows me how much fluctuation there was in stock price. I can see the bottom and the top. Additionally, the position of the dot gives me a good indicator where did things end up. Now in this case the information is useful, but I'm finding the dots a little bit big. So if I double-click on that, I can open up that particular series, and I'll go over here to the options for the controls, and we'll evaluate the Markers.
Now you'll see the ability here to change the marker option. You could turn them off. Use the built-in ones here and adjust their size. You'll also notice several different styles available. So you might find a line a bit more useful. While the dots are kind of cool in 3D, I think it's easier to visualize here with a line. Now what I'm seeing is the fluctuation on the up and down vertical line, and the horizontal lines clearly show me where the stock finished each day.
By changing the way that the data is visualized, it's much easier to read the fluctuations in stock price. Because the line is more absolute and it's not a big, overly stylized, 3D ball, I can clearly see the finishing price for each point. Remember, mousing over will also tell you the precise value, and the vertical line makes it clearer to indicate what the range was. When the line is longer, that indicates more fluctuation throughout the day between the high and the low point.
Remember, it's essential that you modify the appearance of a chart so it clearly conveys the data and doesn't introduce confusion into the audience.
- Creating a chart
- Picking a chart type
- Displaying data with tables
- Editing data in a chart
- Adding SmartArt graphics