Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-28
8
Medium Priority
?
220 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 23

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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 70

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 23

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

927 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