Too many variables for a chart?

[SandsB]

I do charts all the time but this has me stumped. The image attached shows a small subset of my data; goes back years. I need to use this data to get a stacked bar chart showing, for each location, the opened and closed for each day. Since the top row showing the dates has 2 columns for each day, I don't know how to do a normal highlite/insert chart. What's the trick?

 

[6StringJazzer]

A chart series can't do alternating columns like that. You will either have to reformat your data, or create another table using formulas that formats the table appropriately for a chart. Since you're using 365 there might be a way to do this with new functions and named formulas as the series, but personally I prefer to see the actual data on a worksheet.

One way to do it is to have one table for Opened, another table for Closed. That would probably be the quickest to get where you need to go.

 

[alansidman]

You could use power query to join the two header rows into one and then you could build your charts.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type date}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Column1", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"