Spreadsheet component of the LibreOffice suite

**1**

vote

**1**answer

17 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

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

**0**

votes

**1**answer

31 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

9 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

13 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

**0**answers

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

**0**

votes

**1**answer

24 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

26 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

**0**answers

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

**0**

votes

**1**answer

26 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

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

29 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

47 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

19 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

28 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

27 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

32 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

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

**0**

votes

**0**answers

30 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

44 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

46 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

**2**answers

457 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

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

**1**answer

115 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

41 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

252 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

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

**6**

votes

**2**answers

225 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

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

**0**

votes

**0**answers

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

**0**

votes

**0**answers

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

**0**

votes

**0**answers

8 views

### How to create readable libre office expressions

When an expression gets longer, it's nice to format it on multiple lines, much like we do for source code. In Libre office Calc, new line can be be entered in cell using Ctrl+Enter.
But once you ...

**0**

votes

**1**answer

107 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

29 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

39 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

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

**0**

votes

**0**answers

29 views

### LibreOffice calc: Can I create a yearly sum out of monthly sheets?

As a simplified example, let's say I have the expenses of a small company in one file per year, and every file has 12 sheets (one per month).
The sheets have these columns:
date|department|amount
...

**1**

vote

**1**answer

479 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

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

**0**

votes

**0**answers

52 views

### LibreOffice Calc dynamic text sort

Given the following table:
A B
1 AA AA
2 HH BB
3 BB HH
4 II II
5 UU LL
6 LL UU
I need to sort the values of column A to B dynamically, using only ...

**3**

votes

**1**answer

671 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

**1**

vote

**2**answers

514 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

44 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

**1**answer

289 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

38 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

83 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

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

**1**

vote

**1**answer

358 views

### Libreoffice: format field yyyymmdd to date

How can I format a field value like 20140226 to 2014-02-26 in LibreOffice? If I use the format cell option I get strange values.

**0**

votes

**0**answers

20 views

### Hide rows that have a zero value

I've got large spreadsheet with over 4000 rows. In column G I have a sum. If that sum is 0 I want to hide the entire row.
Conditional formatting doesn't seem to do the job.
Is it possible to hide ...