Solved

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

Posted on 2014-04-28
8
199 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
8 Comments
 
LVL 21

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 21

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Insert to Begin if data exists 2 31
SQL Server 2008 R2, need a pivot/cross tab query... 4 26
SQL, add where clause 5 23
SQL 2012 clustering 9 11
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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