Stacked column chart in Excel

Question

I have data in a sheet like the image below.

The relevant portions are subtasks, shown in column B, subtask times, shown in column I, and daily totals, shown highlighted in green in the row at the start of each day's data.

Note that the general pattern is a daily total row followed by that day's subtasks. However, each day can have a different number and mix of subtasks.

I am trying to create a stacked column chart, with one column for each day. The gist is illustrated in the sketch, below (although the values and series don't exactly match the portion of the spreadsheet shown above).

The chart shows times aggregated by task. Tasks are defined by the character portion of the subtask name to the left of the hyphen in column B of the spreadsheet. So, for example, ABUDHABI is a task that has values in each day's column. On 09-02-16 in the sample data, it has five component subtasks that will be aggregated.

The task times may add up to less than the total workday (the green rows). In that case, the difference would be included as a "dummy" task called "Overhead".

What I'm having difficulty with is extracting and aggregating the pieces and putting them into a form for Excel to turn into this type of chart.

Answer 1

Here's a solution that requires a minor edit of your data collection table.

1. Re-format table with only necessary columns and rows (i.e. NO summary/subtotal rows). I've added two columns, one to calculate your subtask, based upon your detail column. The other calculates your overhead time by looking to see if there's a delta (created by a start/end time in your tasks) and if not subtracting your total committed time per day from 8 (assumed work day length).

2. Convert table to an Excel Table, Insert > Table. This table will grow automatically as you add your data-it is your data collection point.

3. Insert a Pivot Table using your Excel Table as a data source, Insert > Pivot Table. It can be on the same or a different worksheet.

4. Format your Pivot Table, with:

   • Row Labels = Date
   • Column Labels = Subtask
   • Values = sum of time.

5. Insert a Pivot Chart using your Pivot Table as a data source, Insert > Pivot Table > Pivot Chart. The layout and formatting should follow your pivot table and provide the detail your looking for.

As you add data to your table, simply refresh your pivot table and it will add columns/rows for new dates or subtasks. If you filter your Pivot Table or Chart, the other will follow so you can look at date ranges or specific tasks.

Here's a quick sample: