Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

0
votes
1answer
14 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 ...
-2
votes
0answers
14 views

import of google-contacts is ot possible - which separator are needed [on hold]

import of google-contacts is ot possible - which separator are needed how does google use separators - and how can i import a csv-dataset i need a easy way to import data from calc - that is a ...
0
votes
1answer
20 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
1answer
593 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
1answer
14 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
2answers
691 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
1answer
59 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 ...
7
votes
3answers
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
2answers
1k 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
1answer
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
0answers
8 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
1answer
34 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
0answers
24 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
2answers
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
1answer
21 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
1answer
31 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
1answer
104 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
1answer
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
2answers
25 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
1answer
24 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
2answers
12k 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
2answers
2k 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
1answer
850 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
0answers
578 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
0answers
16 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
0answers
22 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
1answer
42 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
3answers
180 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
2answers
176 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
2answers
951 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
1answer
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
4answers
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
2answers
163 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
1answer
85 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
1answer
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
1answer
155 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
2answers
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)) ...
2
votes
1answer
515 views

LibreOffice Calc - apply style to neighbouring cells

I have a column with data and I applied a style to it with the help of Conditional Formatting. I now wish to apply the outcome of that style to the neighbouring column that holds other data. Here is ...
3
votes
2answers
50 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
0answers
29 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
0answers
81 views

Selecting multiple values from a list into a cell

One of my columns is named "Version Tested". It contains all the versions tested for a particular tool. Now for a given tool X, I want to select more than one version which has been tested. Using ...
0
votes
0answers
60 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 ...
15
votes
1answer
12k views

What is in LibreOffice Calc the equivalent to Excel's “Paste Special > Value”?

In my professional life, I'm limited to use Microsoft Office. In Excel there is an option ows to render the result of a formula to be pasted as a "fixed value". For instance A1 = "I",B1 = "ride", C1 ...
0
votes
0answers
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 ...
1
vote
1answer
27 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
5answers
2k views

How to convert text to numbers for all cells in a spreadsheet?

I have a spreadsheet containing values that are entered as text by having an apostrophe ' in front. I tried the search and replace function, but for some reason it couldn't find those cells containing ...
0
votes
1answer
94 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
1answer
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 | ...
1
vote
1answer
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
0answers
256 views

How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells. i.e. the conditional format changes the background colour of some cells if they are negative. I ...