Solved

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

Posted on 2014-04-28
8
201 Views
Last Modified: 2014-05-19
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
0
Comment
Question by:cipriano555
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40028285
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 40028305
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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40029272
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40029347
Have you tried preceding the ZIP codes with a single quote as in '15232 ?
0
 

Author Comment

by:cipriano555
ID: 40029558
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40030217
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40030978
>>  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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40032769
Thanks teylyn for the explanation.
I can't make (easily understandable) screenshots, because my version is not in english.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question