Is it possible to create a “background bar” for a cell in OO calc?

Question

I have a column in OpenOffice calc which contains number values (between 0 and 40). I'd like to add a bar in the background of the cell which shows the value (to give an obvious visual clue which value is larger).

Is that possible somehow?

Answer 1

I don't think that it is possible to have background bars in calc cells.

Another option could be to automatically set the background colour of the cells to different colours based on the value of the cell, e.g. different shades of red, the higher the cell's value, the darker the shade of the cell's background.

To do this:

1. Format the appearance of a cell to how you want it to look - e.g. pick a cell with a value of less than 10, and set its background colour to 'Salmon'. Ensure this cell is selected.
2. Go to the Format->Styles and Formatting menu (or just press F11).
3. Click the New Style From Selection button. Name the new style something like "Values 0-10".
4. Select the range of cells for which you automatically want to apply the background colours.
5. Go to the Format->Conditional Formatting... menu.
6. Set Condition 1 to have the values "Cell value is" "between" "0" and "10". Set Cell Style to be "Values 0-10". Click OK.

Repeat for the other ranges of values and background colours.

Answer 2

I've found a workaround: Just create a bunch of columns and set the background of them depending on the cell value. In the cells left to the value column, use a clever text color to make the text invisible (use White to turn it off and the desired bar color for "On").

1. Create three styles:
   • One derived from "Standard Test" with a text color "White". Call it "Off".
   • One derived from "On" with the desired background and a text color "Black".
   • Lastly, One derived from "On2" with the text color == the background color
2. Create a couple of columns before the value. Make them thin (.3-.5mm)
3. Assign the value of the value columns to the new columns (=F3 in E3 when your value is in F3)
4. Use conditional formatting in the value column (F in the example). For the value 0, use the style Off. For values > 0, use On.
5. Use conditional formatting in each new column to set the style to On2 if the value is large enough. Set the style to Off otherwise.