Spreadsheet component of the LibreOffice suite

**1**

vote

**1**answer

211 views

### Use libreoffice-calc to create charts displaying the results of an exam

I would like to create charts from data in a libreoffice-calc document, displaying the results of an exam. For example, one column would contain numbers from 0 to, say, 100 (points reached), and I ...

**-1**

votes

**1**answer

180 views

### Change background fill of cells in LibreOffice worksheet containing specific text

How can I change the background fill of all the cells of a LibreOffice spreadsheet worksheet that contain a specified text?
In Excel that could be done by configuring the settings in the find and ...

**0**

votes

**0**answers

69 views

### Is it possible to recover cells with hidden content wiped out upon opening with LibreOffice

Some of my .xls Microsoft Excel files contain cells with hidden information formatted using custom code ;;; to deter casual viewing. I made the switch to LibreOffice a year ago and converted all the ...

**2**

votes

**1**answer

265 views

### Applying Conditional Formatting to all computed values in LibreOffice Calc

In LibreOffice Calc, how can I use the Conditional Formatting feature to apply a style to all cells which contain a computed value (via formula, as opposed to a directly input value)?
LibreOffice ...

**-2**

votes

**1**answer

36 views

### How can I type certain days (such as Monday, Wednesday) in an easy way for the next 5 months without typing them one by one?

I am trying to input in a libreoffice calc file only Mondays and Wednesdays for the next 5 months, but I dont wanna do it typing the dates one by one. So..I thought I would type 4 dates like this:
...

**1**

vote

**0**answers

123 views

### Default font and cell padding when opening files in LibreOffice/OpenOffice Calc

I've seen solutions for setting a default font when creating new files. What about a default font (and cellpadding) when opening an existing CSV file? LibreOffice seems to always default to Liberation ...

**1**

vote

**0**answers

95 views

### Disable prompt when opening a CSV file in LibreOffice/OpenOffice Calc

Every time I open a CSV file, LibreOffice presents me with a prompt asking to confirm Character Set, Language, and Separator options (tab, comma, semicolon etc). This prompt is not present in ...

**1**

vote

**1**answer

27 views

### Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour.
However, I'd like to generate a personalise replica of the first ...

**1**

vote

**0**answers

155 views

### How to force LibreOffice Calc to treat time values as numbers

I want to do something very simple in LibreOffice Calc:
I have a set of simple CSV files that contain lists of durations, formatted as HH:MM:SS.
I need to make a SUM of those lists.
With some ...

**0**

votes

**0**answers

98 views

### How do I unformat unused rows in libreoffice calc columns to reduce loading time/memory use in PHPExcel

I have just started using PHPExcel to try to read data for application development. I have found that a few of my libreoffice calc 3.4 spreadsheets can be read quickly while most take a very long ...

**2**

votes

**1**answer

372 views

### LibreOffice pivot: How to count 1 per line?

I have a list of fruit occurrences like this:
banana
banana
apple
orange
apple
And I want LibreOffice to give me a number of occurrences per fruit, for instance:
banana | 2
apple | 2
orange | 1
...

**0**

votes

**1**answer

198 views

### Libre Calc error on a float in decimal notation. Err. 519 (#value error)

There is a problem with my libreoffice.
I doesn't calculate cells with decimal points, possibly seeing them as text.
I have checked my locale settings (you can see them in the picture above) that ...

**2**

votes

**0**answers

247 views

### Open only the first 10 lines of large CSV file in LibreOffice Calc

I will start receiving many large CSV files, of which only the first few lines are of interest to me. I was hoping to open them by piping only the first few lines to Calc, but this doesn't work:
$ ...

**1**

vote

**1**answer

968 views

### Hour and minute count as decimal

I am working in LibreOffice Calc. I have a paycheck which for each day tells me the number of hours I worked, in this format 4:25, ie 4 hours and 25 minutes.
1.
First of all, I want to type 4:25 ...

**1**

vote

**2**answers

820 views

### Decimating rows in spreadsheet

Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.

**0**

votes

**2**answers

84 views

### Calculate # of shared elements in cells

I am struggling to get a calc formula to do the following calculations:
A B D G H A
A B C 2 1
A D E 2 1
A F G 1 2
B H I 1 1
As in the example, I need for the ...

**0**

votes

**1**answer

119 views

### What is the Excel equivalent of Find & Replace catch-all “*” in libreoffice calc?

In LibreOffice Calc, I am trying to find and replace all characters after a blank space to delete, and similarly to find and replace all characters before a blank space to delete, but the "*" is not ...

**0**

votes

**1**answer

3k views

### How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?

I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...

**0**

votes

**1**answer

82 views

### Are spreadsheet programs inherently unsuitable for “one row=one case” data?

Often when I'm working with large data sets, I structure the data in a way where every row equals one specific case. For example, in a psychological experiment, I input the data so that all data ...

**0**

votes

**0**answers

62 views

### Macro to manage sport ranking and calendar?

I need to write a macro (or a different easy way) to manage ranking and calendar for curling tournament.
The event will follow the Shenkel system
first match determined by general draw
after that ...

**1**

vote

**2**answers

1k views

### Move rows with the keyboard in Calc

How might one move a row to a different location with the keyboard? I have found this guide for the mouse but due to disability I have trouble using the mouse for this.
Note that I am not looking to ...

**0**

votes

**1**answer

548 views

### Currency conversion in LibreOffice Calc?

I have a *ods.-spreadsheet in LibreOffice. Some fields contain numbers in currency format (JPY), but they could also be in general number format. I want to create corresponding fields which display ...

**3**

votes

**1**answer

106 views

### How do I conditionally format a cell that contains a dropdown list

Hi Guys I want to know how to conditionally format a cell that contains a drop down list of 3 values. I want each value to represent a different coloured background when selected in the drop down ...

**1**

vote

**1**answer

76 views

### How can i reset my Libre Office Calc setting

I am using Libre Office Calc for my Excel work.
Image 1:
Image 2
I can not see the format color in on Libre office installed @My System.
but on my Colleague System who uses the same Libre ...

**0**

votes

**1**answer

524 views

### Column order in stacked bar chart is the the opposite from spreadheet

Often I have a spreadsheet in LibreOffice Calc like this:
Apple 423412 124233
Banana 234234 23434
Cherry 23324 423432
I then make a stacked bar chart. However the order of the stacked bar chart is ...

**2**

votes

**0**answers

434 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

**0**answers

154 views

### LO Calc: Why are data labels placement options greyed out?

When creating pie charts I like to add the values of the individual slices to the chart. LO Calc allows you to do this ('Add data labels') By default Calc places these values in the center of the ...

**3**

votes

**1**answer

580 views

### Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data:
A B C D E F
...

**0**

votes

**1**answer

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

**2**

votes

**0**answers

30 views

### Libreoffice calc: pagebreak within row?

When printing a LibreOffice Table,
LibreOffice will automaticaly place the pagebreaks so that they appear after/before a row.
When having many tall rows this results in a heavy paper-usage. How can I ...

**0**

votes

**1**answer

136 views

### DateTime to just Date

In LibreOffice, Calc under Linux, I have a table with DateTime values (eg. 2011-06-15T15:37:56+00:00). How shall I convert these values to just Date values (eg. 2011-06-15)? I suspect it is very ...

**3**

votes

**3**answers

187 views

### How do I convert a delimited list to a multicolumn table in excel?

I have a list like this:
Friut;Taste;Color;Other;Apple;Good;Red and Blue;1;Orange;Really Good;Orange;12
And I want to convert it by selecting every 4 delimitations and turning them into rows like ...

**1**

vote

**1**answer

249 views

### Make a system call to calculate a spreadsheet cell value

Is it possible for me to make a system call in order to calculate a cell value in LibreOffice Calc?
To clarify, can I set a cell's formula to something like this:
=B3*C4-system("/my/amazing/script ...

**3**

votes

**1**answer

216 views

### Openoffce Calc - Substitute space with line break by function

in my calc document for example cell A1 has the value
A1:"AB 0123"
and in cell B1 I need the same value with a line break instead of the space like
B1:"AB\n0123".
How can I archive this?
I ...

**1**

vote

**1**answer

83 views

### Following Sunday based on date in LibreOffice Calc [duplicate]

I'm looking to create a calculation to display the following Sunday in day-month format based on the current date (=NOW()).
How can I do that?

**1**

vote

**5**answers

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

**0**

votes

**1**answer

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

**2**

votes

**4**answers

3k 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

305 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

573 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

3k 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

424 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

465 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

772 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

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

**8**

votes

**2**answers

14k 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

392 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

368 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

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