Search code examples
excelgoogle-sheetssparklines

Google Sheets Sparklines - colour each bar in a barchart

I have a collection of sparklines that looks like this:

enter image description here

And I want it to look like this:

enter image description here

Now, everyday of the week is assigned a colour.

Does anyone know if this is possible? Feel free to edit my spreadsheet here:

https://docs.google.com/spreadsheets/d/1zzDY-K6yALg2iXleRPBJaVdlXucmH7ryOgAPqIvzff0/edit?usp=sharing

Thanks in advance

EDIT (2018/08/22 18:06 GMT):

Or is this perhaps possible to do in Microsoft Excel 2017?

Solution

  • According to the reference, the 2 color positions you may set is last and first columns:

    =SPARKLINE(data,{"charttype","column"; "firstcolor", "red"; "lastcolor", "green"; "ymin", 0})

    enter image description here

    Making more columns with different colors is impossible according to the current formula settings, you may refer to the Google team to make what you want:

    menu Help > Report a problem and write what you want to get.

    Workaround

    The way to make a similar-looking result is to create 7 separate charts:

    enter image description here

    The formula for H1 (copy down and right) is:

    =SPARKLINE(A2,{"charttype","column"; "firstcolor", H$1; "ymin", yMin; "ymax", yMax})

    where

    yMin is:

    =MIN(dataRange)

    yMax is:

    =MAX(dataRange)

    dataRange is range A2:G8 for my sample.

    Used Color Pallets for finding colors.