LibreOffice calc / oocalc notes

Notes about oocalc.

Regex find

You can use regular expressions only if you tick ‘Regular expressions’ in the ‘Other options’ section of the Find & Replace dialog, accessed via Edit -> Find & Replace…

Reference: List of Regular Expressions

Some keyboard shortcuts

Key combo Action
ctrl-home Go to first cell (top left)
ctrl-end Go to last cell (bottom right)
ctrl-left Go to first value in row
ctrl-right Go to last value in row
ctrl-up Go to first value in column
ctrl-down Go to last value in column
F2 Edit a cell without having to click on it

Reference: Calc/Shortcut Keys for Spreadsheets - LibreOffice Help

Turn off auto-replace

You’ll find a load of options at Tools -> ‘AutoCorrect Options…’. If you want to turn off all auto-replacement then browse around the tabs and disable/enable most things.

Here are the ones that bother me the most…

Hyphens

Do the following prevent hyphens being auto-replaced with a dash character:

  1. Tools -> AutoCorrect Options…
  2. Untick ‘Replace dashes’.

Date contractions

Do the following to stop date contractions 1st, 2nd, 3rd, 4th etc being auto-replaced with superscript st, nd, rd or th:

  1. Tools -> AutoCorrect Options…
  2. Localized Options tab.
  3. Untick ‘Format ordinal numbers suffixes (1st -> 1^st)’.

Filter column to find unique cells

If the sheet has a lot of data e.g. 30000 rows then select a column or row otherwise the whole sheet will be auto selected and the app will slow to the point of despair.

Standard filter

Select column. Data -> Filter -> Standard Filter…

Condition <> empty Options -> No duplications

Advanced filter

I did this first before I found that a standard filter could be easier / faster. I’ve left this here for future reference, at which point I’ll review.

Data -> Filter -> Advanced Filter..

Set a range e.g. $N1:$N32324 Options -> No duplications.

Performance is shit with large sheets. Also I would get ‘This range does not contain a valid query’ errors randomly, even if I used the mouse to select a range!

Advanced filter

Quotes

If you have a condition like SUMIF, you must use double quotes. Single quotes won’t work.

=SUMIF(A1:A100,"foo",B1:B100)

If you use single quotes you’ll get a sum of 0.

Source cell range from different sheet

  1. Copy the range in sheet 1.
  2. Paste Special in sheet 2, selecting the Link option.

Show rows hidden by a filter

If you apply a filter and it hides a load of cells, select all cells (i.e. selection includes cells before and after), then do Format -> Row -> Show.

Export a graph/chart

One way of exporting a spreadsheet graph or chart is to copy it into a LibreOffice drawing and export from there.

  1. Copy the chart to the clipboard.
  2. Create a new drawing. File -> New -> Drawing.
  3. Paste into the drawing.
  4. Export the drawing (as whatever format you want).

Reference: How do I export the charts in a spreadsheet as PNGs?

Flip a bar chart

If you have a bar chart that shows largest to smallest and you want it to show smallest to largest…

  1. Right-click the chart and select Edit.
  2. Right-click on the labels of the axis you’d like to flip and select Format Axis.
  3. On the Scale tab select ‘Reverse direction’.
  4. Click OK.

Reference: [Solved] Is there a way to flip the data?

Weird characters that look like Chinese

If you open a document and it contains a single cell with odd characters (e.g. 慲獮捡楴湯ⱳⰬⰬਬ堬塘⁘塘) then you probably need to open the file again and select the correct encoding.

On linux you can determine the encoding of a file as follows:

test@pc ~ $ file -bi example.csv
text/plain; charset=utf-8

Reference: [Solved] Problem with Calc and Weird Symbols in the Output

Last modified: 24/07/2015 Tags: , ,

This website is a personal resource. Nothing here is guaranteed correct or complete, so use at your own risk and try not to delete the Internet. -Stephan

Site Info

Privacy policy

Go to top