+ Reply to Thread
Results 1 to 6 of 6

Removing gaps in column chart for missing data

  1. #1
    Registered User
    Join Date
    09-05-2012
    Location
    Bellingham, WA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Removing gaps in column chart for missing data

    Hi-
    I have data that spans numerous months and sites. I was measuring species found there so I need all of the species to show up, but some sites or months don't have that particular species. How can I get the gaps in data to disappear so it shows the data together? The example shows the data and how it is charting. I'd like, for example, Hozomeen - April to have no white space, but rather to place Oligochaeta and Terrestrial right next to each other.

    Thanks in advance for your help. I've been scouring threads all afternoon.

    Emily
    Attached Files Attached Files
    Last edited by WiscoEm; 02-16-2014 at 08:06 PM. Reason: needed to attach data

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Removing gaps in column chart for missing data

    Hi WiscoEm,

    I recently did the same thing for another user. Download the file in post #4 of the following thread:
    http://www.excelforum.com/excel-char...bar-chart.html
    Try it out and see if it does what you want and you can live with the restrictions provided in the solution.

    If it does, reply here. I can probably customize the solution for your specific chart (which I have already downloaded and viewed). I am very busy now, so the customization will probably take about a week. The hard part is not eliminating the spaces, but using VBA to recreate the chart you created using the 'Chart Wizard' (and possibly manual commands).

    Lewis

  3. #3
    Registered User
    Join Date
    09-05-2012
    Location
    Bellingham, WA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Removing gaps in column chart for missing data

    Lewis-
    That's similar but it still has the a gap (just with a zero) in many of the x-axis variables (fixed annuities in Q2-2013 etc). Also, I'm not sure how to get the second category x-axis with this method. The way I understand your response is that the only way I can do this is with Macros? I'm not very good at those yet so maybe I'll just deal with the spaces. I really appreciate your response.
    Emily

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

    Re: Removing gaps in column chart for missing data

    You can do it by altering your data layout.

    format series to have 100% overlap and zero gap width.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    09-05-2012
    Location
    Bellingham, WA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Removing gaps in column chart for missing data

    Andy-
    Thanks so much. That is exactly what I was looking for. One other question: I can't seem to figure out how to remove the little tabs between the columns. It isn't that important but it's going in my thesis and isn't supposed to have those. Is that possible?

    I really appreciate your help.

    Emily

  6. #6
    Registered User
    Join Date
    09-05-2012
    Location
    Bellingham, WA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Removing gaps in column chart for missing data

    Thanks Andy - I figured it out. I was making it more difficult than it was. I appreciate your help.
    Emily

+ 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] Clustered Column Chart - Removing Gaps for Zero Value Series
    By rzrbkpk in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 08-30-2013, 11:02 AM
  2. Fill in missing gaps in dates
    By wazimu13 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2013, 10:43 AM
  3. Removing Gaps in Charts
    By o4008 in forum Excel General
    Replies: 3
    Last Post: 02-18-2011, 11:09 AM
  4. Removing gaps from data extraction
    By sigfreid in forum Excel General
    Replies: 14
    Last Post: 11-14-2009, 08:20 AM
  5. [SOLVED] Transpose Column With Gaps to Row With No Gaps?
    By SteveC in forum Excel General
    Replies: 5
    Last Post: 06-17-2005, 08:05 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