9

I have 2 columns in my excel file. 1 is 'code' which has the following values: 2050, 2196, 1605, D1488. The next column is 'amount' which has dollar amount values. When I insert a column chart, the x axis shows 1, 2, 3, 4, 5, 6. The y axis is showing two bars which are the code and amount. I want the x axis to show the numbers in the 'codes' column. How do I do this? I realize that it's because they're numbers, that's why it's not working. But if I change this column to actual text like a, b, c, etc. then it will work. So any way to get around this?

jerry
  • 129
  • 1
  • 3
  • 10
  • Even, have set the formatting correctly, I found the problem happen again, after reopen the file. – Eric K. May 09 '17 at 02:28

3 Answers3

7

Starting with this data ...

Code    Amount
2050     $680.00 
2196     $824.00 
1605     $127.00 
1488     $853.00

Use the following steps ...

  1. Select the data range.
  2. Select the Insert Ribbon. In the Charts area, pick a 2D column chart

enter image description here

  1. Select the Design Ribbon. Pick Select Data.
  2. In the Select Data Source dialog, Remove the Code Series.

enter image description here

  1. In the Select Data Source dialog, Edit the Horizontal (Category) Axis Labels.

enter image description here

  1. In the Axis Labels dialog, for Axis Label Range, enter the data range for Code (exclude the header, the first row).

enter image description here

  1. In the Select Data Source dialog, click OK.

enter image description here

OldUgly
  • 2,129
  • 3
  • 13
  • 21
3

Alternatively, click on the graph, then the Filter button which appears in the top right, below the + and the paintbrush icons.

Click on Names, and then under Categories click on Column A (or wherever your values are)

This should change the layout of the graph to pull the actual values, rather than their positions, and doesn't require any other changes to your graph range.

Ben Pentelow
  • 31
  • 1
2

Here is another solution for this or similar problem. I propose to use the Funfun Excel add-in to do this. This add-in allows you to use JavaScript code directly in Excel so you could use powerful libraries like Chart.js or D3.js to plot chart like this easily. Here I made an example based on your problem and sample data.

enter image description here

Based on your description, what you need to do is to make the data in the code column recognized as labels rather than real values. This is very easy to achieve in Chart.js since all you need to do is to set them as labels in the code.

The Funfun also has an online editor in which you could explore with your JavaScript code and test the result. You could check the detailed code of this example on the link below.

https://www.funfun.io/1/edit/5a4a4fda927dd84b42aceb91

Once you are satisfied with your result in the online editor, you could easily load the result into your Excel (so you could see the chart and data directly in Excel) using the URL below. But first, of course, you need to insert the Funfun add-in into your Excel from Insert-Office add-ins. Here are some screenshots showing you how to load the example into your Excel.

enter image description here

enter image description here

Disclosure: I'm a developer of Funfun

Chuan Qin
  • 655
  • 6
  • 6