OpenOffice Calc Tips

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.

In this tutorial, we will see how to restrict the value of a cell or range of cells to a specified list.

In the example below, we would like to restrict the values of two fields - State and Sex.

First select the column with the State values. Invoke the Validity dialog with Data - Validity as shown below.

There are three tabs in the Validity dialog. First, we define the criteria to be applied to the selected cells.

By default, all values are allowed. We will define a list which will be displayed when the user selects the cell for input.

Because the list of valid states will be displayed from which the user can choose, we can dispense with an input help message. The Error Alert tab allows us to define an error message/action to be generated if erroneous value is inputted.

We click OK to exit the Validity dialog. We are now ready to see how effective this feature is in validating the data being input in the table.

As you can see, selecting a cell in the state column results now in a drop-down list from which the user selects the desired value.

We can also define other lists where desired/required - for example the Sex column - as shown below.

It has been decided to merge the Mungits and Wingdings product lines. We will use Paste Special to merge the sales totals in the spreadsheet below.

Step 1: Select C6:F6 and Edit - Copy

Step 2: Select C5:F5 and Edit - Paste Special

The Paste Special dialog opens. In this situation, we apply the ADD operator to the paste operation which has the effect of adding the pasted data to the existing contents of the selected cells.

Having merged the data, we can safely delete the Wingdings row in the table.

Unlike Excel, OOo Calc does not have random sort feature. It is straightforward to work around this.

Consider the table below - we wish to randomly sort the rows.

Step 1: Add a random number for each row in a seperate column as shown below.

Step 2: Select all columns and invoke the sort dialog as shown below…

We will be sorting our original two columns based on the column of random numbers…

The final result. You can delete the column of random numbers - they have served their purpose.

The paste special feature is useful for preserving the values in a spreadsheet - but removing the formulae that generated those values. This may be required to preserve proprietary equations.

To remove the formulae for a range of cells, select and copy the desired range. Do not unselect the range. Now click on Edit - Paste Special - see below.

Make sure the Formulas box is unchecked. After you click OK, the formula is removed but the value is preserved.

In a series of short tips, we introduce the Paste Special feature.

A quick and easy way to multiply an array of numbers by a constant without using a formula…

In the example below, we wish to multiply B5:B20 by 10. Temporarily enter 10 into an empty cell. Select it and then Cut or Copy

Select the target range, then click on Edit - Paste Special . In the Paste Special dialog, unselect Paste All and Formats. Also select Multiply. Now click OK …

We are done! The modified cell range.

Open Office Calc has an interesting Easter egg - a hidden feature that might not be obvious or documented. Programmers sometimes bury Easter Eggs in their programs or web sites to add extra depth and challenge users to find them.

In an empty cell, type the following formula..=GAME(”StarWars”)
This is what you will see…

After you have selected your hero, the game begins. Another screen shot…

You can only play the game once. To play again - exit OOo Calc and the quickstarter before restarting.

There are many mathematical and statistical constants in use that are needed in spreadsheets. For OpenOffice Calc, only PI has been hardwired as a function call - PI()

Using defined names is a quick and easy way to define constants in your spreadsheet that will also make your formulae more legible and maintainable. There will also be no need to have hidden sheets where the constants are defined. In this example, we will define Euler’s constant - e

Open the Define Names dialog as shown below.

Instead of assigning/associating the name with a particular cell, assign a fixed value as shown below. Exit the dialog

The named constant can now be used in formulae as shown below. Note that Names defined in this way do not appear in the name box - which is just above the cells area on the left.

A checkerboard shading effect is easily achieved with conditional formatting as shown below. The conditional formatting dialog is invoked with Format - Conditional Formatting

Please review earlier articles on conditional formatting here , here and here.

When the condition (formula) evaluates to true, then the specified style is applied to the cell. We have seen this before. ISODD is a boolean function so is ideal in this situation.

The resulting pattern…

You can annotate your spreadsheet with pop-up notes attached to any cell you wish. The data validation function is invoked as shown below..

Choose the Input Help tab and enter the desired annotation for the selected cell.

This is what will be displayed each time you select the cell.

In this tip, we build on our previous lesson by looking at one of the key new OOo 2.0 features related to the DataPilot.

The example table below has four fields, Sex (M/F), pay grade, state of residence , and salary.

We open the DataPilot dialog as before. For OOo 2.0, a new Page Field is available. We drag the headers into the various DataPilot fields as shown below.

The new Page field gives us a filter to control the data that is displayed/processed in the DataPilot table.

Below, we have returned to the DataPilot dialog and swapped the STATE and GRADE fields. We see the different options provided by the filter. A good illustration of the power and flexibility of the updated DataPilot feature in OOo Calc.