OpenOffice.org — Colorful Conditional Formatting in Calc

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:

1. Click the down-arrow at the far-right of the “Standard” toolbar (the one with the options “New”, “Open”, “Save”, and others).
2. Click “Customize Toolbar”.
3. Ensure “Standard” is selected in the drop-down box to the right of the words “Toolbar”.
4. Select “Add” on the right.
5. 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”.
6. 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)

OpenOffice.org — Full-Page Backgrounds in Writer

I personally have found OpenOffice to be a very competent free and open-source alternative to Microsoft Office. I know that it doesn't have the latest features that MS Office does, necessarily, but it certainly has everything that I'd imagine most people need for basic (and not-so-basic) word processing. Of course, it may have the features you want, but it doesn't always have an intuitive way to use them. For instance, in MS Word 2007, you can easily change the color of a document's background by selecting (or mouse-scrolling to) the “Page Layout” tab and then the “Page Color” button in the “Page Background” group (that may all sound complicated, but it's actually not very). In OpenOffice Writer, however, it's not nearly as simple (or obvious). After much searching, I found a work-around that can achieve the same effect:

1. Go to Format > Page > the Page tab and change all of the margins to 0”.
2. Go to the “Background” tab and choose the color you want as the page's background.
3. Go to the “Borders” tab, turn the borders on for all sides by clicking the second-leftmost button under “Line Arrangement” and “Default” on the left.
4. Select the border color.
5. Finally, change the “Spacing to contents” values to the appropriate amount of spacing you want. They take the place of the margins, so if you want the text to be 1 inch from the border, type 1” in each of the textboxes. If “Synchronize” is checked, the value for all four textboxes will change at once, so you only need to enter the value 1” in one of the textboxes.
6. Click “OK”.

Voilà! Your page is beautiful!

If you use full-page backgrounds in Writer fairly often, you can add “Page Settings” (or pretty much any other feature) to the “Standard” toolbar by adhering to the following instructions:

1. Click the down-arrow at the far-right of the “Standard” toolbar (the one with the options “New”, “Open”, “Save”, and others).
2. Click “Customize Toolbar”.
3. Ensure “Standard” is selected in the drop-down box to the right of the words “Toolbar”.
4. Select “Add” on the right.
5. Find the command you wish to place on the Standard toolbar. In this case, you'd go to “Format” under “Category” and “Page Settings” under “Commands”. Press “Close”.
6. 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”.

source: <http://homepage.ntlworld.com/pesala/Home/html/watermarks.html#Fills>

OpenOffice.org — Adding Colors to OOo's Initial Selection

When I first started using OpenOffice, I had wanted to change the background of some cells in Calc, but I didn't know how to add my own custom colors to use instead of the standard set of colors that came preinstalled with OpenOffice. Well, if you're in a similar situation, I have good news for you! Yes, you can in fact add your own custom colors to use in OpenOffice :) No longer will you be limited to the existing palette!—all you have to do is:

1. In any OpenOffice application, go to “Tools” > “Options” > “OpenOffice.org” > “Colors”.
2. Click “Add” and choose a name for the new color. Note: if a color is selected before you press “Add”, you'll get a message stating that the name already exists. Press “OK” and at the prompt, type in the name of the new color you're going to create. Now you're back on track.
3. Create the color by changing the Red, Green, and Blue (RGB) or Cyan, Magenta, Yellow, and Key (CMYK) values (you can switch between RGB and CMYK in the drop-down box to the left of “Delete”). Alternatively, for better fine-tuning (including Hue, Saturation, and Brightness settings), you can click “Edit”. Note: if you change the color values in the Edit window, you'll have to apply the changes by pressing “Modify”. Unfortunately, OpenOffice doesn't accept hexadecimal values.
4. When you're done perfecting your color, simply press “OK”. Caution: if you change an existing color's values and then click “Modify”, the new values will overwrite the old color! Only click “Modify” if you truly want to commit the changes.

Of course, it would be really nice (hint, hint) if OpenOffice allowed you to add custom colors at the same time that you choose the font or background colors, as Microsoft's Office does. Maybe in a future update . . . .

OpenOffice.org — Creating and Setting a Default Template

If you find yourself frequently using the same or similar layout for new files in OpenOffice Writer, you may find it easier to create a new default template that incorporates the changes you usually do by hand, automatically. This is a simple way to save you time, especially for the more sophisticated layouts you may utilize. To change the default template for text documents in Writer, first create a blank document with all of the formatting that you'd like the template to have. Next, go to File > Templates > Save, make sure "My Templates" is selected under Templates > Categories, and then give the template a name under "New template" and press OK. This just saved your template in the default template folder, which you can specify in Tools > Options > OpenOffice.org > Paths. To make this template the default template, go to File > Templates > Organize, double-click "My Templates", right-click the template you just saved, and finally click "Set As Default Template". Click close, and you're done! Every time you open Writer or choose "New" from the toolbar, the new document will automatically use your template. To reset the default template, go to File > Templates > Organize, double-click "My Templates", right-click the template that's currently the default, hover over "Reset Default Template", and choose "Text Document". If you'd like instead to choose which template to use when first creating a new document, simply go to File > New > Templates and Documents, or you can add a button to the Standard toolbar that does the same thing. To do so, click the down arrow on the far right of the Standard toolbar, click “Customize Toolbar”, and under Toolbar Content > Commands, check “New Document from Template”. When you click on this button, you will get to choose which template to use for the new document you create. Keep in mind that all template files (*.ott, etc.) should be closed while you make these changes. Also take note that with the default “New” button, if you make a selection from its down arrow, whichever selection you make will become the new default selection whenever you click the actual “New” button. For example, if I click the down arrow to the right of “New” and select “Spreadsheet”, the “New” button will now show the spreadsheet icon to demonstrate that if I press “New”, a new spreadsheet will be created (rather than a text document, for instance) since I last selected “Spreadsheet” from the down arrow. This information is current, as of OpenOffice.org version 2.3.1 on Windows Vista. To find out which version you're using, go to Help > About OpenOffice.org. Some steps may be different in OpenOffice.org for other operating systems, such as Linux.

Wine

Expanding on the Wine talk, why don't we discuss why Wine is useful. Well, I personally know that it's making my transition much easier. There are tons of free and open-source programs available for Linux, and in this case, that may be working both for and against me. Given the vast amount of Linux software, I'm bound to find a program that's suitably equivalent to the programs I formerly used on Windows; but at the same time, it may take me a while to finally find that program. Until I do find these alternatives, Wine may allow you to use your Windows programs either indefinitely, or until you come across a Linux-based one. Currently, that's what I'm doing with a few programs. As I mentioned before I use both HashX and (to a lesser extent) Easy Duplicate Finder. In addition, I use Bulk Rename Utility, which as the name suggests, allows me to easily rename several files at once. I understand that there's Métamorphose, but I haven't tried that yet. Now I don't necessarily use these just because I'm used to them or haven't found a Linux-equivalent; in some cases, the Linux equivalents are inferior to their Window counterparts. For example, I feel that the free Paint.NET is superior to GIMP, even if GIMP has many more features—mostly it's because Paint.NET has an easier-to-use interface. It has one window, with four others (toolbox, color editor, history, and layer manager) inside it, vs. GIMP's multi-window configuration. But also, tools in Paint.NET are more intuitive, and thus easier, to use. You select a tool from the toolbox—say the selection tool—and the more refined settings for that tool appear on the top toolbar. There, I can easily decide whether to replace an existing selection area with a new one, add a selection to an existing one, subtract a selection area from an existing one, etc. Unfortunately, as the name implies, Paint.NET is reliant upon Microsoft's .NET architecture, which makes it more difficult than usual to port the program to Linux. As of now, all I now is that some have worked on a version of Paint.NET for Linux that uses Mono in place of .NET, with decent, but not preferable, results. As such, I'm stuck with GIMP (in Linux, anyway), and will be for the foreseeable future :( I really hope that GIMP's development team can fix GIMP's interface enough that it's less reliant on menus and generally more like Paint.NET's—for the end-user's sake, you know? So Wine doesn't work with every Windows program out there, but with time and/or luck, your favorite Windows program may work well with it. Wine's website, , maintains a database of programs and their status (i.e., whether they work or not), so you can use that resource to find out whether others have been successful in using a certain program with Wine. Of course, the database may contain outdated information, and also it wouldn't be uncommon for your specific program to not be listed, so you can always try the program yourself. Good luck :)

Windows Programs, Their Extensions, and Wine

Some people feel that Windows should just die. And its little programs too. Others, however, rely on certain “exclusive” Windows programs, whether it's because of their employer or their own personal taste. While I personally look forward to going completely Linux, there are still a few programs in Windows that I feel are superior to current Linux alternatives. For those, I must run them with the help of Wine (or a virtual machine, but that's beyond this article), a program (that's not an emulator) that allows you to run many Windows programs within Linux. Now, given the fact that there are so many programs exclusively on Windows, your obscure tax form application most likely won't be supported in Wine. Many of those programs are so tied to the Windows way of doing things that the more complex ones most likely won't be compatible. I personally have had some success with some of the smaller programs I used pretty extensively in Windows. HashX, a program I use to compare files I download to the originals, ensuring that I received the file intact and as the author intended; and Easy Duplicate Finder, a program that helps identify any duplicate files I may have so that I can delete them (although FSLint, for Linux, seems to have a decent duplicate finder component as well), are two examples.

One thing I noticed, though, was that when I double-clicked on one of the programs running with Wine, I would get a pop-up message stating that the content of the file didn't match its extension (.exe). As a result, I had to manually right-click the file and choose to run it with Wine in order to use it. This was pretty annoying. Thankfully, the fix was easy: all I had to do was rename the files by deleting the “.exe” part, and then I was successfully able to run them by double-clicking.

One of the many things I like about Ubuntu is that it doesn't necessarily need a file to have an extension to function—rather, it simply looks at the file content to determine which application should open it. For instance, a simple text file named “README” would open up just fine in gedit (or your preferred text editor) under Ubuntu, but it would choke up Windows, and you'd be required to add a file extension in order to use it (unless you right-clicked the file and manually opened it with a specific program). So it looks like the concept works similarly for Wine-driven programs, with the difference that you pretty much must remove the file extension if you want to be able to run a program by simply double-clicking it.

My Blog's Automatically Resizing!

In case you're wondering why my blog template may seem to have different dimensions than the original version of the template (called “No. 897”), it's true. I have a rather large widescreen computer monitor (1680x1050 resolution), and as I was first previewing my blog, I was shocked to find that in this day and age, the template didn't proportionally resize to fit my monitor by default. What this meant was that my posts were tall and skinny, and there was plenty of unused space on the left and right. This issue doesn't affect 1024x768 monitors—the most common—since the template seemed specifically suited for them; but as a widescreen monitor owner, I personally understand how others with widescreen monitors feel when websites don't scale to fit different-dimensioned monitors, and so I've done what I can to make any blog or website I put on the Web dynamically suit each monitor dimension (to an extent). In other words, my blogs and websites aren't suited for just standard or widescreen monitors—it should work well with all monitors (except extremely wide, tall, small, or large ones). So I hope that this doesn't cause you any discomfort; just understand that I'm trying to appeal to many people at once :)