+ Reply to Thread
Results 1 to 7 of 7

Stacked columns chart - use non-numerical data in the vertical axis

  1. #1
    Registered User
    Join Date
    06-10-2014
    MS-Off Ver
    2013
    Posts
    6

    Stacked columns chart - use non-numerical data in the vertical axis

    I would like to create a chart with stacked columns. Usually, the data on the Y-axis is some kind of numerical data, such as budget.
    I would like to have the Y-axis have non-numerical data (= nominal categories).
    Example :
    Take the following data :
    category | productname
    red berry
    red carrot
    green cabbage
    green apple

    The ouput format i want is a chart which shows on the horizontal axis the categories red and green, and stacks in each category the right productnames on top of eachother; preferably each productname should have a different colour and showing data labels (e.g. "1", "green","apple" could be 1 specific data label).

    I don't know how to achieve this, because if you display data as "count of productnames", then automatically you get something like a block with value 2, which doesn't show the different productnames inside that block.

    Can the desired chart be made with Excel somehow?
    Thanks...

  2. #2
    Registered User
    Join Date
    06-10-2014
    MS-Off Ver
    2013
    Posts
    6

    Re: Stacked columns chart - use non-numerical data in the vertical axis

    I would still like to know if it is theoretically possible to stack data in column charts, where the Y-axis data is non-numerical. I would expect then that each element in the stacked columns has size 1, but just basically is a different "label".

    For example : you want to stack area codes (= no numerical or ordinal sense to it) on top of each other vertically (Y), organized in per state horizontally (X), where each stacked block in the column represents 1 area code "label", each of equal size. Would appreciate feedback on wether this can be done in Excel.

    Ideally each block in the stacked column should have a different colour additional with its "label" name.

    (Off couse, some columns will be larger than others, just like some states have more area-codes than others.)
    Last edited by guillermo_A; 06-18-2014 at 03:41 AM.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Stacked columns chart - use non-numerical data in the vertical axis

    The chart has to have a number to plot in order to give the column data point a height.
    If you add a value field you can plot the min in order to get similar sized columns but the data label will not display the correct count of items.

    As the chart is not really providing any additional information why not simply use the pivot table itself?
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    06-10-2014
    MS-Off Ver
    2013
    Posts
    6

    Re: Stacked columns chart - use non-numerical data in the vertical axis

    Thanks very much, that was exactly the chart i was looking for.

    I had some difficulty to adjust the datalabels all in one operation,
    Whenever i tryed to change them all with "datalabels>more options>add series name", Excel just adjusted one of them.

    It seemed like a bug to me, but i found a workaround for that.
    (http://stackoverflow.com/questions/1...simultaneously)

  5. #5
    Registered User
    Join Date
    06-10-2014
    MS-Off Ver
    2013
    Posts
    6

    Re: Stacked columns chart - use non-numerical data in the vertical axis

    I was thinking to add a complexity.

    Would it be possible to have the following situation :

    category1 category2 productname value
    red in stock berry 1
    red in stock carrot 1
    green sold out apple 1
    green in stock cabbage 1

    Now, i would like to create a chart with following specs :

    X-axis : shows the products grouped by category1 (red / green)
    datalabels : are provided and show at least the productname
    legend : has 2 random colours which represent in stock / sold out status.

    So you would have stacked columns :

    |
    | apple carrot
    | cabbage berry
    -------------------------------->
    green red

    Important : only apples should be in a certain colour appart from the others,
    as only apples are sold out. the other 3 should have the same colour.


    (Note: the order of the stacking is not so important, e.g. alfabetical)

    Thanks for guidelines or help on this.

  6. #6
    Registered User
    Join Date
    06-10-2014
    MS-Off Ver
    2013
    Posts
    6

    Re: Stacked columns chart - use non-numerical data in the vertical axis

    Any news on added complexity?

  7. #7
    Registered User
    Join Date
    06-10-2014
    MS-Off Ver
    2013
    Posts
    6

    Re: Stacked columns chart - use non-numerical data in the vertical axis

    I have added a chart example to explain the question better.
    I want the labels to shout out all the fruits..., in whatever category they are.
    (so i should see 4 data labels in my chart). Unfortunately, whatever i try, excel
    always summarizes certain items, and as an effect of that the individual data labels
    get lost.exel_question_apples.jpg

+ 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. Replies: 1
    Last Post: 05-30-2013, 10:03 AM
  2. Changing Y Axis to Non-numerical value for Stacked Column Chart
    By Sweetsting in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-05-2013, 02:44 AM
  3. Replies: 5
    Last Post: 07-16-2011, 08:07 AM
  4. Stacked Bar Chart - Two Vertical Axis Labels
    By cjsec9 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-18-2008, 02:44 PM
  5. [SOLVED] Create stacked columns chart but with 2 columns for each x axis?
    By PSM in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-07-2005, 11:06 AM

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