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 :)

On to the first tip . . . Exiting Linux Manuals

Okay, so with all that said, I'll finally get to the first tip :) Have you ever been typing some commands in the terminal and then got to reading Linux manuals (a.k.a. "man" or "man pages"), only to find yourself unable to get back to the previous screen? For instance, many times before I've typed in a few commands and then read a certain command's manual to find out more about it, but when I wanted to get back to the usable terminal, I couldn't! The best I could ever do was close the existing terminal window and open a new one. But I got so frustrated with having to do that, and I couldn't imagine that there wasn't an easier way to work in the terminal. So I finally broke down and . . . searched for an answer. It didn't help that the first few searches yielded no helpful results, but I persisted, and I'm glad I did; I finally found a webpage that referenced this very issue! It turns out that all you have to do is press "q"! That's right--you don't have to keep closing terminal windows each time you want to go back to the regular terminal window. Just press "q", and you just saved yourself a real hassle :) By the way, the winning combination of search words was: Ubuntu how to exit return terminal which led to <http://www.pendrivelinux.com/2007/11/01/how-to-exit-a-man-page/>. Keep in mind that this single, first tip won't necessarily give an indication of the variety of future tips and tricks, so if you're even just slightly interested, you might want to periodically check back so you can see for yourself if this blog is what you're looking for. For now, it looks like I'll be posting once a week, but that's only a general rule.

A Little History . . .

        Before I go off spewing the tips and tricks that will make this blog, you might want to know a little more about me and how I got into FOSS.         As I said before, I'm a college student—community college student, to be exact. Like many of my peers, the only OS I really knew was Windows, back when I was just an average computer user. I took a typing class and two programming classes in high school, but over the recent years I took a much larger (and perhaps broader) interest in computers and computer-related things. It wasn't until around the Fall of 2006 that I seemed to be really (really) serious about computers, however. At that point, I was mostly just looking forward getting Vista and Office 2007 the following January. I got a new graphics card since it was on sale. But I didn't actually ravenously rummage through the Internet to learn as much as I could about computers yet.         When I finally got Vista and Office 2007, I was certainly impressed by the eye-candy, but I certainly experienced major problems as well. Admittedly, I had followed the instructions of Microsoft tech support and upgraded Vista over my (crapware-laden) copy of XP, so that didn't help at all. On my own, I fresh installed Vista a little bit later, and that got rid of some problems. But then I got some extra RAM to boost the performance of my computer, and unbeknownst to me it seemed to be the cause of many a Blue Screen of Death! I didn't do anything about the RAM, though; it wasn't until later reformats, which I came to habitually perform every couple of months, that Vista didn't crash any more.         All this while I had been sitting on the conflict of whether I should install my graphics card (which I was worried I'd do wrong), or have a professional do it for me. It took me half a year to finally decide, but I'm a cheapskate, so I installed it myself after researching a little more into the process. I had been concerned that I'd render my computer unusable since it might not display anything on the monitor any more, but nothing went wrong. This, the RAM issue, and my experience reformatting my OS (which was a pretty advanced thing for me at the time :P) all boosted my confidence in regards to working with computers. Next, I wanted to try out lots of software to experiment with them and learn in the process.         I would download free (and trialware) utilities mostly, but I also went from Internet Explorer to Firefox too :) I liked to read more about the various workings of Windows, such as the registry, and I finally felt comfortable modifying it (but only because nothing bad happened when I did :P). I also learned about Ubuntu and other free and open-source software. However, it wasn't until I just got fed up with software activations (those frequent Windows reformats were a problem) that I seriously considered using Linux as my main platform. I'd off-and-on been using Ubuntu LiveCDs to test it out, but at this time I used it like crazy and wanted to learn more and more about it. I started downloading tons of FOSS for it, and if there were Windows versions available, I would use them in Windows. For documents and other office-related files, I started using open formats (i.e., .ods, .odt) for new files, and I converted old files to open formats. Microsoft will not own my stuff!         That pretty much leads to now. I am certain I will move over to Ubuntu (or another Linux derivative) sometime this year—I'm just not sure when yet. My wireless adapter currently doesn't work in Gutsy, so I'll need to either work with "ndiswrapper", wait for Hardy to come out (assuming the wireless adapter will work with it right away), or find another Linux distro that supports it. Other than that, though, I think I'm pretty much all set with the transition: I'm familar with OpenOffice in both Windows and Linux (though in some ways, Gnumeric beats Calc); I'm familiar with using the terminal to install software (although I prefer to use Synaptic in Ubuntu when I can), copy and move files, and access files and folders with root; and once I can get my wireless adapter to work, I'll be able to access the Internet (which, honestly, at least 90% of my work depends on). I wish more hardware vendors would provide official Linux support, but I'm happy to say that even without their support, I'll be moving to Linux soon :)

Welcome!

        Hello, anyone :) I'm a young college student who as of late has become very interested in Linux and other Free and Open-Source software. It's a completely new, refreshing, and indeed interesting world that I'm glad I happened upon. My experience hasn't always been very easy, but luckily I'm so fascinated by it that I have plenty of motivation to spare. I recognize that others may not be the same way, however, so they might need a helping hand to guide them. Even experienced users can benefit from a few tips or tricks here and there as well. So I've decided to make my very own blog for these kind of people, as well as myself.         This blog will be used to provide any tips or tricks that I may come across on a forum, another blog, website, etc., or that I've discovered firsthand. You can expect that these tips and tricks will be random since I only document those things that I don't already know or that I feel I may have difficulty remembering. For instance, the type of material you can expect to find here will range from Linux OS's to other free and open-source software (mostly for Linux OS's) to anything else I deem relevant. Therefore, I don't really have a set schedule in terms of when I post. That may make it sound like this blog is for me more than you . . . perhaps it is. But along the way, I'm more than happy to share the information with you. I understand how frustrating it can be working with unfamiliar software.         Feel free to comment on my posts, as well. I'd wager most new and important tips and tricks are provided by posters than the actual blogger, and blogs can be a very good source for this kind of information. I hope you enjoy mine :)

Just a test . . .

Fedora Test

This post is merely a test of whether Fedora 8's "Blog Entry Poster" works properly, and if so, in what way.

EDIT: Well, it looks like it worked!