+ Reply to Thread
Results 1 to 15 of 15

Stacked Horizontal Bar Chart / Help with Dates

  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Stacked Horizontal Bar Chart / Help with Dates

    Hello Friends

    I am new here and have a query with regards to stacked horizontal charts.

    Basically I want to create something like this:

    cr61.jpg

    But I can't get the Stacked Horizontal Bar chart to look like that. Can some please tell me what my cell data needs to be like and what edits I have to make to get my Stacked Bar chart to look like that?

    I would greatly appreciate any help.

    Thank You.

    J.

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

    Re: Stacked Horizontal Bar Chart / Help with Dates

    Hard to tell from the picture what data you have.
    The horizontal axis looks like dates but they don't appear to tally with the numbers in the bars.

    I'm assuming the numbers are days from a starting point of 1/jan/2013

    You need to include a dummy series to start the date from 1/jan. You also need to fix the horizontal axis Minimum value.
    The other values are simple number of days to stack.

    Vertical axis is in reverse crossing at maximum.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Stacked Horizontal Bar Chart / Help with Dates

    Thanks Andy

    You're right, the numbers on the bars are something different, so it is like saying:

    For Task A, from 02 Feb 2013 to 05 Apr 2013 we wrote (45K lines of code[red colour] and 15K test code[green colour]).

    How can I get Task A to start from 02 Feb 2013 instead of 01 Jan 2013?

    Thanks, you're a great help.

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

    Re: Stacked Horizontal Bar Chart / Help with Dates

    You will need to calculate the percentage of codeline and text codelines and aportion the time elapsed.

    I have added another series so you can display start and end dates of project. I've set the transparency for those 2 series to 80% but you can remove fill completely once you understand chart construction.

    The data labels for 3 of the series are linked to cells.
    this free addin will help with that
    http://www.appspro.com/Utilities/ChartLabeler.htm
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Stacked Horizontal Bar Chart / Help with Dates

    Thank you so much Andy.

    So I take there is no way to not have that date leading to start of project (assume all tasks start on same date) showing (light blue in your previous spreadsheet) in Excel?

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

    Re: Stacked Horizontal Bar Chart / Help with Dates

    If you don't want the date labels just select and delete the data labels.

  7. #7
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Stacked Horizontal Bar Chart / Help with Dates

    I think I was not clear in my previous post I have drawn up my vision of the chart in Photoshop, see below:

    Attachment 270453

    Can such a thing be made with Excel (note how start date is not 1st of Jan) ?

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

    Re: Stacked Horizontal Bar Chart / Help with Dates

    your link reports as invalid

    you can change the start date by altering the Min value of the horizontal scale.

  9. #9
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Stacked Horizontal Bar Chart / Help with Dates

    Quote Originally Posted by Andy Pope View Post
    your link reports as invalid.
    That's weird! It works fine for me.

    Here is a link to the image.

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

    Re: Stacked Horizontal Bar Chart / Help with Dates

    okay, reply still stands. alter the properties of the horizontal axis

  11. #11
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Stacked Horizontal Bar Chart / Help with Dates

    Hmm I can't even see a horizontal (x) axis on the second spreadsheet you sent me!

    When I double click on the vertical axis I can access properties, but can't do same for x-axis since one doesn't exist

    I really appreciate your patience Andy, as you can tell I am a total n00b when it comes to this advanced (by my standards) stuff.

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

    Re: Stacked Horizontal Bar Chart / Help with Dates

    it's there I just remove the labels, tick marks and colour.

    Quick way back is to set it to None and then Default.
    Use the ribbon, Chart Tools > Layout > Axes > Axes > Primairy Horizontal > None.

    then again selecting Show Default Axis.

    You can then format the Min/Max

  13. #13
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Stacked Horizontal Bar Chart / Help with Dates

    Thanks Andy, got the axis to show again, that wasn't very intuitive to me!

    Ok, I got the dialogue box, but check this out, the minimum is currently set to 41275.0 .. what on earth!!?? I was expecting 01 Jan 2013 !

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

    Re: Stacked Horizontal Bar Chart / Help with Dates

    excel stores dates as numbers.

    41275 is 1/jan/2013

    You can change the number format from Date to General to see numeric value for date.

    Charts only really do the category axis as dates. Whether that's a vertical or horizontal layout.
    You are wanting dates on the Value axis.

    You can enter dates into the box and it will convert to number when you press ENTER

  15. #15
    Registered User
    Join Date
    10-11-2013
    Location
    Huddersfield
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Stacked Horizontal Bar Chart / Help with Dates

    Genius, thank you so much

+ 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] Dates by Month on X-Axis in Horizontal Stacked Bar Chart
    By Bandolier2k in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 05-16-2018, 11:02 PM
  2. Stacked waterfall chart with horizontal line
    By kalam1989 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-28-2013, 12:42 PM
  3. Stacked Column chart that groups dates by month
    By bigban in forum Excel General
    Replies: 2
    Last Post: 07-08-2011, 05:37 AM
  4. Stacked Bar Chart using Dates as my data
    By kdestef1 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-26-2010, 07:54 PM
  5. change percent for stacked horizontal bar chart
    By fbarbie in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-28-2005, 09:30 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