Sometimes it seems like the best way to highlight data in a spreadsheet is to change its font and/or background color. For instance, the constantly-evolving grade sheet I use to track my school grades has specific cells that tell me whether I'm ahead of, on time, or behind the recommended schedule for a particular class (and by how many weeks I'm ahead or behind). Being ahead is good, so I've chosen green to represent the cell in that instance, being on time is okay, so I've chosen yellow for that, and being behind is horribly bad, so I use red for that. Technically, this color concept came from Excel 2007, and I merely continued using it as I switched to Calc. It's a good thing that the feature was able to translate smoothly from Excel to Calc!
Still, as is common with OpenOffice, this feature is much easier to use in Excel, while still possible in Calc. For the most part, you can rely on Calc to do everything in the translation for you. However, if you don't have an Excel file that you can open in Calc, I'll go ahead and share the code with you.
First, though, let me explain the conditional formatting as it applies to my grade sheet. Three fields are involved: “Week Submitted”, “Week Due”, and “Progress”. “Week Submitted” is simply the week number that I submitted a given assignment—pretty self-explanatory. “Week Due” is the week a particular assignment is due, as predefined by the class's teacher (this is usually a recommendation rather than a requirement, though). Finally, “Progress” is the field with both the formula used to determine whether I'm ahead, on time, or behind, as well as the conditional formatting applied to it.
Creating Styles
Before we get to the actual conditional formatting, let's create three styles—one for each condition. If you need to add colors to OpenOffice's palette, refer to my previous post entitled OpenOffice.org — Adding Colors to OOo's Initial Selection.* Go to “Format” > “Styles and Formatting”. A new window opens up with a few buttons, a drop-down box at the bottom, and a list in the middle. Make sure that “Cell Styles”—the upper-leftmost button in the window—is checked. Right-click the word “Default”, which is located right below the “Cell Styles” button, and select “New”. In the “Font Effects” tab, choose the color you'd like the font to have, and on the “Background” tab, choose the color you'd like the cell to have. If I'm ahead of my class's recommended schedule, both the font and background of the cell will be green; specifically, my font is RGB=0,97,0, and my background is RGB=198,239,206. You may have added those colors previously.
On the “Organizer” tab, give this style a descriptive name; I named it “MS Office Good Green” since the colors can be used to visually indicate anything positive, whether it's when you're ahead of schedule or earning profit. You can repeat this two more times (starting with right-clicking “Default”), pairing the font and background colors I've provided at the bottom of this post, or you can use your own color combinations. Just be sure that one of your two new combinations indicates neutrality and the other negativity.
Applying Conditional Formatting
Now, all you have to do is select the cells that you'd like to apply conditional formatting to, click “Formatting”, and then click “Conditional Formatting”. Here, you can specify up to three separate conditions that each can have their own unique formatting. Thankfully, I only have three separate conditions—“ahead by x weeks”, “on time”, and “behind by x weeks”. I can see this limitation posing a problem if you need more than three, obviously.
You'll notice that there are drop-down boxes that either display the text “Formula is” or “Cell value is”. Unfortunately, Calc doesn't have a straightforward “Cell value contains” or equivalent, as Excel does, which would allow us to simply put in “ahead”, “on time” and “behind” as the cell values. You could technically do “Cell value is equal to ahead”, but then it wouldn't apply the proper formatting because that condition would never be met; I'm never just “ahead”, I'm “ahead by x weeks”, where x can have a value between 1 to infinity, and the s may or may not be there, depending on the typical use in the English language (i.e., 1 week, 2 weeks, etc.). In translating the original Excel file, Calc interpreted “Cell value contains” as “Formula is”, so that's what we'll have to use. In the textbox to the right of “Formula is”, you need to put in the following code:
NOT(ISERROR(SEARCH("on time";I3)))This code searches for the text “on time” in cell I3, and it will apply the formatting that we'll specify next if it finds “on time”. Remember that I use yellow to visual indicate that I'm on schedule, so we'll define the formatting so that the background of the cell is yellow and the text itself is brownish. Again, these colors are actually taken from Excel, and I've decided to continue with them even as the spreadsheet is now in ODS format.
The drop-down box to the right of “Cell Style” determines the formatting. When Calc interpreted the original file, it added the correct styles simultaneously. You may need to define the styles you want to apply, however. Here is where you use those styles that you previously created. Click the down-arrow and select the style that you'd like to apply for this first condition. Then, repeat these steps for the other two conditions. If all goes well, you should be finished!
To test, type “on time”, “behind”, and “ahead”, one by one, into the conditionally formatted cells. If the cell background and font colors change to the correct ones, you did it! If not, please reread my instructions to see if you missed anything.
As usual, if you use the “Conditional Formatting” feature of OpenOffice fairly often, you can add it to the “Standard” toolbar if you:
- Click the down-arrow at the far-right of the “Standard” toolbar (the one with the options “New”, “Open”, “Save”, and others).
- Click “Customize Toolbar”.
- Ensure “Standard” is selected in the drop-down box to the right of the words “Toolbar”.
- Select “Add” on the right.
- Find the command you wish to place on the Standard toolbar. In this case, you'd go to “Format” under “Category” and “Conditional Formatting” under “Commands”—the same way you got to Conditional Formatting in the first place. Press “Close”.
- At this point, you can drag the newly-added command to where you'd like it, or you can use the up and down arrows to the right of the scrollbox. When you are finished, press “OK”.
Well, I hope you found this post helpful. Until next time :)
*Add the following colors to OOo's palette if you want to use the same ones I do. The format is RGB=Red value, Green value, Blue value (color description). You can enter these values as instructed in my post OpenOffice.org — Adding Colors to OOo's Initial Selection.
“ahead”
RGB=198,239,206 (light green background)
RGB=0,97,0 (dark green text)
“on time”
RGB=255,235,156 (light yellow background)
RGB=156,101,0 (brown text)
“behind”
RGB=255,199,206 (light red background)
RGB=156,0,6 (dark red text)
