Categories in Chart are Numerical, Excel 2013 won't treat them like categories

I need to make a bar chart of data whose categories are 5 digit zip codes.  I cannot find a way to make excel treat the zip codes as categories.  I formatted them as text.  If I precede the zip codes with a letter, such as z,  (so for example, the category is supposed to be 15232 but I use z15232) excel does what I want. But if I try to use the zip codes unaltered as categories, excel treats them as a second series.  

Please see attached image.  The top chart is correct, except it has the letter z in front of the zip code, which I do not want.  Leaving off the z gives the bottom chart, which is not what I want.

Thanks,

Cipriano
Excel-Problem.JPG
cipriano555Asked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

insert a row above row 1 and add a header for the data in cell B1. Leave cell A1 empty. Now create the chart. If the top left cell of the chart source is blank, Excel will treat that column as X axis categories, no matter if the values in the cells are text or numbers.

It's not intuitive, but that's just how Excel works.

If I need to create a chart with numeric X axis labels and my data source has a column header for the column, I delete the header text, create the chart and then put the header text back in.

cheers, teylyn
0
 
Ejgil HedegaardCommented:
Mark only column B and make the chart.
Then "Select data" and add the categories, column A, then the numbers will not be treated as a second series.
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Just add a column near the Zip code Column and copy the Zip code Data to that Column , So now there are three columns A B C where A and B are Same as Zip Code

Just put this formula in Column B
=LEFT(A1,LEN(A1)) and now Plot Graph by taking Column B and C and you can See the Result.

All the Best
Sample.xls
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
Have you tried preceding the ZIP codes with a single quote as in '15232 ?
0
 
cipriano555Author Commented:
hgholt:

Mark only column B and make the chart.
Then "Select data" and add the categories, column A, then the numbers will not be treated as a second series.

This didn't do anything.  I guess I didn't know how to "add the categories."

 teylyn:
Yes, that was a simple solution.

Vikas:
Yes, that works.

Anthony
A lot of manual work because I have a lot of zip codes, but it worked
0
 
Scott PletcherSenior DBACommented:
Make the entire column as Category = "Text" in Excel (click on the column letter, right-click, "Format Cells...", set "Category:" to "Text").  SQL will then load the column as nvarchar.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>>  I guess I didn't know how to "add the categories."

Here is a screenshot to illustrate what hgholt suggests

select categories for chart
You can use this technique in the second chart of your screenshot: open the Select Data Source dialog, select Series1 and delete it. Then click the Edit button highlighted in the screenshot and select the zip codes in column A.
0
 
Ejgil HedegaardCommented:
Thanks teylyn for the explanation.
I can't make (easily understandable) screenshots, because my version is not in english.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.