22 votes

How to set decimal separator in Libre Office Calc?

From https://help.libreoffice.org/Common/Languages#Locale_setting: Specifies the locale setting of the country setting. This influences settings for numbering, currency and units of measure. Go to ...
Jim K's user avatar
  • 4,019
19 votes

Copy/Paste a formula in Libreoffice without adjusting the cells

Trick: Cut (ctrl + X) Undo (ctrl + Z) Paste (ctrl + V) Since "cut-paste" works, but not "copy-paste", you can cut-paste and use the undo not to erase the old values.
Daniel Möller's user avatar
16 votes
Accepted

How to find and remove external links in (Libre|Open)Office Calc spreadsheet

I also got that popup on startup: I had the same problem and found out today that there is an option in the Edit menu called Links.... Select that option and you get a dialog with the list of ...
Alexis Wilke's user avatar
  • 1,580
15 votes

how to get Libre Office to adjust row height to accommodate text entry?

Using LibreOffice Calc 5.4.4.2 on OS X, I found I had to do four things to set every row to its optimum height: Select all cells In the "Format" menu, turn on "Format/Text/Wrap Text" In the "Format" ...
Daniel Griscom's user avatar
15 votes
Accepted

How do I join two tables with LibreOffice Calc?

Here's a straightforward way to do it. Sheet 1: Sheet 2: Sheet 3: On Sheet 3, Name and Age are just cell references to Table 1. So A2 contains: =Sheet1!A2 And B2 contains: =Sheet1!B2 Gender ...
fixer1234's user avatar
  • 27.4k
13 votes
Accepted

How to get number value with leading zeros in LibreOffic Calc?

The TEXT function can add leading zeroes without formatting the cell. It returns a string. Even though it is a string, it can still be treated as a number in other formulas. For example =D2+1 gives ...
Jim K's user avatar
  • 4,019
11 votes
Accepted

Extracting named groups of regular expression in LibreOffice Calc

In both Excel and Calc, the cleanest solution is to create a general-purpose regular expression macro. To do this in Calc, go to Tools -> Macros -> Organize Macros -> LibreOffice Basic and ...
Jim K's user avatar
  • 4,019
11 votes
Accepted

Convert the multi-step sum of booleans into a single formula

The function you are after is COUNTIF(): Enter the following formula in G3 and ctrl-enter/copy-paste/fill-right into G3:I3: =COUNTIF(A3:A8,">"&D3) COUNTIF() checks each value in the first ...
robinCTS's user avatar
  • 4,367
11 votes
Accepted

How to find cells that reference the current cell in LibreOffice Calc spreadsheet?

LO Calc has built-in functions for that equivalent to Excel's audit functions. In Calc, it's called Detective, and is available from the Tools tab of the menu. Tracing precedents and dependents also ...
fixer1234's user avatar
  • 27.4k
10 votes
Accepted

Open a CSV in LibreOffice Calc without converting values to scientific notation

Try these options in the Text Import dialog: Deselect the 'Detect Special Numbers' checkbox In the preview area, click the column header where it is labeled 'Standard' and change it to 'Text'. ...
Liam's user avatar
  • 3,060
10 votes
Accepted

Default cell background in Calc

Another way would be to modify the overall color settings: Menu "Tools" -> "Options" in options: "LibreOffice" -> "Application Colors" modify the very first color property "General" -> "Document ...
tohuwawohu's user avatar
  • 10.7k
10 votes

The maximum number of columns per sheet was exceeded

Options... ▸ LibreOffice ▸ Advanced ▸ Optional Features ▸ Enable experimental features (may be unstable) Restart LibreOffice Options... ▸ LibreOffice Calc ▸ Defaults ▸ New Spreadsheet ▸ Enable very ...
Sarke's user avatar
  • 362
8 votes

Currency conversion in LibreOffice Calc?

Without using a plugin, try using api.fixer.io: =LEFT(RIGHT(WEBSERVICE("http://api.fixer.io/latest?symbols=EUR,GBP"),9),7) This will retrieve something like this: {"base":"...
doc's user avatar
  • 181
8 votes
Accepted

Identify cell containing given text in LibreOffice Calc

Just use the MATCH() function. Since it supports regular expressions, you can use MATCH() to search for partial strings. Here's an example: The lookup array is A1:A4, the search criterion is .*a.* (...
tohuwawohu's user avatar
  • 10.7k
8 votes
Accepted

Insert the current date and time (DATETIME-function) with a single shortcut into a cell in LibreOffice Calc

Change the format of cells before shortcut is simplest solution? Format cell after shortcut does not work. Macro way ... Sub Main Dim Doc As Object Dim Sheet As Object Dim Cell As ...
MShaffer's user avatar
  • 146
7 votes

How to find and remove external links in (Libre|Open)Office Calc spreadsheet

Every time I opened up a Calc Sheet I had copied, I received the same pop up "This file contains links to other files. Should they be updated?" The spreadsheet I was opening was copied from another ...
Al Walding's user avatar
7 votes

How to find if 2 columns (or rows) in LibreOffice Calc are identical?

Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be: =EXACT(A1,B1) Apply the same formula for every cell in column C. In Calc you can grab the ...
Mirzhan Irkegulov's user avatar
7 votes

Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

From menu: Format → AutoFormat Styles.. . You could add your own. Another option is to use macros (as in ngulam's answer). Color2Rows is an extension that adds a button to the tool bar, for ...
user.dz's user avatar
  • 668
7 votes
Accepted

Sum values with same name

That's exactly what a pivot table does. Add labels in the first row, then go to Data -> Pivot Table -> Create. Drag "Name" to Row Fields, and "Amount" to Data Fields. For complete instructions, see ...
Jim K's user avatar
  • 4,019
7 votes

Default cell background in Calc

One way is to change the default template: Create a new blank document Press F11 to open the Styles editor Find the Default style and right-click then choose Modify... Set Background Color as desired ...
James P's user avatar
  • 11.3k
7 votes
Accepted

How to highlight the current row and column like a crosshair in Libreoffice?

Lupp has recently created an example spreadsheet that uses macros for this behavior. It's posted at https://forum.openoffice.org/en/forum/viewtopic.php?t=43531#p431848. However, he remarks at AskLO ...
Jim K's user avatar
  • 4,019
7 votes

Use a Python function as formula in LibreOffice Calc cells

Some things you asked about are easily solved, others not so much. First of all, the IDE. APSO is an extension that is now ready to use and provides a convenient menu for editing Python macros. ...
Jim K's user avatar
  • 4,019
7 votes
Accepted

Copy/Paste a formula in Libreoffice without adjusting the cells

when I have done a CUT & paste in Excel in the past, it transfers as is. The copy/paste will change cell references. I have not used LibreOffice.
ruggb's user avatar
  • 358
7 votes
Accepted

LibreOffice Calc: conditional formatting based of column / cell value

I am using LibreOffice Calc 5.1.6.2. Just like my highly respected preceding poster described: Select the range of cells to be conditionally formatted (in my case A1:F44); Select Menu Format -> ...
Pawel Debski's user avatar
6 votes

Is it possible to copy & paste a pivot table in a spreadsheet?

Select the Pivot Table On the Analyze Ribbon - Click on Select-> Entire Pivot Table Right Click Copy in the pivot table area Go to the location where you want to place the copy of the pivot table and ...
user822380's user avatar
6 votes
Accepted

Is there any way to extend the column limit in Libreoffice Calc?

Yes, there is. How to enable support for very large spreadsheets in Libreoffice 7 Calc? Via the following steps in Tools -> Options...: First under LibreOffice -> Advanced, check Enable ...
Fernando Fraga e Silva's user avatar
6 votes
Accepted

LibreOffice equivalent to SQL "count(*) group by some_column"

Create a pivot table with column B in the "Row Fields" and in the "Data Fields" list; double-click on the "Data Fields" entry "B" to change the default SUM ...
tohuwawohu's user avatar
  • 10.7k
5 votes

Can I retrieve and access a simple JSON object via HTTP in LibreOffice Calc?

After 4 years, they still haven't implemented the suggested FILTERJSON(). This is obviously not a real solution, but one that works for now, for very simple cases: Send a request to your API using: =...
Juha Untinen's user avatar
5 votes

LibreOffice Calc: how to pad number to fixed decimals when used within formula?

Solved! I couldn't find this it at first, but I've got it: FIXED(C1,2)
RocketNuts's user avatar
  • 1,082
5 votes
Accepted

How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?

I answer myself as I do this at least once a year and I feel the need of having a place to look for quickly when I forget something, plus I think this could be useful for someone else too. So, ok, I ...
danicotra's user avatar
  • 1,984

Only top scored, non community-wiki answers of a minimum length are eligible