Spreadsheet component of the LibreOffice suite

**0**

votes

**0**answers

12 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

21 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

16 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

9 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

22 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

27 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

25 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

38 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

17 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

27 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

25 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

29 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

48 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

24 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

43 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

39 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

250 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

29 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

97 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

38 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

187 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

39 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

186 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

52 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

67 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

101 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

28 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

38 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

70 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

25 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

358 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

115 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

49 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

488 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

450 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

246 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

71 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

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

**1**

vote

**1**answer

193 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

150 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

63 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

235 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

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