How to automatically assign different colours to categories in a bar chart

I would like to represent a sequence of categories in bar chart form. I would like the different categories to each have its own colour bar.

The x axis would consist of the sequence, for example:

ACTGAGCTCCAGA

A=Red
C=Green
T=Blue
G=Yellow

How can all of the As be made to appear Red, without having to individually set ever instance of A?

The Y axis is a percentage value, 0-100%.

y values: 385976294480983
x values: ACTGAGCTCCAGA

A C T G A G C T C C A G A
3 8 5 9 7 6 2 9 4 4 8 0 9 8 3

Note:

In the “Data Range” section of the chart wizard, selecting “Data series in columns” results in different coloured columns for every column. In the required instance, 4 colours, no more nore less, are needed, one colour for each of the four values, ACTG.

Here is another way, using the Property Mapping feature introduced in v4.3. It basically uses this type of formula in a third column:

=IF(A2="A",COLOR(255,0,0),IF(A2="C",COLOR(0,255,0),IF(A2="T",COLOR(0,0,255),COLOR(255,255,0))))

Which means:

  • If the X value is “A”, create a colour value based on RGB 255,0,0 (i.e., red) ELSE
  • If the X value is “C”, create a colour value based on RGB 0,255,0 (i.e., green) ELSE
  • If the X value is “T”, create a colour value based on RGB 0,0,255 (i.e., blue) ELSE
  • If the X value is “G”, create a colour value based on RGB 255,255,0 (i.e., yellow).

This column is then selected for the Fill Color data range. Sample ODS here.

That’s really cool. I’ll pay more attention to the release notes in future.

Here’s one way, by rearranging your data. The rearranging could be done with formulas. example_barchart.ods Adjust colours for each category accordingly. Note that I could not be sure of how the string of digits was divided. Set bar overlap to 100% for best visual effect.