How to change source data to show chart correctly?


If you find any of the following difficulty while preparing chart then this article is for you:

1. Excel does not interpret chart series correctly. So chart prepared is totally no use for you.

2. Excel displays default chart labels as Series 1, Series 2 and so on…

3. Horizontal axis labels are showing in Vertical axis and same vice versa.

4. You want to display two columns data in Horizontal axis as shown below.

Let's use one example and undestand each problem one by one.

Download example How to select data source in Excel to try by yourself.

Example: We have an example of 4 week of expenses datewise.

If we directly insert chart using F11(Shortcut key) or Insert-> Charts, It results in this type of chart. 

Now this is certainly you don't want to have. This problem may be frustrating and you may need to spend lot of time to resolve it.

You want chart something like this:

Now follow these easy steps to modify chart for appropriate results:

1. Prepare default chart using Insert->Charts. It may be weird then you want but don't worry we will change it soon. Right click on chart and click on "Select Data".


2. Select Data Source dialog box will open. Try to understand the each part of source entry. By default excel selects Automatic entries for Horizontal and Vertical Category.

3. Remove the entries which you don't want to show on Vertical Axis. You can also edit specific entry as per your requirement.

4. After removing Date Category from Legend Entries, your chart will look like this. (Still better than before!!)

5. Let's make chart better by adding Date to the Horizontal axis. Click on Edit button of Horizontal Axis Labels.

6. Select data which you want to display on Horizontal Axis. Do not select Column headings because it will appear with data of date and week.

7. Change in Horizontal Axis labels data

8. Now your Chart is perfectly ready with awesome chart and required result.




2,962 total views, 2 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>