Introduction to the Status Bar

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.

The Status Bar displays information about the current sheet. It is shown below in the default configuration with the different fields tagged.

The fields in the default configuration are:

  • 1 : Position The number of the current sheet and the total number of sheets in the document.
  • 2 : Page Style The Page Style of the currently open sheet. You can double-click in this field to call up the Page Style Editor.
  • 3 : Zoom The current page display zoom factor.
  • 4 : Insert Mode The current insert mode. This field is active when the cursor is in the input line of a formula or cell. By clicking in this field, you can toggle between INSRT = insert and OVER = overwrite.
  • 5 : Selection Mode The current selection mode. By clicking in this field, you can toggle between STD = Standard (default), EXT = Extend and ADD = Add.
  • 6 : Modified Flag If modifications to the current document have not been saved, a * is displayed here.
  • 7 : Formula A basic evaluation of a simple formula for the currently selected range is displayed here. Default is SUM

The Status Bar can be customized by clicking Tools – Configure and selecting the Status Bar tab. The Status Bar configurtion dialog window is shown below.

Read More

Basic Functions: FREQUENCY

The following tips will show how the FREQUENCY function can be used in conjunction with the charting capabilities of OpenOffice Calc to create frequency plots of data.

In the example below, our data is in B4:D20. We wish to plot the frequency of occurence of the numbers listed in B26:B31.

The corresponding frequency tabe C26:C31 is created with the array function


For some reason (OOo Calc bug?), you need to specify B26:B30 rather than B26:B31 – but it seems to work as entered here.

Read More

Checkerboard shading

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…

Read More

Naming Multiple Cell Ranges

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.

Read More

Who needs DATEDIF() ??

I recently came across a blog in Indonesian – which had useful pointers related to OpenOffice alternatives to the Excel DATEDIF() function. Here is the link:

The DATEDIF() function is a strange beast in Excel – so much so that it isn’t even implemented in OpenOffice Calc.

The syntax for DATEDIF is as follows:



Date1 is the first date, in standard Excel serial-date format.
Date2 is the second date, in standard Excel serial-date format.
Interval indicates the unit of time that =DATEDIF is to return is result.

There are sixdifferent interval codes in Excel DATEDIF() and we will cover them all with alternatives in OpenOffice Calc.

For example, to calculate someones age (in years) in Excel, you would use =DATEDIF(A1,NOW(),”Y”)

In OpenOffice CALC, we can use =YEARS(A1;NOW();0)

For calculating number of months in Excel, we use =DATEDIF( A1,NOW(),”M”)

In OpenOffice CALC, we use = MONTHS(A1;NOW();0)

For calculating number of days using DATEDIF, we use  =DATEDIF( A1,NOW(),”Y”)

In Openoffice CALC, we can use.. =INT(DAYS(now();A1))

Read More

Create Links Between Cell Ranges (Quickly)

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.

Read More

Easter Egg: starcalcteam()

There is a new Spanish language OO related blog from Rafael Sanchez

He points out an interesting Easter Egg in the OpenOffice Calc program.

In any cell, type =starcalcteam()

The image below is displayed…

Read More

Auto Filling Cells

Use this procedure to automatically increment data as you fill down a column or across a row.

1.Select the cell range whose existing contents you want to extend to more cells. Then release the mouse button.

2.Move the mouse pointer on top of the automatic fill handle in the lower right of the selected cell. The mouse cursor turns into a target cross.
3.Click and hold down the left mouse button on the automatic fill handle, and drag in the desired direction of the fill, selecting all the cells you want to contain data.

Here are a few examples of how the automatic fill sequence works:
The sequence 1, 2 becomes 1, 2, 3, 4, and so on
The sequence 1, 3 becomes 1, 3, 5, 7, and so on
The cell content “Quarter 1” becomes Quarter 1, Quarter 2, Quarter 3, and so on
Automatic fill only increments data that Calc recognizes, such as numbers, dates and cell references. If Calc does not recognize data, automatic fill duplicates the data exactly. However, you can create sort lists that contain lists that Calc will recognize and automatically increment. To create a sort list, choose Tools – Options – Spreadsheet – Sort Lists

If you just want to copy the range without changing the values, hold down the Ctrl key while dragging.

Read More

Character Codes

OOc calc uses the ANSI character set. By using the CHAR function, any member of the ANSI set can be generated. Conversely, the ANSI character code can be determined with the CODE function. The sheet below displays all the ANSI characters.

Read More

Controls : PushButton

The Form icon opens a floating toolbar with the tools and functions needed to create an interactive form.

Long-click the Form icon in order to open the floating toolbar. A short mouse click always activates the control field inserted last, which is shown as an icon.

To place a PushButton on the sheet – click the icon shown here. iconbutton.jpg
This control can be used to execute a command for a defined event, such as a mouse click.

To modify/view the properties of a selected form control select
Control The dialog shown below pops up. It has two tabs.

The General tab controls the appearance of the control.

The Events tab controls the actions associated with the control.

Read More