+ Reply to Thread
Results 1 to 14 of 14

Overlapping Bars in Pivot Chart

  1. #1
    Registered User
    Join Date
    06-29-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    43

    Overlapping Bars in Pivot Chart

    Hi,

    I'm trying to create a Pivot Chart (bar chart) with a Secondary Axis, but I'm having a problem with the bars overlapping. I've seen workarounds for normal charts whereby one creates "dummy" series to create space, but I don't know how you achieve this in a Pivot Chart. I'd prefer to have the bars adjacent without needing to create any unnecessary series. Below is a screenshot of what I'm seeing:

    Overlapping Bars.JPG

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,082

    Re: Overlapping Bars in Pivot Chart

    If you attach the sample workbook, that would help, or, could you make the secondary series a line?
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    06-29-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    43

    Re: Overlapping Bars in Pivot Chart

    Hi,

    Attached is the file. I'd prefer not to use a line series for this graph.
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,082

    Re: Overlapping Bars in Pivot Chart

    Maybe you can change the series overlap and gap width with a transparent color.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-29-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    43

    Re: Overlapping Bars in Pivot Chart

    That's a possibility, but I would prefer side-by-side if possible. Thanks for the suggestion.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,082

    Re: Overlapping Bars in Pivot Chart


  7. #7
    Registered User
    Join Date
    06-29-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    43

    Re: Overlapping Bars in Pivot Chart

    Yeah, I saw this video, but this really only applies to "regular" bar charts, not the Pivot Chart that I'm dealing with. I think I'll just go with the partially transparent bar. Thanks for the help.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,082

    Re: Overlapping Bars in Pivot Chart

    You are welcome. Sorry I couldn't get you exactly what you wanted, but glad you have something to work with.

    I will post a message to our help thread to see if somebody else has another idea...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by jeffreybrown; 05-03-2018 at 03:11 PM.

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Overlapping Bars in Pivot Chart

    is that what you want?
    You can play with format series with size and overlap
    Attached Files Attached Files
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,082

    Re: Overlapping Bars in Pivot Chart

    Thanks Sandy. That appears to be a winner.

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Overlapping Bars in Pivot Chart

    @Jeff

    Really? I just watched linked video from your post#6. That's all

    edit:
    I forgot about PT for another use (not for chart only) so here is a full version
    and one more thing: I didn't switch columns/rows
    Attached Files Attached Files
    Last edited by sandy666; 05-03-2018 at 04:08 PM.

  12. #12
    Registered User
    Join Date
    06-29-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    43

    Re: Overlapping Bars in Pivot Chart

    Thanks, both of you. I can't believe that there's no built-in capability for Excel to move bar graphs off of one another when trying to use a secondary axis. Anyway, I'll play with both versions and see which I like better.

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Overlapping Bars in Pivot Chart

    You are welcome

    both version has the same PivotChart but first : one PivotTable, second : two pivotTables

    If that takes care of your original question, & to say Thanks, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
      then
    2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

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

    Re: Overlapping Bars in Pivot Chart

    Here a variation which uses additional columns within pt.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

+ 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 Chart Overlapping Axis labels
    By unsalted in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-22-2015, 08:44 PM
  2. Replies: 1
    Last Post: 07-22-2015, 07:16 AM
  3. Bar chart with secondary axis and overlapping bars
    By ashley.phillips in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-18-2013, 10:46 AM
  4. Chart data from pivot table using overlapping years
    By SueWithQuestion in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-18-2011, 12:20 PM
  5. How to Create an Excel Chart With Overlapping Bars
    By AccessGeek in forum Excel General
    Replies: 3
    Last Post: 03-22-2011, 09:16 AM
  6. Overlapping Bars in Chart
    By Jonathan78 in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 10-03-2010, 05:09 AM
  7. Chart with Secondary Axis but Bars are Overlapping
    By steven_thomas in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-23-2008, 06:20 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