+ Reply to Thread
Results 1 to 11 of 11

Gaps between columns

  1. #1
    Registered User
    Join Date
    07-10-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    34

    Gaps between columns

    Hi there,

    I'm having trouble getting rid of the gaps in the bar graph ('volume' data). Please help!

    Thank you in advance!

    -Wikster7
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,478

    Re: Gaps between columns

    You are using Date axis for your X axis. This is what is causing gaps as you have missing dates.
    Change it to Text, since it's not contiguous series and you want it to be treated as Categorical rather than Continuous axis.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    07-10-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    34

    Re: Gaps between columns

    Hi CK76,

    Thank you for the help. I changed the dates to text and it got rid of the gaps, but it flipped the entire graph and I don't know how to flip it back. Please see the images below: original chart (first) and what I have now (second).

    Original.PNG

    Flipped.PNG

    Thank you!

    -Wikster7

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,478

    Re: Gaps between columns

    Go to Chart Elements menu -> Axis.

    Set both the primary and secondary Horizontal to reverse order (and as text type), and then remove the one you don't need.

  5. #5
    Registered User
    Join Date
    07-10-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    34

    Re: Gaps between columns

    I don't think I did it right. Images below.

    format axis.PNG

    reverse order.PNG

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,478

    Re: Gaps between columns

    You'd need to set both to reverse order for horizontal. I think you only have primary horizontal in reverse.

    Now this may inverse the format for Volume Series. If that is undesired. You'd just need to sort the source data.

    Below is sample with source data sorted oldest to newest on date column.
    0.JPG

  7. #7
    Registered User
    Join Date
    07-10-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    34

    Re: Gaps between columns

    I'm not sure how to do what you outlined, but I just reversed the raw data and it did the trick.

    Thank you for your help CK76!

    -Wikster7

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    Re: Gaps between columns

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  9. #9
    Registered User
    Join Date
    07-10-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    34

    Re: Gaps between columns

    I actually have one last question!

    How do I format the x-axis so that only one date per quarter is shown?

    Thanks!

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,478

    Re: Gaps between columns

    You can adjust Label's Interval unit. Ex: 90
    However, since you have some missing dates. It won't be exactly by quarter.
    0.JPG

    In addition to above, you can set Number format in Axis option to something like mmm-yy (interval unit set to 60), and hit "Add"
    1.JPG

  11. #11
    Registered User
    Join Date
    07-10-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    34

    Re: Gaps between columns

    Thank you for all the help CK76 - it's much appreciated!

    -Wikster7

+ 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. Copying fomulae across columns but with gaps
    By graeme27uk in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-24-2018, 01:17 PM
  2. Match columns, sort and add gaps in row/s if required
    By ram.excelforum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2018, 11:38 PM
  3. Retrieval of values from columns with gaps between month columns
    By pvp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2016, 06:19 AM
  4. Average between two columns that have gaps
    By arcolombo698 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 09:07 PM
  5. Compiling several columns into one, eliminating gaps!
    By Tayloroid in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-18-2013, 11:02 AM
  6. Creating a list from two columns without gaps
    By fielg in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2012, 09:23 AM
  7. remove gaps between columns excel 2007
    By kamelkid2 in forum Excel General
    Replies: 3
    Last Post: 02-01-2011, 01:25 PM

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