OpenOffice Calc: Cell Background/Border Color

  • fgysin

    I am trying to create a spreadsheet with colored cell backgrounds. But I encountered this strange bug, where the background of a cell is bigger than the shown border, so coloring a cell will actually color a very slim margin in the cell next to it. :/

    Here is an example of what I mean (zoomed in):

    Screenshot of the PDF

    Note that what you see here is the created PDF, so this is not just an issue with Calcs representation!

    • As you can see the blue cell stretches into the cells above and to the left, but strangely not to the bottom.
    • Furthermore there is a very faint grey line as border between some black cells. This is strange as I certainly never used any grey colors for cell borders. Also the line is only visible in the PDF (or in the OOCalc preview) on certain zoom levels.

    Any idea on how to fix this? Am I missing something?

    (Only way around it up to now has been to add quite thick borders to cover up the error, but that is an ugly hack...)

  • Answers
    Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

    Related Question - Is it possible to create a "background bar" for a cell in OO calc?
  • Aaron Digulla

    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?

  • Related Answers
  • jwaddell

    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.

  • Aaron Digulla

    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.