Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

11
votes
4answers
6k 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 ...
8
votes
4answers
8k 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.
3
votes
3answers
2k 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
3answers
2k 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
3answers
522 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. ...
3
votes
2answers
3k 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 ...
2
votes
3answers
736 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 ...
14
votes
1answer
11k 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
102 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
1k 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
1answer
3k 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 ...
1
vote
5answers
990 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 ...
1
vote
1answer
736 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 ...
0
votes
0answers
159 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 ...