excelexcel-chartsspill-range

How can I get my Excel spill range to be grouped by a specific column for a chart?

I need to produce a chart in Excel to show a quantity of items for a number of categories, grouped by the month they were received. At the moment, every occurrence of the Month shows up in my clustered column chart. I can manually do a table and remove all except the first occurrence of each month, but I need it to be done dynamically as the date range can be changed (between 1 and 12 months). I have a full list of data for 12 months, showing Types and their Quantity received. I've filtered the list to just show the types and quantities received between the date range selected (picture 1).

The three images below show the spilled data, the current chart, and how I would like the chart to look. If it cannot be achieved directly using a spill range, then could anyone suggest another approach to have my data dynamically displayed in a chart, grouped by month?

Spill range

Chart

Correct Chart

Solution

  • Not a perfect answer but here's a start:

    You could paste:

    =IF(COUNTIF(F$5:F5,F5)=1, F5,"")

    in J5, and drag this down a sufficient way to cover the expected length of the dynamic array.

    Then paste:

    =CHOOSECOLS(F5#,2,3)

    in K5, and use these three columns for your graph data.

    I can't think of a way to make the "J" column dynamic at the moment.

    EDIT:

    Pasting:

    =XLOOKUP(ROW(F5#), XMATCH(UNIQUE(CHOOSECOLS(F5#,1)),CHOOSECOLS(F5#,1))+4, UNIQUE(CHOOSECOLS(F5#,1)),"")

    in J5 should work dynamically too.