Solved

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

Posted on 2014-04-28
8
203 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 (Microsoft MVP / EE MVE) 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 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