Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

10
votes
4answers
11k 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.
18
votes
4answers
9k 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 ...
13
votes
2answers
22k 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 ...
3
votes
3answers
3k 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
3
votes
4answers
4k views

Finding min/max values in column where cell matches something

I have a spreadsheet laid out kind of like this: Date Project# 2012-01-01 130 2012-01-02 153 2012-01-03 153 2012-01-04 130 2012-01-05 130 2012-01-06 130 And I want to get ...
1
vote
2answers
2k views

Calculate sum of products in LibreOffice Calc

I am not that proficient in using Excel/Calc and I have a simple question. I have a table of my studies, my grades and the respective credit points on those. study grade cp a 2.3 8 b ...
0
votes
1answer
77 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 ...
0
votes
3answers
937 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. ...
6
votes
2answers
7k 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
3answers
1k 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 ...
1
vote
5answers
4k 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 ...
17
votes
1answer
14k 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 ...
6
votes
2answers
755 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 ...
4
votes
2answers
2k 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 ...
4
votes
1answer
4k 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 ...
2
votes
0answers
1k 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
1answer
934 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 ...