Spreadsheet component of the LibreOffice suite

**1**

vote

**1**answer

11 views

### Applying reference to another sheet to all cells

I have LibreOffice Calc file which has 2 sheets.
I wanted to reference Sheet1 from Sheet2, so I wrote function =Sheet1.A1 in Sheet2 cell A1.
How do I apply this to all cells on Sheet2? (Sheet2.B5 = ...

**0**

votes

**0**answers

12 views

### Styling multiple files on Libre Office Calc [duplicate]

Here's what I do now:
Get CSV file from extrenal source
Open it in Calc
Apply style (set colors, fonts, etc.)
Export as PDF
Is it possible to get rid of step 3? Somehow loading data to already ...

**0**

votes

**0**answers

9 views

### LibreOffice Calc performance tuning

I have 350 thousand rows in a LibreOffice Calc table (it's a 35 meg CSV file). I only need to view it, not edit it. Yet, it is very slow at scrolling, filtering, and selecting the cells. What can I ...

**0**

votes

**1**answer

23 views

### Time format of Pomodoro in LibreOffice Calc

I'm using LibreOffice Calc in Windows 7 and I'd like to write a formula that, given a decimal number, converts it to hours and minutes. For instance I have this:
cell E3 is =($B$3*25/60) i.e. it ...

**0**

votes

**0**answers

19 views

### LibreCalc Date-function returns wrong month

I have a spreadsheet with three columns for "Date", "Currency", and "Amount".
Date Currency Amount Month
06.02.2015 EUR 188 March
30.01.2015 EUR 285 January
I ...

**0**

votes

**2**answers

37 views

### Change color of pasted email addresses in LibreOffice Calc

I use LibreOffice Calc on a Linux system. Sometimes when I paste text into a cell (in particular, email addresses) they display as blue font on grey background. But I want black font on white ...

**0**

votes

**1**answer

59 views

### How to plot IP address frequency distribution chart on Excel or Calc?

I have one column of data:
IP Address
1.2.1.2
1.2.1.2
1.3.4.1
1.3.4.1
1.3.4.1
1.3.4.1
1.3.4.1
1.10.0.5
1.10.0.5
1.10.0.5
2.1.2.1
2.1.2.1
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
12.1.2.3
...

**0**

votes

**0**answers

23 views

### Create a pie chart from amount/category pairs

From a table like
Amount | Category
2 A
1 B
5 A
how to create a pie chart with one sector per category found in the second colum and the summed up values for each ...

**0**

votes

**0**answers

15 views

### Position cells within view and copy functions/formulas from existing cells in Libre Calc?

I would like to keep a specific range of rows visible in Libre Calc. As new rows of data are entered, I would like the sheet/page to scroll, keeping the current row visible. As a comment made me ...

**0**

votes

**1**answer

83 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

89 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

123 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

26 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

**0**answers

18 views

### Show deleted cell values (Track changes in Libreoffice Calc)

I was looking for this feature for a while now:
If I turn tracking the changes on and delete the value in a cell it just highlights the border around the cell in red, meaning it has changed. Is it ...

**0**

votes

**1**answer

47 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

22 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

48 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

35 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

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

**1**

vote

**4**answers

743 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

128 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

33 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

291 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

16 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

**1**answer

65 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

30 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

46 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

74 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

36 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

74 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

88 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

134 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

29 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

43 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

37 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

66 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

71 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

56 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

53 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

90 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

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

32 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

212 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

55 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

516 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

54 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

599 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

67 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

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