LibreOffice

Gentoo installation

• Emerge myspell-en, myspell-fr etc to get spellchecking support.

Writer

• To create a line break, use SHIFT + ENTER. This won't create a new paragraph.

Pages

• You can edit the page style at the bottom of the application screen (left click to change the page style).
• If the footer is different on left and right pages, edit the page style, Footer tab, and check the checkbox "Same Content Left / Right".
• To be able to correctly print (at the right size) double check that Page Format (in Format -> Page) is set to the correct size. Else the printer will ask for another paper size and refuse to print.

Paragraphs

• If there is a thin black line appearing somewhere, it is most likely a paragraph border. To remove it, edit the paragraph formatting.

Numbering and Bullets

• When creating a new paragraph (typing ENTER), a new number / bullet is automatically inserted. You can remove it by pressing BACKSPACE.
• I don't know how to have two or more levels of numbering / bullets on the same line.

Tables

• To change the properties of an individual cell (such as borders), select the cell then choose Table -> Table Properties. Even though it seems this dialog is for the whole table, it will apply only on the selected cell...

Calc / Excel

Settings

• Usually you should go to Tools -> Options -> Language Settings -> Languages to change the date acceptance pattern (to use the French locale). Format of cells that correspond to dates should also use the French locale.

Useful functions

• NOW() gets you the current date.
• AND(2 > 0; 4 > 0) can be used in tests.
• COUNTIF(range; condition) is useful to count only cells matching a given condition. For instance: COUNTIF(A1:A10,"=My Category").

Types

• It seems that there is no function to convert an internal date to a text (using some formatting options).
• You can use "'" (single quote) in front of a value to force Calc to recognize it as text.
• To remove the apostrophes (single quote) in a document where the cells should represent numbers, use the Data -> Text to columns function.
• There are test functions like ISTEXT, ISNONTEXT who can be useful.

Sheets

• You can delete the current sheet via Edit -> Sheet -> Delete.
• Window -> Split is used to have fixed views (I don't really like this feature).

Links

• You can link a whole sheet from an external document. For that, use Insert -> Sheet from file... and check the Link checkbox. This feature can be very useful.
• Sometimes in that case you must trigger "hard recalculation" if cells don't appear to update right. The key shortcut is Control + Shift + F9.
• You can view all links in a given file by going to Edit -> Links.
• Be careful: if, in a linked sheet, you reference another sheet in a cell via its name, the name won't be used. It is actually the index of the sheet in the document which will be used, thus make sure the indexes match between the original document and the linked document.

Rows and columns

• You may move a row/column/cells by using the following procedure:
  • Select the cells you want to move.
  • Then click on the black square, and start to drag and drop it.
  • While dragging hold the alt key to move it to the destination.

• To create a group of rows that can be collapsed / expanded, select the rows and apply:
  • Data -> Group and Outline -> Group / Ungroup.

• On Excel to move a column, select it and hold down the shift key. Note that you may have to "upgrade" the format to a newer version if you run into an error.

Cells

• To paste the contents of a cell, not its formula, copy as usual and then select "Paste special".
• To use absolute cell references, write $B$1 rather than B1. When copying, the copied formulas will still reference the absolute cell.
• You can reference a cell with a "named range", which can actually be very useful. To define a named range, use "Sheet -> Named Ranges and Expressions". You can then use this named range in place of any cell / range reference.

Referencing cells on the previous column or row

• You can use the INDIRECT method like this (it would target the cell with same row and previous column):

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))

Referencing external cells

• You can reference cells in another sheet or even in another document. You can even use some functions to build the path of the other document. Example:

=INDIRECT(CONCATENATE(Sheet2.B1,YEAR(H1),"-", MONTH(DATE(Sheet2.B10,Sheet2.B9,1)) - 1, "-vincent-bernardi-pay.ods'", "#$sheet1.i15"))

The CONCATENATE function is used to build the path. Note that you *must* use double quotes for concatenate to work when dealing with inline strings. The INDIRECT function is used to actually get the value of the referenced cell.

• Note that if you don't remember the syntax for referencing an external document or another stylesheet, you can just click on a cell, type "=" then click on the target cell on the external document. It will fill in the reference for you.

Merged Cells

• You can see if some cells are merged by clicking on a row / column head. Then all cells belonging to the row are highlighted in blue except those that are merged.
• To unmerge: Format -> Merge cells.

Text Cells

• You can create a line break with Control + Enter.

Copying the formatting of some cells

• Refer to this link.

Special cases

• Counting the number of rows where one column match a condition, while removing duplicate rows, is surprisingly hard. The UNIQUE() function can be helpful but it is only present in Excel's latest versions and absent in LibreOffice. Here is a working example:

{=SUM(IF($A3:$A30="My Category",1/COUNTIF($B3:$B30, $B3:$B30),0))}

This will count unique rows where the A column matches "My Category". The duplicates are removed based on column B (which is the name). The formula should be entered as an array formula (press control + shift + enter instead of just enter). Note that this will work only if the category is always the same for a given name. If the category can be different, it will fail. This probably would be OK:

{=SUM(IF($A3:$A30="My Category",1/COUNTIFS($B3:$B30, $B3:$B30,$A3:$A30,"=My Category"),0))}

• Array formulas are tricky. They are represented with braces but it's impossible to create an array formula by manually entering the braces. You need to use the control + shift + enter combination. However, in LibreOffice if you want to transform an existing formula into an array formula, you need to make at least one change in the cell, just clicking and entering control + shift + enter will not work. Typing a single character ("+"), deleting it, and then hitting control + shift + enter is enough and will work.

Exporting to PDF

• It is possible to define only certain sheets to be exported to PDF by defining a "Print Range" (Format -> Print Ranges -> Define). You can give a custom selection or just select "Entire sheet" to use the current sheet. In that case you don't need to enter anything in the field.

Printing

• To scale down a sheet to a page, go to View -> Page Break Preview and then drag the page breaks as needed.
• This also works for PDF export.

Impress

• Export to PowerPoint (.ppt) is not perfect in LibreOffice 3.3. Layout problems can appear, it may be worthwhile to check under vanilla PowerPoint.