Want to create Chart for a column without numbers

Question

Just as the title says.

Running Windows 10 on one machine using Excel 2013.

A coworker may also be adding details to the same file, he is using Excel 2003. ( I understand that the file extension is different .xls v. xlsx)

So I may have to save mine in the older format if he wants to use it. But as of right now being able to create a chart/graph in 2013 is fine.

The column we are trying to create a chart/graph (whichever is easier, we are just looking for an easy way to visually represent the data) 

is the "reason product was returned"

From what I understand, a Chart must consist of numbers that total 100%, I read forums online of people suggesting creating another column with numbers that corresponds with the content of the cell, but I can't help but think there is a better way.

We will be adding to this list and updating, so anyone with suggestions please take that into consideration.

Any ideas?

Answer 1

Hello,

a chart does not need to consist of numbers that add up to 100%, but Excel cannot draw a chart from text. You will need some way to count how often each return reason shows up in the column. Consider the following screenshot:

The data in column a is a list of return reasons. One row per returned item. That data cannot be used in a chart.  A chart needs numbers. So, a helper table has been created that uses a formula to count how many times each return reason appears in column A.  The formula in cell E2 is 

=COUNTIF(A:A,D2)

and has been copied down to cell E6. Now there are numbers that can be plotted in a chart, in this case a horizontal bar chart. 

If you need to maintain compatibility with 2003, you need to select "Save as type: Excel 97-2003 Workbook (*.xls)" in the File Save dialog underneath the file name. 

There are other possible ways to chart this data, and there are other ways to arrive at the numbers, for example with a pivot table, which looks a bit different in compatibility mode, so I won't go into these details here.

Answer 2

Hi,

The numeric column can get auto generated by creating a Pivot Table.  Drag the Reasons column to the Row labels and to the Value area section.  Now create a Pivot Chart.

Hope this helps.