Calc FAQ – Miscellaneous questions

  • What’s the maximum number of rows and cells for a spreadsheet file?
  • How do I protect cells in my spreadsheet?
  • How can I delete cell contents immediately with the backspace key like in Excel?
  • How to make a checkbox change according to data in another cell?
  • How can I rename a sheet?
  • How I change the order of sheets in my spreadsheet?
  • How can I see the row and column captions as I scroll through a sheet that is larger than my display?
  • How can I create a spreadsheet that is right-to-left oriented?
  • How is a variable date inserted into a spreadsheet cell?
  • I want to select two cells that are not adjacent, but holding down the CTRL key does not seem to work. How can I perform this action?
  • What is the fastest way to copy a calculation to all rows?
  • How can I start OpenOffice.org Calc instead of Writer?
  • How can I use cells from different Calc files?

What’s the maximum number of rows and cells for a spreadsheet file?

 

1.1.x

The OpenOffice.org 1.1.x versions were able to handle a maximum of 32,000 rows. Update to the current version to solve this issue.

2.x

The limitations of the OpenOffice.org 2.x Calc versions are

  • maximum number of rows: 65,536
  • maximum number of columns: 256
  • maximum number of cells per sheet: 16,777,216
  • maximum number of sheets: 256
  • maximum number of cells per file: 4,294,967,296

3.x

The limitations of the OpenOffice.org 3.x Calc versions are

  • maximum number of rows: 65,536
  • maximum number of columns: 1024
  • maximum number of cells per sheet: 67,108,864

Notes:

  • When referencing to external documents in formulas or when creating scenarios, hidden helper sheets are created that reduce the available number of sheets and consequently cells.

How do I protect cells in my spreadsheet?

 

Cell protection is active for all cells by default. If only certain cells are to be protected, this setting must be turned off.

To exclude cells from the protection:

  1. Select the cells to be excluded from protection
    Hold down the Ctrl key while clicking on non-adjacent cells to highlight the ones that are to be protected.
    If you only want to protect a small number of cells, it may be easier to clear the protection for all cells and the re-activate protection for the appropriate cells. For this, select the entire spreadsheet: press CTRL+Aor click on the little gray box above row 1 and to the left of column A.
  2. Select Format – Cells from the main menu
  3. Click on the Cell Protection tab
  4. Clear the check mark for the Protected option
  5. Click OK

Initially however, the protection is not activated. To activate the protection:

  • Select Tools – Protect Document – Sheet to protect the current sheet only
  • Select Tools – Protect Document – Document to protect all sheets in the current document

How can I delete cell contents immediately with the Delete key like in Excel?

 

If you press Delete in Calc, you get by default a Delete window that lets you delete formats, text, formulas and/or other elements. And to delete cell contents immediately you have to press Backspace.

If you want to use the Delete key for immediate deletion you have to do the following:

  1. Choose Tools > CustomizeKeyboard tab. Be sure that the Calc radio button is selected.
  2. In the Shortcut Keys area of the window at the top, select “Delete”. In the Functions area of the window at the bottom: under Category select “Edit”, and under Function select the FIRST of the two “Delete Contents” items. This is the one that just deletes, with no window.
  3. Click the Modify button. In the Functions area you will see “Delete” now listed as a key.
  4. If you don’t want Backspace to delete contents, then select “Backspace” in the Keys list in the lower right corner and click the Delete button.
  5. Now scroll through the Shortcut Keys list at the top and select “Backspace” as the function you want to use to bring up the Delete window.
  6. Then in the lower part of the window, as before, under Category select “Edit” and under Function select the SECOND “Delete Contents” item.
  7. Click the Modify button to bring up the interactive Delete window by pressing the Backspace key.

How to make a checkbox change according to data in another cell?

 

In Calc enter this function
'=IF(A1=1;"True";"False")'

Now, you need to add this macro. If you don’t know how, please see the user guide.

 Sub SetMyCheckBox
 Dim oForm
 Dim oControl
 Dim oSheet
 Dim oCell

	oForm = ThisComponent.Sheets(0).DrawPage.Forms.getByIndex(0)
	oControl = oForm.getByName("MyCheckbox")
	oSheet = ThisComponent.Sheets(0)
	oCell = oSheet.getCellRangeByName("A1")

	If oCell.Value = "1" then
		oControl.Value = "True" (?)
	Else
		oControl.Value = "False"
	End If
 End Sub

How can I rename a sheet?

 

  • Right-click the sheet you wish to rename and select Rename Sheet from the popop menu, or
  • Select Format – Sheet – Rename from the main menu, or
  • Double-click the sheet to rename it (ooo 3.1)

How can I change the order of sheets in my spreadsheet?

 

You can move a sheet to a different position, click and hold the sheet tab at the bottom of the screen with the mouse and drag it to its new position.

You can also move sheets (even across different documents) using the menu:

  1. Right-click the sheet you want to move and select Move/Copy Sheet… from the pop-up menu, or
    select Edit – Sheet – Move/Copy from the main menu.
  2. Specify the new position of the sheet in the dialog.
    You can even move the sheet to a different document that is opened in Calc.
  3. Click OK.

How can I see the row and column captions as I scroll through a sheet that is larger than my display?

 

There are two ways to obtain this result:

Mode 1 (with a single row or column caption): Freeze the caption.

  1. Click on the cell just below and to the right of the row and column that will contain your caption.
  2. Select Window – Freeze from the main menu.

If you have either row captions or column labels, you can create a cross freezing by clicking on the uppermost left side cell that does not contain a caption. For example, in a sheet with single row and column captions, choose the B2 cell. The position of all cells above and to the left of the cell where the Freeze was activated will be frozen.

Mode 2 (with multiple row or column captions): Split the sheet.

  1. Click on the cell just below and to the right of the row and column that you want to contain your caption.
  2. Select Window – Split from the main menu.

The sheet window will be split. The result is different from Mode 1 because you can scroll all sections of the split window, showing the caption according to your needs.

There is another (almost hidden) way to split a sheet window:

At the top of the right scroll bar and at the right of the bottom one, you can see little black lines. When the mouse is over one of these zones, the mouse pointer changes into a dragging icon.

  1. When this icon shows, click and hold down the left mouse button to display a border line in the grid of cells.
  2. With the mouse, drag the line on the grid to the row or column that will contain your caption.
  3. Release the mouse button. The sheet will be split at this border.

How can I create a spreadsheet that is right-to-left oriented?

 

If you like your spreadsheet weighted on the right

  • Choose Tools > Options > Language Settings > Languages and check “Enabled for complex text layout (CTL)”.
  • Right-click on a sheet tab and choose Sheet Right-to-Left.

The sheet will flop and the numbers of the rows, etc. will be on the right.

How is a variable date inserted into a spreadsheet cell?

 

  1. Select the cell that will hold the function.
  2. Enter =TODAY() for the current date or =NOW() for the current date and time.

The values will be dynamically updated when the file is reloaded.

I want to select two cells that are not adjacent, but holding down the CTRL key does not seem to work. How can I perform this action?

 

1.1.x

In OpenOffice.org 1.1.x, if you click in a cell, it is not selected, but focused. To select a cell, you need to

  1. Click on a cell to focus it
  2. Hold down the SHIFT key and then click on the cell again to select it

After having done so, you’ll be able to select multiple non-adjacent cells, by holding down the CTRL key as you select new cells.

 

2.x

In all 2.x versions, you can use the CTRL key to mark cells that are not adjacent.

What is the fastest way to copy a calculation to all rows?

 

To fill a selected cell range with the formula that you entered on the Input line, press Alt+Enter. Hold downAlt+Enter+Shift to apply the cell format of the input cell to the entire cell range.

How can I start OpenOffice.org Calc instead of Writer?

 

Pass -calc as an argument on the command line when executing openoffice.org to start OpenOffice.org Calc:

ooffice -calc

On a Mac OS X computer:

Choose File > New to open the other modules of OOo.

How can I use cells from different Calc files?

 

  1. Open the source file
  2. Select the cells and press CTRL+C to copy them to the clipboard
  3. Open the target file
  4. Select a target cell and select Edit – Paste Special from the main menu
  5. Check the Link box in the Options section

The inserted data are now linked to the original document. Whenever the data changes in the source document this will be reflected in the target document.