Ask Your Question

Chart conditional format : color scale

asked 2020-12-06 20:22:47 +0100

JG101 gravatar image

Trying to have a Bar chart to mimic a Format conditional color scale and / or apply a conditional format color scale to a chart?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-12-06 21:05:14 +0100

LeroyG gravatar image

updated 2020-12-07 02:01:05 +0100

Updated thanks to @ouleyang's hint (upvoted).

Still a little handwork is needed:

  1. Select the cells with the conditional format color scale applied (column C), and export it as PDF.
  2. Import the PDF in Draw.
  3. In Calc: Secondary click on the chart, choose Edit, click on the bars.
  4. Click on the n-th data point node, secondary click on the data point and choose Format Data Point….
  5. In Draw: Copy Area color Hex code from n-th "cell".
  6. In Calc: Paste it in Area color next the Fill Color data range of n-th bar (column A).
  7. Repeat for the next bar from step 5.
  8. Add the formula =HEX2DEC(A2) in column B.
  9. Edit the chart's Data Range to add Range for Fill Color.

image description

Tested with LibreOffice (x86); OS: Windows 6.1.

It would be great if someone finds a simpler way.

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark (Answer markCorrect answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more

answered 2020-12-07 00:00:04 +0100

ouleyang gravatar image

I am not sure I understand exactly what you mean, but the fill color can be taken from a cell, so you just need to configure a formula to get the corresponding color in the cell. You can have something like =IF(A2>2,HEX2DEC("ff0000"),HEX2DEC("00ff00")) in order to have a red bar for values above 2 and green bar otherwise, then apply this to the whole column of values.

It will not be dynamically linked to the conditional formatting though (you need to update your formula if you update your formatting).

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-12-06 20:22:47 +0100

Seen: 40 times

Last updated: Dec 07 '20