Spreadsheet component of the LibreOffice suite

**0**

votes

**1**answer

364 views

### How to change all cells in a spreadsheet to just one font type and size?

I just receive a spreadsheet that was converted from a PDF file. Due to the poor conversion, the cells contain a mixture of fonts and font sizes. For example in:
Future Insurance
The first ...

**2**

votes

**0**answers

2k views

### Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with
the font color red. All other rows are in the default font color.
Can I use the font color of a cell within a formula to react to ...

**4**

votes

**4**answers

4k views

### How to add month to date in LibreOffice calc

This seems like an obvious question.
I have a date column, and I want to create a formula to increase it by one month for each column.
10/2013, 11/2013, 12/2013, 1/2014, 2/2014, ...
How do you add ...

**1**

vote

**0**answers

394 views

### Libre Office Calc, User-defined Number format permille

I know that LibreOffice allows you to define custom number formats.
I have a spreadsheet that displays some smallish percentages (all are less than 1%).
I would like to use permille ‰ instead.
...

**1**

vote

**1**answer

728 views

### Autocomplete only on demand in OpenOffice Calc

In Open-/Libreoffice Calc, it is possible to enable the auto-completion of cells. That means the cell is filled e.g. with a string that starts with the characters already typed. This may give unwanted ...

**0**

votes

**2**answers

4k views

### How to do a “great circle” calculation in MS Excel or LibreOffice?

I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called ...

**3**

votes

**2**answers

509 views

### LibreOffice: stop automatic formatting of text into a formula?

In LibreOffice, I'm entering some text into cells, which happens to look like a math problem:
-5 / -10 / -15
The program assumes I want to calculate this, and sticks an equals sign automatically in ...

**1**

vote

**1**answer

569 views

### OpenOffice Calc - How to substitute multiple characters?

I need to substitute non-English characters in French words to the closest English charachers. For example:
"Combien ça coûte" Becomes "Combien ca coute"
I also need to remove some punctuation. Is ...

**0**

votes

**1**answer

918 views

### How remove all characters after pattern in LibreOffice Calc

In LibreOffice Calc how can I use Regular Expressions to find and replace every character after a pattern, and remove the pattern itself.
Example:
123_2015970_etgn.jpg
124_2015971_etgn.png
...

**3**

votes

**2**answers

14k views

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

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data.
Since I have to use multiple pivot tables, all with nearly the same data input but with slight ...

**11**

votes

**2**answers

19k 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 ...

**0**

votes

**2**answers

444 views

### Open/LibreOffice Calc data “extend selection” stops at blank rows

I have a spreadsheet and have right clicked on a column header then selected Sort from menu. The dialog box provides an option to extend the selection, which I accept. The problem is that the ...

**4**

votes

**2**answers

446 views

### Does libreoffice have a math concept like 'infinity'?

Does libreoffice calc have special numbers corresponding to -infinity and infinity similar to python's numpy?
They would obey rules like:
x = some number
infinity + x = infinity
-infinity + x = ...

**-1**

votes

**1**answer

133 views

### How to check if a date is in the future

I want to make a spreadsheet reporting daily and weekly goals for a certain quantity and their achievement, one reporting sheet per calendar week. Imagine that I open it on Wednesday, then I want to ...

**2**

votes

**3**answers

3k views

### libreoffice what is a fast way to paste special > text and numbers only (aka paste special values)?

I copy and then paste values in excel quite a bit (when I use Excel). When I use LibreOffice you can do the same thing (see related question here) but it is oh so tedious!
It gets especially tedious ...

**0**

votes

**1**answer

186 views

### Attempting to open TSV file in Libreoffice Calc results in being opened by Writer

I have been working with TSV files for months, but I have one file that refuses to be opened in LibreOffice Calc. It instead brings it up in writer. This behavior is consistent across command line and ...

**2**

votes

**1**answer

661 views

### LibreOffice Calc - apply style to neighbouring cells

I have a column with data and I applied a style to it with the help of Conditional Formatting. I now wish to apply the outcome of that style to the neighbouring column that holds other data.
Here is ...

**0**

votes

**0**answers

71 views

### Libreoffice won't let me have a read-only ComboBox

I have a database table with some entries. I want to display all the fields of this table as a ComboBox. I want the user to be able to select this any entry in this ComboBox without modifying the ...

**0**

votes

**2**answers

842 views

### Calculate conditional average on disjoint cells

I would like to know if libreoffice calc has the functionality to calculate an conditional average on disjoint cells. An example would be: Calculate the average for every second cell in Row1 if Row2 ...

**1**

vote

**1**answer

318 views

### Getting date of last change as a cell value in Open Office Calc

I have a few Open Office spreadsheet files (let us call them source files), each containing the financial info of a particular office of ours. I also have a summary spreadsheet, that reads some key ...

**1**

vote

**1**answer

1k views

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

I need for LibreOffice to automatically adjust row height for text entries.
I have tried to acomplish this by checking the wrap text option, which I thought would be the solution, but it is not.
How ...

**0**

votes

**3**answers

835 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

**1**answer

1k views

### Auto fit column value without increasing row height in LibreOffice

I m using Libre Office Calc on my Ubuntu 12.10 desktop and I need a help.
I am writing some data from other resource and save it for each row. I have only 2 columns which values are lengthy and rest ...

**1**

vote

**2**answers

1k views

### How do I mark weekends red in LibreOffice Calc?

I have a list with dates in it, and I would like to mark the dates representing weekends as red (for easier telling which day of the week it is without having to manually look up the day of the week ...

**4**

votes

**6**answers

5k views

### Quickly fill blank cells in a list in LibreOffice Calc

For example:
Cell A1 = 2/3/2012
A2 is empty
A3 is empty
A4 = 3/7/2012
A5 is empty
A6 is empty
A7 is empty
What I want to do is:
select the ...

**0**

votes

**1**answer

68 views

### Using LibreOffice and having problems, copying details

A staff member in our office is having trouble pasting details from a spreadsheet {In any format} to a template in .ott format.
When pasting and details from the spreadsheet, it removes some of the ...

**4**

votes

**2**answers

4k views

### How do I create a sum for each group of data in Libre Office Calc?

I have a simple spreadsheet like this:
Invoice_Number Invoice_Date Invoice_Amt
1000 1/1/12 1
1001 1/1/12 2
1002 1/2/12 ...

**1**

vote

**2**answers

1k views

### Is it possible to display a range of cells in a different sheet in LibreOffice calc?

I've been looking around for a while, but could not find a answer.
My budget sheet contains a meta-sheet called "settings". In "settings" there is an area e.g. C10:D20 that I would like to display in ...

**2**

votes

**1**answer

2k views

### Grouping date-level data by month to produce averages in Excel?

How would I return a monthly average with below cells without manually selecting each month separately?
A B
1 2013-02-13 25600
2 2013-02-14 5000
3 2013-03-15 12300

**2**

votes

**1**answer

2k views

### Lookup the nearest value in a column?

I have a column (A) containing numbers between 0-200. By comparing the value of A against the values of C, I want to pick the nearest cell and thereby return the value of D.
So, if A is 160 I want to ...

**2**

votes

**1**answer

1k views

### How do I generate a ranking/sort cells automatically in LibreOffice Calc?

For example I have a table with a caption and some numerical data, such as:
Mary | Sue | Mike
-----------------
100 | 20 | 30
-----------------
I want to generate a ranking, i.e.
1. Mary
2. ...

**0**

votes

**1**answer

1k views

### LibreOffice/Excel: SUM an adjacent cell if the value in the conditional cell is equal to N (SUMIF(?))

Disclaimer: I have no idea what I'm doing.
In LibreOffice Calc (3.6.2.2), I'm trying to SUM all of the values in G:x (where range is G2:G529), where B:x is equal to a given number N.
The data set ...

**-1**

votes

**1**answer

233 views

### Ways to delete or isolate entire rows in a huge data collection

I'm working with a huge datafile (about 400,000 rows and 8 columns) in Libre Office. I need to get rid of certain rows so I selected all cells with the desired value (with Find & Replace) and ...

**3**

votes

**2**answers

1k views

### How do I select non-adjacent ranges in libreoffice calc?

I'd like to perform an operation on a set of non adjacent ranges in LibreOffice calc.
I can type: C4:GA4 into the "name" box in order to select a single range. Is there a way to select non adjacent ...

**2**

votes

**1**answer

1k views

### LibreOffice Calc Date Insert - Ctrl + ;

For some time now I've been able to insert the date into LibreOffice Calc cells by pressing Ctrl; combination like in Excel.
With regard to this I have two questions:
Is it possible to make it ...

**3**

votes

**3**answers

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 ...

**0**

votes

**1**answer

280 views

### Libreoffice Calc SUM returns ###

In Libreoffice Calc, sometimes the SUM function returns ### in the cell when referencing other sums. For example:
=SUM(x1+y1)
where
x1=sum(a1:d1)
y1=sum(a2:d2)
gives
###
in the output cell. ...

**0**

votes

**1**answer

106 views

### How to make text in cell visible

Each field contains text, but how can I display it?
See C9 — it contains text, like all other cells:
How can I make it visible?

**1**

vote

**0**answers

161 views

### Formatting text in a LibreOffice Calc cell

Currently I'm working on the layout of a calendar for the new year 2013. I found a template that was designed in LibreOffice Calc.
How can I layout the text in the cell of a Calc program? What can I ...

**2**

votes

**1**answer

4k views

### Libreoffice calc relative and absolute cell reference on copy-paste

I'd like to create a simple Calc sheet, where column A contains the days, B contains how many hours of work i had done and C the price for that day. The price is simply a multiplication of hours (B ...

**0**

votes

**1**answer

2k views

### How to extract unique data from cells in a single column with thousands of similar values?

I have a single column in an Excel sheet with around 25,000 cells (numbers). Most of them are identical that in the end there are only - I guess - maybe 200 different numbers.
Is there a way to ...

**1**

vote

**0**answers

460 views

### Copy formula across row incrementing sheet number, down column incrementing number by 2

I have information on four different sheets I would like to copy to this summary sheet.
I want to know how do I copy the formula across the row and down the column on this summary sheet(say this is ...

**2**

votes

**1**answer

179 views

### Create pie chart with single sector separated from the rest?

Is it possible to use LibreOffice (I'm currently using 3.5.4) to create a pie chart where a single sector of the chart appears removed from the rest?
If so, how to do it?
Alternatively, can someone ...

**0**

votes

**1**answer

764 views

### Libreoffice Calc - Fill with Sum is not working

I want to add 2 columns of data and output the sum into a third column, with a =sum(a1+b1). With many rows of data, I try dragging down the highlighted cell to Fill the rest of the output column with ...

**1**

vote

**1**answer

674 views

### Libreoffice Calc: how do I copy formulas, but not values from a row?

My spreadsheet has rows with cells that contain either numerical values or formulas that use these values (they access only values from the same row). Each row corresponds to an event, when an event ...

**0**

votes

**1**answer

441 views

### calc: finding the number of numerical values in a given interval

I want to build a histogram of a set of values. So I need to get the number of values that are in a considered interval.
Say my values are on a sheet named 'LIST' in column A. On current sheet, I ...

**1**

vote

**1**answer

2k views

### Strip text from number in Libre Office Calc

I want to convert the string in column C into number. I want to convert "11,00 TL" to 11. How can do this?

**3**

votes

**1**answer

831 views

### LibreOffice Calc/Writer: How to get the dates for specifc weekdays per month?

I'm stuck with a problem in LibreOffice Calc or Writer:
I try to get a table of dates for every Monday, Wednesday and Friday per month, something that I can load every new month wich sets the dates ...

**2**

votes

**1**answer

3k views

### How to count how many rows contain particular values in a particular set of cells in libreoffice and excel

This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total".
In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D ...

**0**

votes

**1**answer

142 views

### Libreoffice equation does not get saved in xlsx

If I create equations in a LibreOffice Calc document, and save it as xlsx it wont get saved.
Is there a way to create compatible equations in the first place or to convert them to Microsoft Office?