Forum Discussion

AmyEH's avatar
AmyEH
Copper Contributor
Oct 21, 2020
Solved

Stacked column chart creation - data reconfiguration

How do I rearrange this data in the most efficient way to create a stacked column chart from it?  The dates will be along the x-axis.  The phytoplankton concentration (cells/mL) will be on the y-axis...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 23, 2020

AmyEH 

Excel 365 will adjust the row and column headings dynamically.  Older versions and you might as well filter and copy them manually.

 

The Excel 365 formulas are

 

Row heading (Date )
= UNIQUE(Data[Date])
Column heading (groups)
= TRANSPOSE(UNIQUE(Data[GPNAME]))
Body (concentrations)
= SUMIFS(Data[cellsPerML2], Data[Date], date, Data[GPNAME], groups)

 

  • AmyEH's avatar
    AmyEH
    Copper Contributor
    Oct 24, 2020

    PeterBartholomew1 I don't think I have access to Excel 365, so this is only so helpful.  Thanks for offering advice!

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Oct 24, 2020

      AmyEH 

      I see your point!  It is possible to replace 

      = UNIQUE(Data[Date])

by

= SMALL(
    IF(
      MATCH(Data[Date],Data[Date],0)=ROW(Data[Date])-1,
      Data[Date]
    ), 
  k )

      but such a formula is such a dog by comparison that I decided it was time to dump my old Excel licence!  Now virtually every formula I write is a dynamic array formula.  It did mean moving to a subscription license though. 

       

      Anyway, I am pleased you got the Pivot Table / Chart to come to order!

Resources