Spreadsheet component of the LibreOffice suite

**0**

votes

**0**answers

14 views

### How to nail LibreOffice references in place?

I have a series of cells with very simple formula in them (e.g: G2=G4-G5) and I want that to remain fixed whatever I do to the sheet, in particular I want it to be resistent to insertion of a row ...

**3**

votes

**1**answer

196 views

### Cut and transpose in LibreOffice

When using cut and pasting LibreOffice maintains exact formula used along with moving old references to point to the new location, however when using paste special: transpose it treats the past as if ...

**0**

votes

**0**answers

6 views

### Generating unique values in Libre Office Calc

I've got a list of entries in a range in LO Calc. The max size of the range is 1 X 9, but may include blanks and/or duplicates. How can I extract a list of unique values automatically, so that my user ...

**0**

votes

**1**answer

22 views

### How to save single sheet, as separate file in LibreOffice?

When I have multiple sheeet in LibreOffice document, how to save specific sheet as new .ods file in LibreOffice?

**3**

votes

**5**answers

19k views

### How to separate number by its decimal point in Excel/Calc?

I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...

**1**

vote

**1**answer

608 views

### Changing the units on the x-axis in a LibreOffice Calc chart

I've used the LibreOffice chart wizard to make a chart, and I would like the x-values to be different to the default of 1,2,3... (in this case, 500,1000,...). How can I do this?

**1**

vote

**4**answers

167 views

### Count the number of rows with equal values in two specified columns

I have two columns in MS Excel/LibreOffice Calc/Google Spreadsheets with numbers. I would like to count the number of rows which have identical values in both columns.
In the following example:
...

**3**

votes

**2**answers

2k views

### How to efficiently insert a table from R into a Word document?

I am creating a Word document (.docx) using LibreOffice. I need to include some numeric results I calculated in R. They look like a simple table of numbers, with text captions for the rows and ...

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

**0**

votes

**1**answer

31 views

### Parsing dates in LibreOffice Calc - is there a better/easier way?

I have a string of this particular format:
DD/MMM/YY HH:MM AM/PM
e.g.
03/Nov/14 9:00 AM
I could not find figure out how to make Calc recognize it as a date so I had to do the following for a ...

**0**

votes

**0**answers

22 views

### How to swap two columns in specific rows in LibreOffice Calc?

I have a few hundred contacts stored in Google Contacts, and I would like to sort them. I exported them in CSV and opened them with LibreOffice Calc. Some of my contacts have first and last name ...

**9**

votes

**4**answers

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

**0**

votes

**1**answer

67 views

### Conditional format cells, based on their text value in Libre Office

This is driving me crazy. I try to set up the simplest conditional rule, but it will not work: format cells based on their text value.
The sense is to colorize a separator line. Until now I had ...

**0**

votes

**0**answers

11 views

### assign string “tags” based on numeric interval in Libreoffice Calc

I'm struggling with an easy issue. I'm working in Libreoffice Calc and I have a numeric column with values from 0 to 365.
What I would like to do is to create another column assigning string values ...

**0**

votes

**0**answers

19 views

### Copy distinct values in calc

I have some text data in column A, adding data to that column is the standard use case. I would like Calc to dynamically copy only unique values to, say, column D.
I am aware of OpenOffice: get ...

**2**

votes

**0**answers

58 views

### How to customize default CSV format in LibreOffice?

Every time I export my data to CSV (and I do it often) I have tick the "Edit filter settings" checkbox, choose UTF-8 for the encoding, and semicolon for the fields separator manually.
I do this ...

**3**

votes

**4**answers

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

**0**

votes

**1**answer

31 views

### LO Calc / Excel - Dropbox change another cell

I am creating a simple Point of Sale System for a store using an excel/libre office spreadsheet.
The spreadsheet has a sheet called "Inventory" which has the following columns:
Name
Price
This ...

**0**

votes

**1**answer

39 views

### Can I format cell font colours in LibreOffice Calc with more than just red, green, or blue?

I have a row of cells with this user defined format:
[GREEN]0;[RED]-0
With this format, positive numbers are green, negative numbers are red.
The problem is that the default green is too bright ...

**2**

votes

**1**answer

639 views

### How to make a LibreOffice Calc XY (scatter) plot with named dots?

I have a simple XY scatter plot made from a 2-column spreadsheet. When I hover over a dot, I can see the ID of the dot (e.g. "Data point 8456").
I would like to know if I could have the content of a ...

**0**

votes

**1**answer

34 views

### How to adjust the Y Axis maximum value on a bar chart in LibreOffice Calc

How do I change a chart so that the highest values actually are at the top of the chart? Say the chart goes to 1600, but I want it to go to 1500?

**1**

vote

**2**answers

913 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

**1**answer

104 views

### LibreOffice Calc time formatting trouble

I'm calculating a time sheet with LibreOffice with this SUM column.
00:00
08:00
08:00
08:00
08:00
08:00
00:00
04:00:00 PM
The printing format of the bottom row is Time 876613:37:46 but that's not ...

**8**

votes

**3**answers

4k views

### Permanently and globally stop openoffice calc from autocorrecting dates/time

When I type "15:45" or "21/12" openoffice Calc changes it to "15:45:00" and "21-12-2011".
How do I disable this globally and permanently?
I know you can use "Format cell..." and then select text, but ...

**4**

votes

**2**answers

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

**1**

vote

**1**answer

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

**0**

votes

**0**answers

18 views

### Libre Office Calc - how to suppress search results window when performing a macro search?

I work with a macro that finds and replaces certain characters in a spreadsheet. I use this macro a lot and I don't need the "search results" window to pop up after every search since I am not ...

**1**

vote

**1**answer

58 views

### How to set different orientation for different sheets in OpenOffice.org Calc?

I have document with two sheets sheet1 and sheet2 and i want set orientation of sheet1 to portrait and orientation of sheet2 to landscape.
This can be done in Excel, but in OpenOffice when i set ...

**1**

vote

**0**answers

36 views

### Equivalent for FILTER() formula in Libre Office

In a Google Spreadsheet, I create a report sheet by entering a formula like this in the top left cell.
=FILTER(Sheet1!A2:E, Sheet1!E2:E > 0)
This gives me all the rows in the Sheet1 from row ...

**0**

votes

**2**answers

29 views

### Fix problematic number formatting

In a spreadsheet I have several columns which had the following number format: 233,95. I have replaced all commas with dots, to 233.95, but it turns out that this was not such a good idea as there are ...

**0**

votes

**1**answer

39 views

### Creating 2D data from a flat/1D list of spreadsheet entries

This seems like it'd be a really common operation, but I don't know the terminology or what to search for. I have a table like the following in CSV:
+------+------+------+------+
| Test | Var1 | Var2 ...

**0**

votes

**1**answer

69 views

### Highlight a row based on its first cell date value

I have a sheet with the first column representing date values (e.g. 09/04/14), and I'm looking for some way to make calc highlight the current day's row.
I already used Format -> Conditional ...

**3**

votes

**1**answer

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

**4**

votes

**1**answer

20 views

### Removing functions from a column, but keeping data (convert to normal text)?

I have a column that contains the results of a few different formulas. I want to convert all of these cells to normal text, getting rid of the formulas.
I know I'm missing something obvious here.

**0**

votes

**2**answers

34 views

### How do I make a date field in Excel/LibreOffice Calc that is 180 days from today?

So I have two columns, one is the current date (currently using a 09/06/15 format) and I have another column that is meant to be for a date 180 days into the future. How can I create a formula that ...

**0**

votes

**1**answer

28 views

### Is there a way to have groupings of bars in a bar chart in?

I know this may be bad style, but I want a bar chart that groups certain bars together. For example if I was comparing the speed of two models of cars, and considering the speed under wet and dry ...

**8**

votes

**2**answers

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

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

**2**

votes

**1**answer

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

**2**

votes

**0**answers

585 views

### LibreOffice Calc, how to filter rows based on interval

I would like to filter a spreadsheet on LibreOffice 3.4.4 Calc, based on values of a second spreadsheet column. How can I do that ?
I would like intersection and, if possible, complement filters. So ...

**0**

votes

**0**answers

36 views

### How can the background of a cell in LibreOffice Calc be set to a hex value contained another cell?

How can I make a cell in LibreOffice Calc have a background colour equal to a hex value specified in another cell?
For example, if cell A1 has the value e51c23, how can I set the background of cell ...

**1**

vote

**1**answer

46 views

### How to switch the “input field” between “formula mode” to “numerical mode”?

In the below link, I have made a simple example where A1=1, A2=2 and A3=3 and then at A5 the formula "=A1+A2+A3" is written.
What I now wonder, is there any special keyboard shortcut or icon-shortcut ...

**3**

votes

**3**answers

191 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

**2**answers

704 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

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

30 views

### Format text list with corresponding key into spreadsheet

I have a text file with dates arranged as such.
name1
2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
name2
2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
name3
2011-01-05 (i)
2011-05-05 (i)
2011-06-14 ...

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

**6**

votes

**2**answers

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

**2**

votes

**1**answer

132 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

45 views

### How to make LibreOffice Calc look like Google Sheets? [closed]

I'm trying to transition from Google Docs to LibreOffice for privacy concerns. Does anyone have any advice of how I could make it more minimalistic and clean like Google Sheets?