+ Reply to Thread
Results 1 to 9 of 9

Change chart/pivot table numeric labels to associated text labels

  1. #1
    Registered User
    Join Date
    11-20-2012
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Change chart/pivot table numeric labels to associated text labels

    Hello,

    I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.

    For example, variable ASSIGNMENT has the following possibilities:

    1
    2
    3
    4

    Here's what each of those "mean" (I have this in another table):

    1 - Sick
    2 - Overtime
    3 - Court
    4 - Present

    How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?



    Thanks for any guidance/solutions!

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Change chart/pivot table numeric labels to associated text labels

    can you add a helper column?....then you can do a lookup for those names and create the PT/Chart using that....does that make sense?
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    11-20-2012
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Change chart/pivot table numeric labels to associated text labels

    Yes, I can create a helper column.

    What would you suggest for the best lookup configuration?

  4. #4
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Change chart/pivot table numeric labels to associated text labels

    See if this file works?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-20-2012
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Change chart/pivot table numeric labels to associated text labels

    Amazing. Thank you.

    Question: did you use the IFERROR in the event there was a value in the original column that didn't have a text value associated with it in the VLOOKUP? I'm wondering why one can't just have the VLOOKUP.

  6. #6
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Change chart/pivot table numeric labels to associated text labels

    absolutely correct....I just happen to alway wrap my VLOOKUP in IFERROR....but you can remove that part if you choose....

  7. #7
    Registered User
    Join Date
    11-20-2012
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Change chart/pivot table numeric labels to associated text labels

    Cool.

    And now that I'm applying it to my spreadsheet, another question: VLOOKUP looks to the left most column in an array. If I have my text values in column A and numbers in column B I either need to switch them (which I can do), or use something besides VLOOKUP. Is there a similar formula where I can tell it which column I want it to "look" for?

  8. #8
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Change chart/pivot table numeric labels to associated text labels

    check INDEX/MATCH lookups....it's a little weird to use at first but very powerful...give it a try and if you have difficulties give me a shout and I'll see what I can do.....have fun

  9. #9
    Registered User
    Join Date
    11-20-2012
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Change chart/pivot table numeric labels to associated text labels

    Will do. Thanks again for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table - Show Row labels horizontally (Not as Column labels)
    By Catsup in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-16-2014, 01:15 PM
  2. [SOLVED] How to Plot Data Labels on a Chart with a text value instead of a numeric value
    By Rollinstone12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-29-2012, 03:41 AM
  3. Replies: 0
    Last Post: 07-03-2012, 11:09 AM
  4. Change Event labels on Chart
    By JK1234 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-08-2008, 03:50 PM
  5. Change XY tick labels from numeric to text
    By jennifer in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-20-2006, 03:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1