How to fix the Excel 2010 Stock chart error

If you keep getting the error message “To create this stock chart, arrange the data on your sheet in this order: high price, low price, closing price.  Use dates or stock names as labels” then read on and find out how to resolve this without attempting to download fixes or create custom bar charts…




The error

I was recently asked about a common error message people get when trying to create a stock chart in Excel 2007 or 2010.  That is the information box which states “To create this stock chart, arrange the data on your sheet in this order: high price, low price, closing price.  Use dates or stock names as labels.”

00062_Excel Stock Chart Error_01

This error message, or more appropriately called information message is one that seems to stump many Excel users as they genuinely believe they have their data in the correct format.  I think the main issue is that the information message as presented can be misleading, you need to use a date rather than a stock name otherwise it will not work.

Key causes of the error

  • No Date.  Stock charts require a date, in a date format, so never try and create one with just a stock name or code in the column.  Without the date it does not matter if all the other values are there you will always get the error.

Lack of headers in the data.  Always include the headers as Excel is looking for them so for the basic stock chart that means you need a column called Date, one called High, one called Low and one called close like so:

00062_Excel Stock Chart Error_02

  • Incorrect format of the data.  Make sure you are using dates and a value such as currency or number for the formats.  Date is the most important one as Excel should interpret numbers that are in text format as a number but best to be safe than sorry if you are getting errors.
  • Not enough headers for the Stock chart option selected.  There are several types of stock charts so pay attention to the one you are trying to create and what it requires, Excel will tell you.  If it requires an open price then include it, along with the header “open”.

The basics are you must have your data set correctly to create a stock chart, it is not as forgiving as some of the other chart types where it will just create a chart incorrectly and allow you to resolve formatting issues once created.  If you pay attention and follow the above then you will have no problem, start with the most basic version if needed and build up from there.

Keep Excelling,

Leave a Comment