Spreadsheet component of the LibreOffice suite

**1**

vote

**1**answer

62 views

### How to add a Table of Contents to a LibreOffice Calc file?

Adding a ToC in LO Writer is no problem, but how to insert a ToC in a ods file? For a workbook with tables longer than one page to be distributed as printout (not as file), it would be nice to have a ...

**2**

votes

**2**answers

918 views

### Numbers imported into Ubuntu Libreoffice calc from text or csv files are coming in as text

I working in Libreoffice 3.6.0.2 in Ubuntu. I have a text file that has some columns of text and columns of numbers. When I import them into Calc either by copying and pasting or saving as a csv and ...

**1**

vote

**2**answers

19 views

### Merge / glue exported pdf from spreadsheet application

Spreadsheet applications offer to export tables as pdf. Often, the table is to wide to fit on a single page (without scaling), so several pdf pages need to cover the full width of the table.
Let us ...

**0**

votes

**2**answers

62 views

### Extracting named groups of regular expression in LibreOffice Calc

In Python 3, it is quite easy to extract text from named groups as shown in the following example:
import re
myStr = r"4000/2000/5000/7000"
reObj = re.compile(r"""(?P<g1>\d+) # a capturing ...

**0**

votes

**1**answer

9 views

### Libreoffice Calc XY scatter graph - How do I add minor gridlines?

The option is greyed out under Format > Grid. I have looked here and here but when I change Format>Axis>Y axis>Scale and set it off automatic and change the minor interval count, minor gridlines still ...

**0**

votes

**0**answers

15 views

### Some Conditional Formats 'break' after i reopen my sheets

I'm using LibreOffice Calc here and have noticed that once i reopen my sheets, some of my Conditional Formats stop working and have #REF in them. It's an easy fix, all I have to do is remove a couple ...

**0**

votes

**1**answer

564 views

### Weird glitch after copying from a CSV file?

I was doing a programming job that involved writing data to a large CSV file (700 MB). I occasionally opened the file using libre office to check the data coming in.
This morning I opened the file ...

**1**

vote

**1**answer

23 views

### SUMIF criteria string with spaces?

me again;\, how do i get the criteria part of a SUMIF formula to recognize a string that has spaces?
example (sorry for poor formatting, i have no idea how to format more spaces:():
A B
Tim ...

**1**

vote

**1**answer

22 views

### How to perform Conditional Formatting on the max value of a range of cells?

i'm assuming what i'm trying to accomplish here is really simple, so bear with me here. i just want to create a Conditional Format on a row of cells and have it highlight (bold'ed) the maximum value, ...

**0**

votes

**0**answers

11 views

### LibreOffice Calc 5.1.1.: Fill does not appear in pull down Edit menu

In LibreOffice Calc 5.1.1.: Fill does not appear in pull down Edit menu when I have 2 or more cells highlighted. How do I fix this?

**0**

votes

**1**answer

8 views

### How to get a list of all named ranges in calc

I want to list out all the named ranges and the data ranges of a Calc workbook containing several sheets. I am jus tmoving from excel to calc and thus not too familiar with functions/macros in calc.

**3**

votes

**1**answer

32 views

### Is there a way to add and/or logic into the criteria field?

Using LibreOffice Calc with the expression SUMIF, is there a way to make the criteria field look for 2 types of values using OR logic? Or do I have to make 2 separate SUMIF's and just add them up?
A ...

**3**

votes

**1**answer

16 views

### How to sum the values of one column depending on the value of another?

with LibreOffice Calc I'm trying to set something up so it sums the values of column D IF the corresponding row in column A contains certain words. So far I know sumif works, but how do I set it up so ...

**0**

votes

**0**answers

13 views

### Libreoffice Calc - how to drag cell leaving formula in original cell?

Is there a way/setting to leave the formatting in a specific cell after using 'cut' or dragging the content of the cell away?
'Protecting' the cell is not what I'm looking for since that disables ...

**2**

votes

**1**answer

480 views

### How do I convert dates from Open Office format to MS Excel format

I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols.
I believe the problem is to do with the two ...

**0**

votes

**1**answer

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

**1**

vote

**3**answers

17 views

### How to convert plain text files into libreoffice cal (Excel sheet) on Ubuntu 14.04?

I have a plain text file containing a huge amount of data. The data's are separated by semicolon and a newline for starting a new line of the following data. Here's a sample:
...

**2**

votes

**2**answers

37 views

### Identify cell containing given text in LibreOffice Calc

Is there a function in LO Calc that will find a cell with the given text?
The search and find functions appear to give a location within a string. That is, if I search for the text "a" within two ...

**2**

votes

**1**answer

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

**2**

votes

**2**answers

41 views

### Can I retrieve and access a simple JSON object via HTTP in LibreOffice Calc?

I've located a web API resource that I'd like to access from LibreOffice Calc.
It returns a simple JSON/JavaScript object that's basically just a handful of name value pairs, one of which I want to ...

**0**

votes

**1**answer

57 views

### How to use VBS to manage LibreOffice Calc files?

I am learning VBS scripts and someone gave me a code below, that works with Microsoft Excel. How to convert it to work with LibreOffice Calc?
Dim ObjExcel
Call ExcelSetup("Sheet1")
Sub ...

**6**

votes

**5**answers

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

**1**

vote

**1**answer

15 views

### Fill right in open/libreoffice

I'm using Libreoffice calc for an application where it's necessary form me to fill right. I know that fill down is Ctrl d but is there a way to fill across to the right?
Thanks in advance

**0**

votes

**1**answer

13 views

### Libreoffice : Getting cell value from a range referring to another range

i have a row '1' of minutes ( 1,2,3,4,5 ..) and below a row '2' of values ( 10,50,5,60,9..). How to get the minute of the greater value in cell C2 ?

**1**

vote

**2**answers

79 views

### Excel/Calc INDIRECT+ADDRESS+ROW+COLUMN cleaner alternative

I'm not a regular Excel/Calc user, but I'm currently using LibreOffice Calc to record some money ins and outs. My two functions below works fine as intended, but still I'd like to know of a cleaner ...

**0**

votes

**2**answers

38 views

### Calc, formula to find the index of the minimum value

In LibreOffice Calc, is it possible to write a formula which, given a column of unsorted data (see example below), returns the largest index of the minimum value greater than zero? The following ...

**0**

votes

**2**answers

27 views

### How to find if 2 columns (or rows) in LibreOffice Calc are identical?

I have two long columns of data in LibreOffice Calc, something like below:
Jimmy | Jimmy
Bunk | Bunk
Lester | Lester
Prez | Prez
...
These columns are most likely identical, that is, they ...

**3**

votes

**5**answers

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

**1**answer

43 views

### How does one use LibreOffice FILTERXML function on XML documents with namespaces?

I'm struggling to get the value of an XML node using the FilterXML() function when the XML document's document element has a namespace specified.
Example:
<?xml version="1.0" ...

**15**

votes

**3**answers

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

**1**

vote

**1**answer

33 views

### Generating unique number in LibreOffice Calc

So I have a document that allows a user to select a site/department from a dropdown list. Each site/Department has a unique extension prefix e.g. 10, 11, 12, 13, etc. When a user selects the desired ...

**1**

vote

**1**answer

60 views

### Manually “drawing” line graph chart and export data

I would like to manually create a line graph and then sample the created line to generate a data series from it.
Basically I have an idea, what a plotted values should look approximately and would ...

**0**

votes

**2**answers

33 views

### How do I change date origin in libreoffice writer?

I am copying a chart from a Calc file to a Writer file. The Calc file has a date origin of 1904. When I copy the chart to the writer file, all the dates get shifted because the origin is (I suppose) ...

**1**

vote

**1**answer

57 views

### Import from csv to Libreoffice Calc using 'two blank spaces' as delimiter

I want to import some data from a csv/text file to Calc.
In the first column I'd like to have the 'Dummy text # i', and in the second column the numerical value.
In the text file, 'Dummy text # i' ...

**3**

votes

**1**answer

68 views

### Libreoffice, how to copy from Writer and pasting unformatted in Calc, by default?

I have the revision of a translated text, made by the client, I am the third party reviewer and have to fill up an .xls module with all the source texts, the translated one, and the amended by the ...

**1**

vote

**1**answer

18 views

### stacked area chart with secondary data range

What I have is this:
and I am trying to achieve this:
Using Calc 5, how do I manage the second column to be recognized as secondary data range?

**0**

votes

**1**answer

103 views

### Using libreOffice Calc function in macro

I am trying to create a basic macro function in calc to perform a hexadecimal to binary conversion with more then 10 bits. But I've got a lot of troubles.
First, where can I find actual ...

**2**

votes

**1**answer

39 views

### How can I access the spelling check correction in LibreOffice Calc?

LibreOffice Calc 5.0.4.2 on Ubuntu Linux 15.10 underlines misspelled words but does not display spelling check suggestions.
Is there a way to add spelling check suggestions to the right mouse click ...

**1**

vote

**0**answers

20 views

### How can I supress zero values in a LibreOffice line line chart? [duplicate]

I'm making a line chart counting users of various releases of a piece of software over the years. This is a very spiky line chart, where as new versions come into existence and then retire, lines ...

**1**

vote

**1**answer

29 views

### MDETERM gives Err:502 on non-contiguous range of cells

I have the following setup:
A 3x3 matrix in the cells A1:C3 and a 3x1 vector in D1:D3.
I would like to calculate the determinant of a new matrix formed by the 1st column of the original matrix, the ...

**0**

votes

**1**answer

37 views

### Get the value of one cell based on the value of another

Edited with additional context:
Embedded in a LibreOffice-5 Calc spreadsheet I have a two column array where one column contains dates and the other column values. I wish to select the row based on ...

**0**

votes

**2**answers

62 views

### Conditional Formatting based when equal/not equal to another cell in the same row

I have a spreadsheet like this in LibreOffice
A | B | C | D
-------+----------+---------+---------
1 | A | 1 | B
2 | C | 2 | C
3 | D ...

**0**

votes

**1**answer

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

**4**

votes

**2**answers

245 views

### How does the group feature in LibreOffice Calc work?

LibreOffice Calc has as Group feature. The official help site just tells you where to find the button and what hotkey to use, but doesn't explain what grouping actually does:
...

**3**

votes

**2**answers

95 views

### Perform Sum over Conditional Products

I do have a list like this
╔═════╦═══╗
║ 1.5 ║ A ║
║ 2.5 ║ B ║
║ 2.5 ║ ║
║ 5.2 ║ A ║
║ 6.4 ║ C ║
╚═════╩═══╝
and want as result:
╔═══╦═════╗
║ A ║ 6.7 ║
║ B ║ 2.5 ║
║ C ║ 6.4 ║
╚═══╩═════╝
So ...

**2**

votes

**1**answer

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

**0**

votes

**1**answer

40 views

### Is it possible to automatically separate line/scatter plot data into different series based on an additional column?

My data looks something like this:
H, 2015-12-11T19:44:34, 3270
W, 2015-12-11T19:44:34, 2150
H, 2015-12-11T19:49:35, 3370
W, 2015-12-11T19:49:35, 2290
...
As a one-off, I can get ...

**2**

votes

**1**answer

2k views

### LibreOffice calc: How to apply multiple tables to a single chart

I'll say it right off the bat - I am a complete Excel/Calc amateur.
What I want to do is apply 3 tables, each with 2 columns of data into a chart with 1 XY-line for each.
My current data looks ...

**1**

vote

**1**answer

56 views

### Connect data points around missing data with different style of line on graph

I have an libreoffice-calc spreadsheet with some results of daily measurements. Some days are missed for some reasons.
I want to create a graph showing this data, and I want to highlight all missed ...

**3**

votes

**1**answer

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