+ Reply to Thread
Results 1 to 11 of 11

Vertical line in clustered column chart

  1. #1
    Registered User
    Join Date
    07-26-2017
    Location
    melbourne, australia
    MS-Off Ver
    2016
    Posts
    23

    Post Vertical line in clustered column chart

    Hi - In the attached example I have a column chart with pricing values along the x axis. I would like to draw a red vertical line where I've specified "Suggested Pric" in cell c17. Every attempt I make squashes or distorts my chart. How can I accomplish this?
    chart question.JPGe
    Last edited by gnewmal; 08-20-2017 at 07:29 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Vertical line in clustered column chart

    There is no attachment.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-26-2017
    Location
    melbourne, australia
    MS-Off Ver
    2016
    Posts
    23

    Re: Vertical line in clustered column chart

    Here is the attachment
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Vertical line in clustered column chart

    Thanks. I don't see any attempt at a vertical line there - please provide the version that has your attempt in it so that we can see the squashing problem you describe.

  5. #5
    Registered User
    Join Date
    07-26-2017
    Location
    melbourne, australia
    MS-Off Ver
    2016
    Posts
    23

    Re: Vertical line in clustered column chart

    Attached is the chart. I added another series to the chart, which consists of a point. I then changed the chart type to include scatter. But the chart get's squashed when I do this.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Vertical line in clustered column chart

    OK - and how do you want it to look? can you manually mock up what you want to see?

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Vertical line in clustered column chart

    Here is an idea.

    Without manipulating the X values in column B, it would be hard to insert the 1,600 between 1,595 and 1,645 so the point is it's own bar on the chart.

    There could be others way, but this is a suggestion.
    Attached Files Attached Files
    HTH
    Regards, Jeff

  8. #8
    Registered User
    Join Date
    07-26-2017
    Location
    melbourne, australia
    MS-Off Ver
    2016
    Posts
    23

    Re: Vertical line in clustered column chart

    Here's a mock up. The red vertical line would represent the value of $1595 based on the x axis.

    vertical line.JPG

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Vertical line in clustered column chart

    The thing to recognize when you combine a scatter chart on top of a column chart is that the horizontal axis of a column chart is not "numeric" -- it is a category axis. The number $1595 has no numeric meaning on the chart. When you combine a scatter chart with other chart types, each category is simply counting numbers to the scatter chart. So category 1395 has an X value of 1, category 1595 has an X value of 5, and so on. Edits I tried (Remember that I am using 2007, so you will need to find the equivalent commands in 2016):

    1) Enter 5 (instead of 1600) into C17. The chart changes to the correct X axis.
    2) Select the suggested price series and
    2a) Chart -> Layout -> Error bars -> Add error bars with percentage.
    2b) Delete the horizontal error bars
    2c) Select the vertical error bars -> Format selection -> minus error bar only -> 100%
    2d) Format the error bar as desired

    That got me really close to your most recent picture. Is that what you need, or is there more?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  10. #10
    Registered User
    Join Date
    07-26-2017
    Location
    melbourne, australia
    MS-Off Ver
    2016
    Posts
    23

    Re: Vertical line in clustered column chart

    that is what I'm looking for and your explanation makes sense now. Ideally I want the vertical line to change as the value changes but this will work for now. thanks so much for your help

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Vertical line in clustered column chart

    Ideally I want the vertical line to change as the value changes
    This should be as easy as some kind of lookup function or similar. For example, if you put 1600 into D17, the formula in C17 could be =MATCH(D17,B5:B14,1) (note the 1 instead of the more common 0 in the 3rd argument) which would return 5.

+ 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. [SOLVED] Is it possible to plot a column chart as a vertical line
    By luv2glyd in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-14-2017, 02:06 PM
  2. Replies: 3
    Last Post: 10-29-2015, 10:58 AM
  3. add dynamic vertical line to column chart
    By ammartino44 in forum Excel General
    Replies: 2
    Last Post: 09-17-2015, 06:02 PM
  4. Column chart with vertical line
    By ali_excelforum in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-05-2015, 03:45 PM
  5. Vertical Line in Column Chart
    By Beginner Level in forum Excel General
    Replies: 7
    Last Post: 03-15-2012, 02:12 AM
  6. Adding a vertical line to a column chart
    By Bill Goszinski in forum Excel General
    Replies: 1
    Last Post: 07-28-2006, 07:55 PM
  7. how to add line to stacked-clustered chart?
    By hifert in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-19-2005, 02:18 AM

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