OpenOffice Calc FAQ – Formatting Spreadsheets

  • How can I create a drop-down list that references a list of values to select from?
  • How do I insert superscript or subscript text in my spreadsheet?
  • How do I wrap text within a cell in my spreadsheet?
  • How can I use a dot (.) as decimal sign instead of a comma?
  • How can I use conditional formatting?
  • How can links to other workbooks, including vlookups, update dynamically?
  • How do I add additional strings or characters to cell contents?
  • How do I insert a page number in the form of ‘Page 1 of N’ on each page of a spreadsheet?
  • How are thick lines or borders created around my cells?
  • How do I format cells in Calc so that the rows number automatically?
  • Is there a way to add times that total greater than 24 hrs?
  • How can I create my own sort lists?
  • How do I make a wide title cell extend across several columns in my spreadsheet?
  • I have a custom number format that I use, but Calc forgets it.
  • Some of the rows or columns in my spreadsheet are hidden. How do I see all rows or columns?
  • How do I disable the capitalized letter at the beginning of cells in Calc?
  • How do I rotate a column title so that it fits above my very narrow column?
  • How are notes within cells displayed?
  • Why are my notes not showing for cells?

How can I create a drop-down list that references a list of values to select from?

 

Under Data > Validity, you can control what people can enter in spreadsheets, and offer them help in the form of lists, help tips, etc.

  1. Click in the cell where you want the list to appear. If you want the list in multiple cells, select multiple cells.
  2. Choose Data > Validity.
  3. Select Cell Range in the Allow list.
  4. Type the range.
    • To type a range in the same sheet, type something like this: $F$1:$F$20. You need the $ to make the reference absolute.
    • If the range is in a different sheet, add the absolute sheet reference in front, like this: $Projects.$F$1:$F$20.
  5. Click OK.

Then if you need to, just change the contents of the cell range and the list updates. Previous entries in those spreadsheets containing entries no longer in the list do not change.

How do I insert superscript or subscript text in my spreadsheet?

 

Using the Menu

  1. Select the individual character(s) to be made superscript/subscript:
    • Click on the cell with the text
    • At the input line above the spreadsheet, select the characters to be altered
  2. Select Format – Character from the menu
  3. Click on Font Position
  4. Click Superscript or Subscript. You can optionally change the character reduction ratio but usually the default value will work fine.

Using the Keyboard

  1. Select the individual character(s) to be made superscript/subscript:
    • Click on the cell with the text
    • At the input line above the spreadsheet, select the characters to be altered
  2. For Superscript, press CTRL+SHIFT+P
    For Subscript, press CTRL+SHIFT+B

Notes:

  • Cell height may require an adjustment to accommodate the new character.
  • These commands also work for the word processor.

How do I wrap text within a cell in my spreadsheet?

 
To wrap text within a cell, or merged set of cells:

  1. Select a cell or group of cells.
  2. Right-click the selected area and go to Format cells…, or select Format – Cells from the main menu
  3. Click on the Alignment tab.
  4. Check the Wrap text automatically (in OpenOffice.org 1.1.x: Automatic line break)
  5. Click OK.
  6. Select ‘Optimal Row Height…’ from the context menu. Now the contents of the cell will be wrapped to fit the cell.

How can I use a dot (.) as decimal sign instead of a comma?

 

Some languages, like French, Italian, German, or Portuguese, use a comma as decimal separator. The appearance of numbers in Calc depends on the language settings. If you want to use the dot or decimal point as separator you need to switch the language for the corresponding cells to English:

  1. Select the corresponding cells (press CTRL+A to select all cells of a sheet)
  2. Select Format – Cells from the menu
  3. Select the Numbers tab and select one of the English variants from the Language list.

You can also (and probably better) achieve this by changing the properties of cell style default. That will automatically change that property for all other styles (unless changed explicitly in a another style).

Notes:

  • If you use a dot in a language that uses a comma as decimal separator, Calc will not recognize the input as number.
  • The decimal sign used will change with the language. If you switch back to a language that uses the comma as separator, the displayed decimal separator will change accordingly.

How can I use conditional formatting?

 

Formats in each cell can be based upon defined conditions. This function can be accessed through the drop-down menus:

  • Select Format – Conditional Formatting…

In the dialog box that appears, enter the conditions that determine the formats and the desired formats for each condition. If more help is needed to understand the settings, click the Help button in the dialog box, and a new help window opens with a description of all the fields shown in the dialog box. The help file also shows the path to a sample file that uses conditional formatting for reference.

How can links to other workbooks, including vlookups, update dynamically?

 

Dynamic links across spreadsheets: Imagine you want to use a vlookup from a spreadsheet database (that is, a range of cells you are treating as database), and the database is in another workbook. In Excel, changing data in the database will cause the vlookup to update without user action (dynamic updating).

If you open such a formula into Calc, the formula looks like this:

=VLOOKUP($B$16;’file:///Users/tris/Documents/Cust_database.ods’#$sheet1.$A$1:$IV$65536;4;0)

but this does not update dynamically.

You need to save the database document with any changes made to it, and then manually refresh links perhaps by closing and reopening the spreadsheet using the vlookup.
Solution

Copy the range of cells in your database, and then do a Paste Special -> Link The formula will look like this

={DDE(“soffice”;”/Users/tris/Documents/Cust_database.ods”;”sheet1.A3:I281″)}
This is a dynamic link. References to the other workbook can be changed to use this “DDE” reference, rather than the “file:///” reference.

So, you can rewrite the vlookup as this

=VLOOKUP($B$16;DDE(“soffice”;”/Users/tris/Documents/Cust_database.ods”;”sheet1.A3:I281″);4;0)

and now you have a dynamic vlookup

Works in version 2.2

How do I add additional strings or characters to cell contents?

 

Use the CONCATENATE function to add strings or characters to cell contents:

  1. Go to an empty cell
  2. Enter =CONCATENATE(“prefix”;A1;”suffix”) as the cell contents
    “prefix” being any text you would like to add in front of the existing cell contents
    A1 being the original cell
    “suffix” being any text you would like to add after the existing cell contents

Example:

  • Original Cell “C5” with content “filename”
  • In a new cell, enter =CONCATENATE(“my_”;A1;”.ods”)
  • The new cell value will be “my_filename.ods”

How do I insert a page number in the form of ‘Page 1 of N’ on each page of a spreadsheet?

 

Page numbers can be inserted into the header or footer sections of a spreadsheet. These will be visible only in the Page Preview mode and on the print. The page numbers will not be visible on, nor are they related to, the calc sheets themselves. The page numbers reflect the defined print areas of the workbook.

To insert page numbers:

  1. Select Edit – Headers & Footers… from the main menu
  2. Depending on where you want the page number to appear, select either the Header or the Footer tab
  3. Click in the area where you want the page number to be displayed (Left, Center, or Right) to place the cursor inside the box.
  4. Type “Page “, then add one space character.
  5. The available data fields are represented by a row of icons below the input areas,
    Click on the document icon with ONE number sign [ # ] to insert the page number placeholder.
  6. Type ” of “
  7. Click on the document icon with TWO number signs [ ## ] to insert the page total placeholder
  8. Click OK

Note:

  • The data fields available in the header and footer settings are the only data fields and formats available in spreadsheets.

How are thick lines or borders created around my cells?

 

  1. Select the cells where you wish to apply a border.
  2. Select Format – Cells… from the main menu
  3. Click on the Borders tab
  4. In the Line Arrangement section, under Default, click on the icon that best shows the style of border you wish to use, or
    Under User Defined, select custom border styles by clicking on the individual lines shown bordering four ‘dummy’ cells.

To change the width and color of a border:

  • In the Line section, under Style, click on the desired line for your border, and
  • Under Color, choose the desired color for your border.

How do I format cells in Calc so that the rows number automatically?

 

  1. Enter the formula =row() into cell where the row numbering will start
  2. Extend the cell by dragging the bottom right corner all the way down to the cell you want the numbering to end

Note:

  • If you insert new rows, you will have to copy the formula to the new cells.

Is there a way to add times that total greater than 24 hrs?

 

Yes. Select a format code with the hour symbols in square brackets, like [HH]:MM.

  1. Select the cell you want to apply the format to
  2. Select Format – Cells from the main menu
  3. Select the Numbers tab
  4. Set the Category to Time
  5. Select one of the formats with hours > 24, for example [HH]:MM:SS
    you can also select the Format Code line and enter the format yourself
  6. Click OK.

Now when you add the times from cells with this format together, you will get the true sum of hours and minutes. Otherwise, the sum will reset to zero each time 24 hours is reached.

How can I create my own sort lists?

 

Sometimes it is useful to use sort lists to control the order of your data, especially if the data is better sorted in an order that is not alphabetical or numerical.

Sort lists can be used to fill data into cells by “guessing” following values based on the value of a first cell. The most widespread example is the days of the week or names of the months:

Example:

  1. Insert “Jan” into a cell.
  2. Select this cell, then drag the black square at the lower right corner across other cells in the same row or column.
  3. The other names of the months will automatically fill into the selected cells.

To create your own sort list:

  1. Select Tools – Options from the menu.
  2. Click OpenOffice.org Calc and Sort Lists in the left column
    In OpenOffice.org 1.1.x, the option is called Spreadsheet instead of OpenOffice.org Calc.
  3. Click New on the right side of the window.
  4. Type your list in Entries box separating each word by a comma or a line break. Do not use spaces.
  5. Click Add after your list is complete to save your new sort list.

How do I make a wide title cell extend across several columns in my spreadsheet?

 

  1. Select the cells in which the title is to appear.
    Do this by clicking in the first cell, then hold down the mouse button and drag the selection box across the last cell of your chosen area.
  2. Select Format – Merge Cells – Merge Cells (OpenOffice.org 1.1.x: Format – Merge Cells – Define) orFormat – Merge Cells – Merge and Center Cells

I have a custom number format that I use, but Calc forgets it.

 

2.x

For Calc to remember the preferred number formats, add them to a template:

  1. Create a new empty spreadsheet document or open an existing template by selecting File – Templates – Edit from the main menu
  2. Add the number formats as required
  3. Select File – Templates – Save from the main menu
  4. Insert a name for the template and select a template category (e.g., My Templates)
  5. Click OK to save the template

To have them available with every newly created spreadsheet you need to make this template your default template:

  1. Select File – Templates – Organize from the main menu
  2. Select the template from the list of templates (double click the template categories to show all containing template files)
  3. Right-click the template file name and select Set as default template
  4. Click Close

 

Some of the rows or columns in my spreadsheet are hidden. How do I see all rows or columns?

 

  1. Select the area of the spreadsheet where rows or columns are hidden. To select the entire spreadsheet, select Edit – Select All from the main menu or press CTRL+A
  2. To show all rows, select Format – Row – Show from the main menu
    To show all columns, select Format – Column – Show from the main menu.

How do I disable the capitalized letter at the beginning of cells in Calc?

 

  1. Select Tools – AutoCorrect from the main menu
  2. Select the Options tab
  3. Clear any automatic option you want to disable (in this case Capitalize the first letter of every sentence)

How do I rotate a column title so that it fits above my very narrow column?

 

There are a few options. Follow these instructions:

  1. Select the cells where you wish to rotate the text.
  2. Select Format – Cells… from the main menu
  3. Click on the Alignment tab
  4. In the Text Orientation section (OpenOffice.org 1.1.x: Text Direction) drag the dot on the circle to the bottom of the circle (or enter 270 in the Degrees box). This will rotate the text in the selected cell by 270 degrees.
  5. Click OK to see the effect. Experiment with placement of the dot to learn more.

How are notes within cells displayed?

 

To create a Note for a cell

  • Select Insert – Note from the menu

To show a note permanently

  1. Right-click in the cell with the note
  2. Select Show Note

Why are my notes not showing for cells?

 

1.1.x

  • Make sure that Help – Tips from the main menu is marked

 

2.x

  1. Select Tools – Options from the main menu
  2. Select OpenOffice.org – General
  3. Make sure that Help – Tips is marked