2-D Stacked bar chart

I'm not sure what you have tried. It looks very similar to something like this thread: https://www.excelforum.com/excel-cha...cel-chart.html Most of the work will be taking the four tables you have and combining them into one table (or two tables -- one for each chart). Look over that discussion, then we can work on adapting your data to the strategy outlined there.

This group prefers that you upload your file direct to the forum. See the yellow banner(s) at the top of the page for instructions for attaching your spreadsheet file to your post.

As I noted, the bulk of the work is combining the data from 4 separate tables into 1 table, and I see no attempt to combine the tables. What I did in your file:

1) in A40:A43, enter each person (person1, person2, person3, person4).
2) in B39, I enter "start". In B40:B43, enter 06:00 (since each starts at 6 AM).
3) I note that the maximum number of tasks for each person is 5, so I enter 5 sets of "A, B, C, D, E, F, G" into C39:AL39.
4) In C40:AL43, I enter either 0 (for tasks that a person did not perform in that "set") or a copy of the duration from rows 3 to 7 where a person did perform a task.
5) Select A30:AL43 -> insert stacked bar chart.
5a) My copy of Excel defaulted to "series in rows" so I execute "switch row/column" to get Excel to chart series in columns.
6) Format the "start" series (column B) to be invisible. Format each "A" series to be the same color, each "B" series to be the same color, and so on.

A similar sequence for the chart showing "tasks" instead of "persons."

Does that help you see the kind of rearranging of the data that is needed for this kind of chart?

I think there is a restriction for attaching files for new users

There never has been before. Make certain that you are following the instructions above exactly.

There might be more than five tasks for each person during the day.

In my experience with this sort of thing, one key part of setting up a good template is identifying some kind of maximum number of tasks per person per day. Your example showed 5 tasks per person per day. You as the programmer will need to determine what the actual maximum number of tasks per day will be in order to set up the template.

it doesn't work when a person does a task more than one time during the day.

I would have to see what you are trying to do. I see no reason why you could not set this up so that a person does the same task multiple times over the course of the day.

If it helps, here's how I set it up (limit of 5 tasks per person per day).

Originally Posted by ahs74

Under posting permissions, it says:
...
You may not post attachments

This is wrong, sorry. Anyone can attach files to a post. I'll see if I can get that fixed, but I don't have privileges to do it myself.

You may have bumped into a size limit. The limit for .xlsx files is 1MB. If it's larger you can zip it or convert to .xlsb. For some reason, the limit for those file types is 9.77MB.

I was able to view your hand drawn pictures, and I thought I had come pretty close to creating the same thing in the stacked bar chart. It was quickly done and I left some of the "prettying up" undone, but the stacked bar chart I made seems pretty close to the stacked bar chart you drew.

Your chart creates different legends for the same tasks that repeat during the day

It should be fairly easy to delete the extra legend entries (Select the legend, then select the individual, extraneous legend entries, then delete the legend entry). Again, since most of the work in this task is arranging the data in the spreadsheet, I focused my time/effort on showing how the data should be arranged in the spreadsheet. Assuming you know how to do the necessary formatting, there should be plenty of formatting and such to clean up the chart.

It doesn't seem to be very user-friendly. I prefer having separate tables for different workers and enter the start and end time for each task for each person (like what I've done in my spreadsheet); However, I want to summarize all these tables in two charts.

Of course, a lot of spreadsheet programming is trying to make spreadsheet user friendly. Again, I tried to make something quick to demonstrate how the data needs to be arranged. There is a lot of work to go through to make this more user-friendly.

I can appreciate your preference for individual tables for each person. One of the premier charting gurus, Jon Peltier, has this to say: "What if you want to plot your data but also display it in an optimally formatted way? The good new is, you can. The bad news is, it takes more work. You should have two different data ranges: one arranged for best charting outcomes, the other formatted for visual consumption. Both of these ranges should link to the same original source, to make sure changes are reflected everywhere, and you maintain one version of the data." (see here: https://peltiertech.com/good-chart-data/ ). I think a big part of programming this template is to determine exactly how you want to interact with the sheet, recognize that it's okay (and maybe necessary in this case) to have two or more copies of the data (properly linked so that you need only edit the data in one place). In this case, we probably need to figure out lookup or similar formulas for B41:AL44 that will retrieve the necessary information from the four tables above (or some other strategy for bringing the necessary data into the chart table from wherever the data have originally been entered). I might even go so far as to say that this might be easiest to do as a database project where we enter the raw data into a good database, then build both your individual person tables from that database and build the chart table from that database.

Anyway, all that to say that, yes, there is still more programming to do in order to streamline this. As you determine what kind of strategies and such you want to use to accomplish these tasks, let us know how we can help with that programming.

Excel can do this. One possible way to automate the copying of the data in the top tables into the chart table might look like (starting from my file in post #8):

1) In row 39, replace the 1st task, etc. with 1 in C39:I39, 2 in J39:P39, and so on.
2) Replace Aduration, etc. in row 40 with simple A, B, C,...
3) Build a formula in C41 that will a) test if the task in A3 matches the task in C40 then, b) if it matches, return the value from D3, else c) return 0. In a spreadsheet, this kind of decision is performed by an IF() function (https://support.microsoft.com/en-us/...c-aa8bbff73be2 ).
3a) Use an INDEX() function (https://support.microsoft.com/en-us/...2-b56b061328bd ) to access the value in the first cell of A3:A7. INDEX($A$3:$A$7,C$39) [note the mix of relative and absolute references for easy copying]. Compare to the value in C40 -- INDEX(...)=C$40. Use that as the test condition in the IF() function =IF(INDEX(...)=C$40,...)
3b) If true, then we want to use and INDEX() function to return the value from D3, if not, return 0 =IF(INDEX(...)=C$40,INDEX($D$3:$D$7,C$39),0)
4) Enter that function in C41. Then copy C41 and paste/fill into C41:AL41.
5) Repeat step 4 in C42, C43, C44, replacing the references for person 1 with appropriate references for person 2, person 3, person 4.

Now, you can enter your values in the tables at the top of the sheet, and those entries/edits should be reflected in the chart table and in the chart.

As I noted in step 6 of post #6, go through and format each "A" series to be the same color, each "B" series to be the same color, and so on. It will be easy, if a bit tedious the first time through. Then go through the legend and delete the "extra" legend entries. As I noted earlier, select the legend, then select the desired legend entry, then delete/clear. Again, it should be easy, even if it is a bit tedious the first time you set up the template.

Let me know if you have any difficulty with either of those tasks.

Delays, pauses, breaks will fit seamlessly into the current template if you treat these as their own task. Our identifiers are currently generic, so we could say that Task G represents the "delay, pause, break" task. If you are willing to treat these delays as if they are just another task, then it should fit seamlessly into the existing template. The challenge will be if you insist on not treating delays the same as other tasks.

Timing in the chart matches what is in the table. If I understand what you are trying to do, it appears that there is nothing in the formulas in the table in rows 49 to 55 that will detect "task X was not worked on by anyone for Y duration." If we can figure out how to include that kind of logic in these formulas, then the chart should follow the data in the table. I don't have time at the moment to think through the logic.

This is where it might be easier to have more of a database input format, then extract all of the different tables from that source data, rather than enter the data into the "single person" tables like we are currently doing. How willing are you to completely redesign the spreadsheet?

These aren't the kind of spreadsheets I design, so I am struggling (in the limited time I can devote to this) to come up with a good spreadsheet design. Hopefully someone else can chime in. My thought at present is that a good list "database" where you enter the data might be best. Your tables (both for reporting and for the chart) could possibly be extracted from the list/database using filters or pivots. I have sent out feelers into the community to see if anyone has ideas for designing the spreadsheet.

I have been working on this off and on since Mr. Shorty made the calvary call.
In its present form this is likely to be impractical, but perhaps it will inspire a thought in someone that will prove workable.
It seems to me that the Crew chart is working as desired, so what I am addressing is the Task chart.
I propose using a Gantt chart produced as follows.
1. Use Get & Transform to combine the person tables on Sheet 3 using the following code:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows1" = Table.SelectRows(Source, each ([Name] <> "Query1")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows1", "Content", {"Time Slot #", "Task Code", "Start", "End", "Duration"}, {"Time Slot #", "Task Code", "Start", "End", "Duration"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Start", type time}, {"End", type time}, {"Duration", type duration}, {"Time Slot #", Int64.Type}, {"Task Code", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Name], "_"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Name", "Time Slot #"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text After Delimiter", "Name"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Task Code", Order.Ascending}, {"Start", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Duration"})
in
    #"Removed Columns1"

2. From the query produced table (green/white) on the Query1 sheet use the following formula to fill cells H5:S11

Formula:

=AGGREGATE(15,6,(ROW(Query1[[Start]:[Start]])-ROW(Query1[#Headers]))/(Query1[[Task Code]:[Task Code]]=$F5)/((Query1[[Start]:[Start]]=H$3)+(Query1[[End]:[End]]=H$4)),1)

3. Use the following code to produce the Gantt chart in cells H16:S25

Formula:

=IFERROR(INDEX(Query1[[Name]:[Name]],IF(ISNUMBER(H5),H5,IF(COUNTIFS($H5:H5,INDEX($H5:H5,AGGREGATE(14,6,(COLUMN($H5:H5)-COLUMN($G5))/(ISNUMBER($H5:H5)),1)))<2,INDEX($H5:H5,AGGREGATE(14,6,(COLUMN($H5:H5)-COLUMN($G5))/(ISNUMBER($H5:H5)),1)),IF(COUNTIFS($H5:H5,INDEX($H5:H5,AGGREGATE(15,6,(COLUMN($H5:H5)-COLUMN($G5))/(ISNUMBER($H5:H5)),1)))<2,INDEX($H5:H5,AGGREGATE(15,6,(COLUMN($H5:H5)-COLUMN($G5))/(ISNUMBER($H5:H5)),1)),"")))),"")

4. Several conditional formatting rules are applied to the cells containing names as well as one for the blank cells.
Let us know if you have any questions.