OpenOffice Calc Tips

Version 2.0 of OOo Calc will feature significant improvements to the DataPlot functionality. For this reason, we will revisit this powerful feature over the next few days.

First, the basics…

(more…)

Here is another application of conditional formatting. In a prior tip, we reviewed the ISERR function and how it could be used to hide error conditions in any cell in the Calc document.

We can achieve the same effect with conditional formatting as follows.

Original spreadsheet with this pesky error messages.

As before, we need to create a custom cell format prior to opening the conditional formatting dialog. We set the font color to be the same as the backgorund color and we label this style as blank

Now select the cells whose errors we wish to mask and open the Conditional Formatting dialog (below) We still use the ISERR function - this time embedded in the condition applied to the selected cells.

The final result. Whichever method you apply to mask the error message is your decision. The advantage of the conditional formatting approach is that the original cell formulas are not complicated with the ISERR calls and it is easier to turn the conditional formatting on and off.

An introduction to the data consolidation functionality that is built in to OOo Calc. This is a useful feature when merging similar data from different souces.
(more…)

We continue our look at the application of conditional formatting in OOo Calc. Here, we wish to apply alternate coloring to even and odd rows. Prior to invoking the conditional formatting dialog, we select all cells in the sheet. This was covered in an earlier tip

The trick here is using the mod function to identify odd rows to which we apply the custom format.

The result after the conditional formatting is applied to the entire sheet.

Here is a Flash animation showing how to do this ….

See also:
Conditional Formatting II

Conditional Formatting I

Selecting an entire sheet

To illustrate the application of conditional formatting, we consider the need to highlight dates in a spreadsheet that are fast approaching or are already past.

The original spreadsheet without highlighting.

Before we apply conditional, we define any custom cell formats we may need. In this case, we have cells highlighted orange and cells highlighted yellow. We open the conditional formatting dialog and evaluate two conditions. The order is important here. In the conditional formatting dialog, once a cell evaluates TRUE, it will not be evaluated again. With that in mind, we first evaluate if a date is past due. We then evaluate if a date is fast approaching.

The modified spreadsheet with conditional formatting applied. Using this technique, the spreadsheet has become more readable and any key dates or deadlines can be more readily identified.

Conditional formatting is a mechanism that allows the user to apply formatting to
a cell or range of cells based on defined conditions. If the rules (conditions) are met, then the specified formatting is applied.
In OOo Calc, you can apply up to 3 rules in a cell or range of cells.

(more…)

A quick way to fill a range of cells with a series of values that increase/decrease in a geometric fashion.

(more…)

When you cut and paste formulas from a range of cells, the cell references within the formulas will be automatically adjusted. Here, we show how to work aroud this ‘feature’.

(more…)

The Data Validation feature of OOo Calc is similar to that in Excel. It helps the user control the data that is entered in the spreadsheet where it may be necessary to do so.

(more…)

How many of us have paid any attention to the row of small windows at the bottom of the OpenOffice Calc window? In this article, we will explore the Status Bar in more depth.

(more…)