Ask Your Question

Chart: match data color to cell

asked 2019-02-01 23:10:21 +0200

vedder gravatar image

I recently abandoned Excel in favour of LibreOffice Calc, because I was running into some hardcoded limitations of Excel (namely only being able to plot 255 data series in a single chart) that don't seem to exist in LibreOffice.

As you can imagine having a chart with over 255 data series it's important to have full control over the colour for each series. In Excel I managed this with a macro that picked the colour for each series based on the cell colour of its header.

Is there a way to do this in LibreOffice Calc?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-02-02 12:36:20 +0200

Lupp gravatar image

Quoting @vedder: "...having a chart with over 255 data series..."
How should anybody "read" such a visualization?

Quoting @vedder: "I managed this with a macro that picked the colour for each series based on the cell colour of its header."
There are lots of chart types. There will be significant differences concerning the issue under discussion! I very rarely use charts of any type but "X-Y (Scatter)". This type for visualizing ordinary functions (or empiric data under the assumption they might be generated by a function) does not allow to assign colors to the series except via the chart formatting dialogs - as far as I know.
Column diagrams wich I use sometimes allow to take the fill-colors and the border-colors per column from a dedicated range containing the RGB values. To get the RGB values based on a sample cell, there is no standard function. However, a user function for the purpose is easily written. The one problem insofar is that Calc (as opposed to Excel with VBA) doesn't pass an ordinary reference as a CellRange to the user function. Thus you need to pass the position of the sample cell either by address (string!) or by coordinates (3 numbers for Row, Column, and Sheet). See the attached demo containing such a user function going the coordinate way.
For your intended use case the most serious problem might be that there not seems to ba a way to cause the legend to accept coloring information from cells. I don't need enough about the Chart model of LibreOffice to suggest a solution for this second problem by user code, and also not if there is one at all.

edit flag offensive delete link more


To answer your question about readability and chart type. This is a visual of the graph, depicting games released on various hardware/software platforms by year. Readability isn't so much of an issue as it's more to do about the totals and the big platforms which clearly stand out due to their area size.

I will have to look at the rest of your answer in more detail later when I have the time. Not allowing to colour the legend would indeed be a setback, although I guess it would be possible to create my own legend next to the graph.

vedder gravatar imagevedder ( 2019-02-02 13:32:12 +0200 )edit

Having seen your chart I don't any longer worry about getting information out of it. It's just beautiful and a temptation to the alpinist whether mainly ineterested in alpine geology or in motorbiking.
Quoting @vedder: "I guess it would be possible to create my own legend next to the graph." This is exactly what I considered to suggest. I'm sure you will find your way.

Lupp gravatar imageLupp ( 2019-02-02 14:14:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-01 23:10:21 +0200

Seen: 435 times

Last updated: Feb 02 '19