OpenOffice Calc Tips

The Paste Special feature can be used to quickly link to range of cells from another sheet.

In the example below, we have a table on the sheet labelled First. We want to link to the bottom row from the sheet labelled Second

Select the desired range for linking b9:D9, then select Edit - Copy

Now go to the sheet from where we will be linking. Select the start cell of the new range (corresponding to B9 )

Now select Edit - Paste Special.

The Paste Special dialog is shown below. Select the Links option on bottom left.

The end result. The equations are for illustration. This may seem like overkill for a few cells - but can be very useful if yu have a very large range of cells that you need mapped to another spreadsheet.

The Autoformat feature in Calc is used to assign preset formats to a range of cells - creating attractive, professional-looking table designs at the press of a button.

In the ‘before’ picture below, we have a rather bland table.Time to spice it up. Obvious approach is to do it manually - selecting groups of cells in turn and applying different formats. The micromanagers among us probably favor that approach.

There is a ‘quick-and-dirty’ alternative - namely the Autoformat feature.

The Autoformat feature can be used to assign preset formats to a range of cells. Boder styles, number formats etc can all be configured and reused for different tables.

First, select the cells you want to format. From the menu, select Format - AutoFormat

In the Autoformat dialog box, select the desired Autoformat…

The end result. A prettier table with little or no effort.

We will use conditional formatting to identify unique entries between two lists.

In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can also be applied to two versions of the same list to identify additions/removals.

Step 1: Select B2:B19 and open the Conditional Formatting dialog - below. We have already defined a formatting style that has a darker background.

In the Conditional Formatting dialog, select the “Formula is”option from the drop down list on the left and enter the formula as displayed below. Choose the predefined style. Exit the dialog.

We apply similar conditonal formatting to the D2:D19 array. I will leave that as an exercise for the reader.

The result after the conditional formatting is applied. Those cells that are shaded dark do not have a matching entry in the other list.

By combining INDEX and MATCH functions as shown below, we can create a lookup table whose data can be accessed by specifying the desired row and column values.

This is probably best applied where the lookup table is of fixed size - for example - calculation of sales commisions.

For tables that change on a regular basis - thus requiring constant updates of the lookup formulae, the Base application might be a better solution. Now if only I had more time to play around with that…

Here is a similar

example

For each cell, you can define in advance what type of contents is valid for that cell. This allows you to guide users through data entry in OpenOffice.org Calc by restricting cells to receive specific values and ranges.

For selected cells, a drop-down list of possible values can be defined. In this tutorial, we show how this is done.

Step1: Select the required cell and open the data validation dialog as shown below.

Step2: In the Criteria tab of the validation dialog, the Allow option is itself a drop-down list. We select List.

Step3: With List slected, a text box appears which we populate with the list of allowed values for the selected cell(s). We are almost done.

After closing the validation dialog, we see that we now must select a value for our cell from the drop-down list we have just created - as shown below. It is also acceptable to leave it blank.

This example can be downloaded here.

There are a number of situations where it is desirable to take advantage of the sheet/cell protectection features of OOo Calc.

Perhaps you want to prevent the values for a range of cells from being overwritten. Perhaps, you want to protect sensitive financial formulae but share the results.

In order for protection settings to be enabled for a range of cells, the document or sheet must be protected. Protecting a sheet or document prevents the format of the cells from being changed - and because cell protection settings are part of the format, we apply this first before protecting the sheet.

By default, all cells are unprotected and all formulae are visible (below)

With the desired cells selected, open the Cells Format dialog - Cell Protection tab - shown below. Select Protected to prevent changes to the contents or the format of a cell. Select Hide Formula to hide formulas.

With the correct protection settings having been applied to the selected cells, we must now protect the sheet or document. This is done as shown below…

A password is required - don’t forget it!!

This is the message that you get when attempting to modify the contents of a cell.

This is a topic that you will best learn by trying out stuff for yourself - but here I summarize the basics…

There are a number of builtin functions that make use of regular expressions. Here, we use COUNTIF to illustrate some of the possibilities.

Regular expressions are a system for matching patterns in strings. They provide a very powerful set of tools for finding particular words or combinations of characters.

Based on my investigation, the following is a summary of the allowable constructs in OOo Calc.

• Most characters match themselves. There are exceptions - see below.
  
  COUNTIF(A1:A100; “foo”) will return all the instances of the string “foo” in the specified range.

• “.” matches any single character.
  
  COUNTIF(B2:B18; “..”) counts all cells with exactly two characters.

• “*” is a special character that matches zero or more occurences of the previous expression.
  
  COUNTIF(B2:B18; “.*e”) counts all cells that end in “e”.

• “+” is a special character that matches one or more occurences of the previous single character.
  
  COUNTIF(B2:B18; “.+m.+”) includes “Tmmy” and “name”, but not “my”.

• A string of characters enclosed in square brackets ([]) matches any one character in that string.

  COUNTIF(B2:B18; “[efg].*”) counts all cells starting with e, f, or g.

Consider the simple example below - a table with various columns of data. Any formulas we create would be much more readable if we assigned names to the columns of the table.

With the full table selected, choose Insert - Names - Create to open the Create Names dialog.

The Create Names dialog gives you four options for defining the table range names. In our example, we will name each column according to the top row of the table.

With our newly defined names associated with the table, we can rewrite our formulae into more meaningful expressions.

On the right side of the statistics, you will find a hidden gem of a feature - unless you are a former Excel user.

Select an array of numbers and the sum of the numbers appears in the bar - which you may or may not have noticed. However, if you right-click on the bar, you will see a choice of basic statistical operations to choose from - neat!

In the example below, we have chosen to display the average of the selected range.

Another quick tip illustrating the versatility of the Paste Special function.

To insert an empty row into an existing table…

Step 1: Select and Copy a single empty cell in your table.

Step 2: Select the row above which you wish to insert an empty row - then select Edit - Paste Special - below…

The key setting has been circled. Existing selection is shifted down while blank cell is copied across the table

The end result is shown below.