Spreadsheet component of the LibreOffice suite

**2**

votes

**3**answers

2k views

### How to find and remove external links in (Libre|Open)Office Calc spreadsheet

I have a LibreOffice Calc spreadsheet which pops up a message asking me whether I wish to update external links. I did not add external links myself, but did copy data from another Calc window at one ...

**2**

votes

**1**answer

119 views

### SELECT until endless in LibreOffic Calc

I am used to =COUNTA(B3:B999) and change it when the number of rows exceeds 999, but I was wondering whether there is an endless setting.
Attempt
=COUNTA(B3:B) did not work.

**-1**

votes

**1**answer

578 views

### How to remove Open Office spreadsheet duplicates in a column while keeping rows together?

I have a spreadsheet with a list of column data of contacts, first name, last name, address, phone number, email...
I want to remove the duplicate email addresses but the entire row may not have ...

**0**

votes

**0**answers

188 views

### LibreOffice calc; is it possible to automatically make cells coloured according to the HTML code entered into them?

I am creating a LibreOffice calc spreadsheet designed to be basically be a customized version of the table entitled "Default element colors, by atomic number:" on this page ...

**0**

votes

**1**answer

160 views

### LibreOffice Calc Error 518

I'm trying to achieve the following in LibreOffice (did it with Excel already, but importing this excel-created file produces the same error).
I have 3 columns
(A) (B) (C)
ECTS Mark ...

**0**

votes

**1**answer

64 views

### Calculating total salary

I store my working time in Libre Office Calc like this:
Start End Hours
10:00 15:00 05:00
11:00 14:00 03:00
09:30 15:30 06:00
Total
14:00
Now I want to calculate my salary, i.e. ...

**1**

vote

**2**answers

438 views

### #NAME? when referencing cell in different worksheet

I'm using libre calculator. I just want to reference a cell in another worksheet in the same workbook, but I get #NAME? showing up. I know in the past, I've had this problem before, but never ...

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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

**2**

votes

**4**answers

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

**1**

vote

**2**answers

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

**2**

votes

**1**answer

961 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

**1**answer

454 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?

**0**

votes

**1**answer

79 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

455 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?

**0**

votes

**1**answer

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

**0**

votes

**2**answers

35 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

309 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

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

**4**

votes

**1**answer

82 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

75 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

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

**1**

vote

**0**answers

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

**2**

votes

**0**answers

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

**1**

vote

**1**answer

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

**1**

vote

**1**answer

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

**6**

votes

**5**answers

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

**0**

votes

**1**answer

37 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

**1**answer

451 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

130 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?

**1**

vote

**1**answer

1k views

### LibreOffice graph axis label

I have made a graph out of about 20 lines of dates and 20 lines of values, but the graph doesn't show all the dates, which really bugs me, it just shows 'month' difference:
I would like to have ...

**0**

votes

**2**answers

75 views

### Convert named months date text to date

I have a date text with named months like this
06:29 Mar. 17 2014
I tried this but obviously I need to convert Mar to a number
=DATEVALUE(RIGHT(G1, 4) & "-" MID(G1,7,3) & "-" MID(G1,12,2))
...

**7**

votes

**2**answers

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

**3**

votes

**2**answers

107 views

### Match cell content based on line break

I take data from a helpline call in site and transfer it to Linux Libreoffice Calc (LC).
Column C cells have 2 sets of data - two telephone numbers - 1 being the caller and 1 being the forwarded ...

**1**

vote

**0**answers

58 views

### Return an array from a lookup

I have a table with column A containing vendors and column B containing product codes. One vendor has many product codes.
In another place I need an array of product codes for a specific vendor. The ...

**1**

vote

**0**answers

258 views

### updating links in LibreOffice Calc xlsxfile results in err:509

I have just started to use Libreoffice so I am totally new to the system and immediately encountered a problem with a calc document.
The document has originally been made in Excel (but on a Mac) and ...

**1**

vote

**1**answer

229 views

### Search for non-characters (character replacement indicators)

I have a text file (actually a .csv exported from .xlsx in LibreOffice) with lines that have gibberish when opened in LibreOffice Calc. In VIM they appear as the following:
Joe User, joe@user.com, ...

**1**

vote

**1**answer

39 views

### In LibreCalc, how to delete rows based on WHERE condition

This question told me to ask my excel question here:
http://meta.stackexchange.com/questions/90919/which-stack-exchange-site-is-best-for-microsoft-excel-questions
I'm actually using libre calculator.
...

**1**

vote

**1**answer

26 views

### Is entry in colum a (at least once) related to entry in colum b?

I'm stuck with a seemingly simple spreadsheet (LibreOffice) problem. Assuming I have three columns given as Name, Day and Mood; how can I find out if person x was happy at least once?
In a second ...

**1**

vote

**1**answer

48 views

### How to use third column as identifier in line-graph

I want to draw a diagram(line-graph) from a three column sheet
Input
| Date | Value | Identifier |
| ------------- | ------------- | ---------- |
| 01-04-2014 | 1 | ...

**0**

votes

**1**answer

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

**1**

vote

**1**answer

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

**2**

votes

**1**answer

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

**3**

votes

**1**answer

3k views

### LibreOffice Calc - How can I create a custom legend?

I'd like to use a custom legend for a diagram. Is that possible?
If it's helpful for anyone:
Xubuntu 13.10
LO 4.1

**3**

votes

**2**answers

2k views

### Interpolate values between two cells

I've got a spreadsheet that looks like this in Libre Office Calc:
+-----------+
| | A |
| 1 | 444 |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| ...

**-2**

votes

**1**answer

60 views

### How to compare number with number in other column and get corresponding value

If suppose I have data like
column1 column2
504231 504109
504109 504201
504201
504299
then I used the following formula ...

**1**

vote

**2**answers

792 views

### How to compare numbers in columns

I have data in libreoffice like
ColumnA ColumnB
504231 504109
504109 504201
504201
504299
like so much data Then I want output like
ColumnA ColumnB
504231
504109 504109
504201 504201
...

**-1**

votes

**1**answer

99 views

### How to add the current page number in a cell of a calc sheet

I would like to add the current page number in a cell of that page and to refer back this cell for this page number in a cell somewhere in the same sheet / in another sheet. Is there a way to do so?

**1**

vote

**0**answers

254 views

### Why is libre office calc inserting a ' at the beginning of the cell?

This is the most ridiculous thing i've encountered.. I've imported a csv, I end up with nice neat clean rows of numbers, no formatting, nothing weird, no " or ' left over, just numbers. When I go to ...

**0**

votes

**2**answers

38 views

### keyboard equivalent of dragging the little black square to generate sequence

What is the keyboard way of generating a sequence?
The pic below shows libre office calc. In order to generate the sequence of numbers 1 to 10. I simply enter few numbers of the sequence (in ...