Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

12
votes
4answers
16k views

How to set the default font in LibreOffice Calc?

How do you set the default font used in new documents of LibreOffice Calc? I am using LibreOffice 3.3.2. Some answers below have been updated for LibreOffice 4 as well.
22
votes
5answers
16k views

How to remove a plain text protecting single quote from all the selected cells in LibreOffice Calc?

I've imported a CSV file having the first column to be date-time values in ISO 8601 format like 2012-01-01T00:00:00.000Z for the first moment of the year 2012. Then, willing to make ...
29
votes
2answers
46k views

How to color rows based on cell value in Open/Libre Office

How do you automatically set the background color of an entire row based on the value of a specific column in that row in Open/Libre Office? There are similar questions for Excel, but this doesn't ...
6
votes
5answers
11k views

How to get VLOOKUP to return the *last* match?

I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be ...
3
votes
1answer
9k views

How do I create a line graph which ignores zero values?

I want to create a graph showing trends over time, calculated from a formula of various data. However, some data is missing, resulting in zeroes in the calculated data which the chart uses. When ...
7
votes
2answers
3k views

Libreoffice: sum of column except one cell

I want to calculate the sum of the A column excluding A1, because I want the result there. Illustration: A B C 1 =SUM(A2:Ainfinite) 2 1234 3 5678 ... I ...
2
votes
1answer
4k views

How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells. i.e. the conditional format changes the background colour of some cells if they are negative. I ...
1
vote
3answers
2k views

Sum up to N highest-value out of a series?

I have a spreadsheet where each row is a set of numerical competition scores. Each row can be thought of as a single competitor in the overall competition, and each column is a competition event. ...
0
votes
1answer
95 views

Conditonal sum in Excel

I would like to write the formula (in OpenOffice, but I imagine it won't be any different than Excel) to perform the following calculation: -> Go through the values in column B from B1 to B30 and add ...
6
votes
2answers
2k views

In libreoffice calc, how can I use the fill handle to copy formulae but not the cell/border formatting?

I'm using LibreOffice 3.6.4.3 on Ubuntu 12.10. I use cell color and cell borders to group and distinguish groups of cells in my spreadsheet. I have bold horizontal borders every 4 rows. This is a ...
17
votes
3answers
14k views

LibreOffice Calc: How to get total for HH:MM:SS cells

I'd like to take a group of cells in the HH:MM:SS format, and add them up. I don't want to have the value wrap on the 24 hour clock. I just want an absolute total of the values. (The sum function ...
3
votes
5answers
7k views

How to convert text to numbers for all cells in a spreadsheet?

I have a spreadsheet containing values that are entered as text by having an apostrophe ' in front. I tried the search and replace function, but for some reason it couldn't find those cells containing ...
3
votes
3answers
5k views

Adding a constant in LibreOffice Calc

How do you add a constant to a group of cells in LibreOffice Calc? For example, here are four cells: 47 28 35 62 Now add 3 to each cell: 50 31 38 65
1
vote
1answer
41 views

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

Using Excel/Calc I have the following situation: a range of cells containing values; assume it's A1:B10, and a string I want to look for; for example, assume it is contained in C1. Now what I want ...
21
votes
1answer
17k views

What is in LibreOffice Calc the equivalent to Excel's “Paste Special > Value”?

In my professional life, I'm limited to use Microsoft Office. In Excel there is an option ows to render the result of a formula to be pasted as a "fixed value". For instance A1 = "I",B1 = "ride", C1 =...
5
votes
2answers
6k views

LibreOffice Calc prompting to update “links to other files”. Why?

I have a spread sheet that recently started asking "This file contains links to other files, should they be updated". I have been using this file for a while and don't know why it started, what it ...
5
votes
3answers
3k views

Why does LibreOffice Calc ask about updating links in Excel 2007 .xls file when Edit | Links is disabled?

(I don't believe this is duplicate of LibreOffice Calc prompting to update “links to other files”. Why?. See below for why.) The title basically says it. I have an .xls file originally created with ...
3
votes
3answers
2k views

How do you find the maximum value in a column, skipping to every nth value?

Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of ...
1
vote
0answers
52 views

Switch off autocorrect in libreoffice calc for specific document, column, cell

There is a way to completely turn off autocorrect in libreoffice calc. However, I would prefer turn off autocorrect in less scale: document, worksheet, column/row, cell. How it can be obtained?
1
vote
1answer
1k views

Get cell range for a function argument from the text of another cell in libreoffice-calc

Consider some libreoffice-calc function called MYFUNC which takes a cell range for example A2:B40 as an argument, so I can call MYFUNC(A2:B40). Now I want to be able to write A2:B40 as text into ...
-2
votes
1answer
66 views

How to compare number with number in other column and get corresponding value

If suppose I have data like column1 column2 504231 504109 504109 504201 504201 504299 then I used the following formula =IF(ISNA(VLOOKUP($A2,$B$2:$B$5,1,0)),"",VLOOKUP($A2,$B$2:$B$5,1,...