I have data in a sheet like the image below.

The data format in the spread sheet

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).

A sketch of the chart

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.

  • Any chance you could mock up what you're looking for, even if you draw it in crayon on a napkin and snap an image with your cell phone? It's hard to visualize from the description. – fixer1234 Feb 11 '16 at 6:08
  • @fixer1234 I will try :) – 7heViking Feb 11 '16 at 6:12
  • 1
    @fixer1234 Here you go :) – 7heViking Feb 11 '16 at 6:26
  • 1
    Damn, you're good with a crayon! – fixer1234 Feb 11 '16 at 6:28
  • @fixer1234 The chart only shows the general concept and somehow matches the data in the given sheet. The B column specifies the data group and the I column the amount. All ABUDHABI-nnnn should be grouped in the same group and only show as a single part of the column. The problem is mainly how to get the relevant data from the sheet but also how to add them to the chart. I hope it clerifies it a bit. – 7heViking Feb 11 '16 at 7:00

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:

Subtasks Chart

  • Nice job. One suggestion. For some reason, the daily "totals" in the question don't appear to be a consistent amount, like 8 hrs. Capturing the data in a better format is a good idea. Perhaps the exact "overhead" should be entered as a subtask. – fixer1234 Feb 11 '16 at 17:52
  • Thanks, and good point. The formula in the time column actually does look for a value in the "overhead" subtasks delta and adds it if explicit or uses the difference to 8 hours otherwise. – dav Feb 11 '16 at 18:02
  • One other thing that isn't clear is how you parse the task from the subtask names. – fixer1234 Feb 11 '16 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.