+ Reply to Thread
Results 1 to 12 of 12

Bar Chart with all Project Tasks color coded in a single bar

  1. #1
    Registered User
    Join Date
    07-19-2014
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    12

    Question Bar Chart with all Project Tasks color coded in a single bar

    Hi All,

    I have a list of projects. Each project has a set of tasks and a start date associated with the tasks that go one after the other. I am looking to create a bar chart with this data.
    • Vertical Y axis would be project name.
    • Each bar (for a project) depicts the color-coded project tasks one after the other, as per the start dates in the data table. Each task has its own color, and is maintained in the bar for all projects.
    • Horizontal X axis depicts timeline depending on the dates provided in the data table.
    • Legend shows the color-codes for each task type

    I have tried to mimic this output by putting it in Excel cells in the attachment, so as to provide an idea of how the final output should look like.

    Would appreciate if you could provide the steps to create this chart. I was unable to do this using the automated Excel chart options - it just splits out the tasks separately. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-19-2014
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    12

    Re: Bar Chart with all Project Tasks color coded in a single bar

    Experts, any help here please

  3. #3
    Registered User
    Join Date
    07-19-2014
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    12

    Re: Bar Chart with all Project Tasks color coded in a single bar

    Admin/Moderator: Can I cross-post this question on another forum/website? I would need help on this and hence would like to reach out to a wider audience.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,642

    Re: Bar Chart with all Project Tasks color coded in a single bar

    Perhaps the following will help.
    1. Convert the range A1:F4 to a table (tbl_Project)
    2. Use Get & Transform (standard with the 365 version) to unpivot tbl_Project, as modeled in tbl_Project_2 on sheet 2
    3. Populate the dates using
    a. =MIN(tbl_Projects_2[Value])-3 for the first date
    b. =SUM(F6,3) for subsequent dates
    4. Populate the F3:AC5 (sheet 2) using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    5. Set up a different conditional formatting rule for each task.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,642

    Re: Bar Chart with all Project Tasks color coded in a single bar

    Correction: The formula for #4 should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    07-19-2014
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    12

    Re: Bar Chart with all Project Tasks color coded in a single bar

    Thanks so much for your response. I actually mimicked the output within Excel cells, since I wasn't able to generate this in Excel charts. I mimicked it over there just to show how it should like like in the charts.

    Is there any way we can achieve this in 2-D Column/Bar Charts, instead of using the cells?

  7. #7
    Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    272

    Re: Bar Chart with all Project Tasks color coded in a single bar

    This is a Chart of type: Bars
    Is this what you have in mind ?
    Attached Files Attached Files
    Kind regards,
    Piet Bom

  8. #8
    Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    272

    Re: Bar Chart with all Project Tasks color coded in a single bar

    Just a little update to make it more complete.
    Hope this is what you were looking for.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-19-2014
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    12

    Re: Bar Chart with all Project Tasks color coded in a single bar

    This is amazing! Exactly how I wanted, even better! Thanks so much for all your efforts in putting this together.
    • Could you please let me know the steps on how to create the chart?
    • Can we not have the StartWeek, Weekday Duration, Weekend Duration, Task Date Durations, Weekend Bands calculation shown on the cells? I do not want to show this to the end user since it will confuse them. I want to keep the layout as simple as the one that I had provided initially.
    • I want to keep one sheet (Projects) where the end user would keep adding projects and the respective dates for each task/event, and then the next sheet (Chart) would have the bar chart, which would keep updating itself based on the information in the 'Projects' sheet. Will the chart update itself as I keep adding new rows to the Projects sheet?

  10. #10
    Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    272

    Re: Bar Chart with all Project Tasks color coded in a single bar

    As a guide I added sheet: HowToDoIt
    It is only a beginning…….and to be continued
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-19-2014
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    12

    Re: Bar Chart with all Project Tasks color coded in a single bar

    Sure, thanks, will wait for an updated version. Also, would appreciate your response on the list of questions I had just to have a better idea if it's possible

  12. #12
    Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    272

    Re: Bar Chart with all Project Tasks color coded in a single bar

    a step further…..
    there should come a time that you pick it up and go on...…
    1. In the attachment you will find a worksheet: ReadMe
    2. You can move the chart to as a sheet. Then the data can be hidden for the customer
    3. More Project rows can be added simply by inserting lines
    Kind regards,
    Piet Bom
    Attached Files Attached Files

+ 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. Coded MAIN Workbook to open and perform tasks in other Workbooks
    By hammer2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2017, 05:08 AM
  2. Replies: 2
    Last Post: 04-15-2013, 08:50 AM
  3. Macro to export tasks from MS Project to Excel 2010
    By desibabuji in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-26-2012, 01:37 AM
  4. Gantt chart - multiple tasks in a single row?
    By ABSTRAKTUS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2012, 01:02 PM
  5. Importing tasks from ms project
    By adas12adas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2009, 08:21 AM
  6. How can I move Project tasks into an Excel outline?
    By Russell Daum in forum Excel General
    Replies: 0
    Last Post: 06-24-2005, 12:05 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