Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

0
votes
0answers
53 views

LibreOffice (Calc) | Search & Replace | unable to look for 'comments'

CTRL+F enables the SearchBar (quick search) CTRL+H brings up Find & Replace dialog (more options) I'm unable to find a way to look for comments that are within with a cell (with ability to ...
1
vote
1answer
21 views

LIBREOFFICE calc file can't be open by different computer

I have a .ods file on my USB drive and want to edit it at home and at work. But all documents I create are "stuck" to the computer I created it on. I have tried to save the file with password for ...
0
votes
0answers
64 views

Libre Office Calc Issue: Summarizing columns with too many rows

I'm writing a script that reads data taken from a database. THe data is modified (de-duplicated) and put into a .csv file I have a problem as I want to find the sum of a particular column in excel. ...
1
vote
1answer
50 views

Spreadsheet formula to display most recent prices

I have created a LibreOffice Calc spreadsheet with columns A-C. In column A, I have a list of parts purchased (in chronologically ascending order). In column B, I have a list of corresponding ...
2
votes
3answers
141 views

How do you properly align average and standard deviation outputs in a spreadsheet?

I am not sure of the best way of formulating this question right now so I will use an example using random numbers. I start with values assigned to IDs, so that each ID can be n=1, n=2, ... etc ID ...
0
votes
1answer
114 views

Select data from different sheets in LibreOffice Calc

I have three sheets 1,2 and 3. There are three columns on sheet 2 and 3: name (A), surname (B), age(C). How can I select men whose age is >=30 from sheet 2 and 3, order them by age and show the result ...
0
votes
1answer
20 views

interrogating Bank data - in OpenOffice 4.1.1

I would be very grateful for any assistance with the following... I have a worksheet of raw bank transaction data...I want to interrogate the data to display payments to the same source over the ...
0
votes
1answer
17 views

Edit some Google Files on the desktop without installing Google Drive

I would like to edit a Google Tabel on my Desktop with out installing the Drive Client (I really only need this file) and with out using the browser. What can I do? Is there a plug-in for excle or ...
0
votes
1answer
39 views

Reference a cell with input from another cell

The final objective is to reference the last populated (higher row number) cell from a column (its value). I found a function that returns the row number of the last populated (higher row number) ...
0
votes
0answers
15 views

Currency aware summation in LibreOffice Calc

I have a spreadsheet with columns of the form: Date A B Total 01/01/15 $100 £200 xxx 08/01/15 $110 £195 xxx 15/01/15 $115 £205 xxx where A and B are ...
0
votes
1answer
22 views

Shift down cells on enter in Libreoffice Calc?

In Libreoffice Calc, how does one shift down cells on press Enter, and if I want to go down cells by pressing ↓? Is there any setting that controls this?
1
vote
1answer
27 views

How to rearrange columns in libreoffice calc as per column totals?

I have the final row as total which has the sum for each column.I wish to rearrange the columns such that the columns from left to right will be arranged descendingly as per column total.
1
vote
1answer
126 views

Is there a way to save a filter in libreoffice calc?

I keep switching between unfiltering and filtering on these three checkboxes Is there a way I can save this filter so that I could click only once and have my sheet filtered instead of clicking on ...
0
votes
0answers
28 views

How to show the total number of results in a “Find All” search in LibreOffice Calc?

LibreOffice Calc provides the useful search function to "Find All" instances for a search term. It outputs a result window which looks as follows: What I miss is the total number of found search ...
0
votes
2answers
65 views

Entering notes into LibreOffice Calc and OpenOffice Calc

In LibreOffice Calc and OpenOffice Calc, there is often a need to quickly enter several rows of text that will not be part of any calculation area. This is often done to write notes relevant to the ...
1
vote
1answer
4k views

Switch Calc (or Excel) formulas to English language

I'm not using an American/English version of Calc/Excel so, every time I want to post here (or on other "web-places") or when I want to try formulas posted by others I need to "translate" them somehow ...
0
votes
0answers
34 views

LibreOffice Calc Automatic Detection of Duplicates

Is there any way you can automatically detect a duplicate of any previous cell, the very moment you entered the text? like when you input the word "data" it will be automatically highlighted because ...
3
votes
2answers
54 views

Libreoffice cell/axis format: Insert a newline?

The goal is to plot a value against date/time in the format DD/MM HH:MM but to have the time and date on separate lines. I'm currently using source formatting but this isn't necessary. A couple of ...
1
vote
1answer
23 views

How can I disable dates in the Calc?

I see that all spreadsheets are urgent to recognize your 13.12 as a date. Particularly, I am struggling with Calc, who is said to use English language to import dots as decimal separators. But, ...
0
votes
1answer
92 views

Dynamic array/matrix generating within formula

The following formula: =TRIM(A1:A20) would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...
0
votes
1answer
28 views

How do I look up data from another table?

In this fictional example, I have two tables; one with employee information and pay rate: The second with dates and the corresponding number of hours the employee has worked on that day (note that ...
1
vote
0answers
54 views

LibreOffice Calc equivalent to Excel “Custom Views” feature

Custom Views are an Excel feature that comes very handy when, for example, you want to save a filtered version of a worksheet and then pass to another vision of the same worksheet with different set ...
1
vote
1answer
625 views

LibreOffice Calc equivalent to Excel “Evaluate Formula” feature

Excel has an "Evaluate Formula" feature (it's located in the Excel Formulas ribbon among the Formula Auditing group, as shown in the picture below). Is there an equivalent to that Excel feature in ...
0
votes
2answers
83 views

How to give duration in excel or libre calc?

I have duration data like 5:23, etc... in m:ss format. I already typed the data, but I cannot create a diagram, because none of the mentioned excels understand it. If I change the data type to user ...
2
votes
2answers
138 views

A Conditional Format to change the background color but keep the Date format

I have and OpenOffice Calc document that has a "table" with Text, Numeric and Date columns. I have a conditional format making every odd row have a different background color so it will be easier to ...
0
votes
1answer
75 views

How to undo autocorrect in libreoffice calc

I would like to prevent libreoffice calc to make autocorrecion in some cases in some cell. For example, sometimes libreoffice decides that some text I enter is a hyperlink and it formats the cell as a ...
1
vote
0answers
43 views

Switch off autocorrect in libreoffice calc for specific document, column, cell

There is a way to completely turn off autocorrect in libreoffice calc. However, I would prefer turn off autocorrect in less scale: document, worksheet, column/row, cell. How it can be obtained?
0
votes
1answer
57 views

How to copy tab-contained text from Excel without quotes?

I've noticed that a cell that contains a tab is copied with quotes embedded. I'm looking for copying such kind of a cell without the quotes. Illustrative Example I've 5 cells; A1, A2, A3, A4, and ...
1
vote
0answers
45 views

How can I remove " from my string..?

My string has " and I dont want " then what to do..? My string is like : "Hey My name is Viper I want remove " from string How
0
votes
1answer
186 views

Why does this LibreOffice spreadsheet display a number as “TRUE”

I copied the problem into this sample spreadsheet Cells A1 and A2 should both display numbers, but one displays "TRUE" instead of a number. If I go to "clear direct formatting" on A2, it will display ...
0
votes
0answers
161 views

oocalc unknown node setting file

When I run oocalc, I get a warning: W: Unknown node under /registry/extlang: deprecated This looks like a Python warning to me. However, I do not see the warning when running Python scripts. My ...
0
votes
1answer
246 views

Compare/check multiple columns in multiple sheets and find the unmatched values

I have two sheets in libreoffice calc, both sheets have same columns, i need to compare these sheets column values(multiple columns) and need to check rows are equal, if not equal then need to ...
1
vote
1answer
187 views

LibreOffice: Autocomplete dates with month and year, no day

I'm using Libreoffice 4.4 and I want to drag down a column in format Month Year. For example: May 2015. I do not need days. I tried to drag down after doing this schema for 3 cells but it doesn't ...
0
votes
1answer
78 views

How to add double quotes around data in tables using Calc

I am trying to load my data into a CRM, but am having a lot of trouble. The data simply wont load at the same time, but all the files work fine individually. Support advised that I make sure I'm using ...
1
vote
1answer
559 views

Search for specific key word and replace entire cell contents with something else in LibreOffice Calc

I would like to be able to search for a specific word, lets say "House" in a calc sheet, and then replace the entire cell content with something else like "Car". It is important to notice, that the ...
1
vote
1answer
51 views

Comment visibility in open/libre office spreadsheets

I'm getting moderately perturbed by how difficult it is to see whether there's a comment in a cell in LibreOffice. As it stands a tiny box appears in the upper right-hand corner in the cell. Does ...
1
vote
1answer
52 views

Libreoffice: Import columns of spreadsheet as fields

I have a LibreOffice Calc spreadsheet with Columns A, B, C. I would like to create a document in LibreOffice Writer with format: A1 B1 C1 A2 B2 C2...
2
votes
1answer
66 views

LibreOffice Calc/Spreadsheet – Delete unusual characters from cells so that I can export a valid CSV file

I received a spreadsheet for work and it has cells that are full of line breaks and excessive whitespace. Unfortunately, I need to export the spreadsheet to CSV format and these two are breaking the ...
1
vote
3answers
261 views

How to write a function once and have it update in many cells?

It often occurs that I write a function in one cell and then copy it into many cells. The copies then refer to their own correct rows. But if I change the function in the first cell the copies are ...
0
votes
0answers
134 views

Libreoffice-calc: Apply a macro to a csv-file on the command-line

With Libreoffice/Openoffice it is rather straightforward to do these steps: Import csv file Copy macro from another file Run macro Remove macro Save file in csv and xls formats (no macros, just ...
1
vote
1answer
842 views

Libreoffice-Calc: Count number of rows and ouput total below list

How to write a macro that counts the number of columns that contain data and prints the result at the bottom of the list? For example, if the data is: |1|2|3| |a| | | |1| |3| The final table ...
0
votes
1answer
1k views

Libreoffice: How to count words and characters in cells

I would like to count the words and characters of a range of cells, excluding all cells that start with "§". The text is arbitrary UTF8-text, for example: "my test string" = 3 words, 14 characters ...
1
vote
0answers
73 views

How to reference cells by name in Libreoffice 4.2

In the Libreoffice version that came with Ubuntu 12.04, I could select a column, name it, and then construct a formula that referenced this name. e.g. Say I had two columns A=Price and B=Quantity. If ...
0
votes
0answers
85 views

Concatenate a reversed range in Libre Office

I'm kind of new with programming, but I have to make an excel document, that I can use interactively because the output of one of my programs doesn't fit with the input of the others... I have lot of ...
3
votes
1answer
244 views

How to color rows based on two cell values in Open/Libre Office?

I am trying to apply conditional formatting to an entire row based on two cell values. The style is to be applied only if both cells have a particular value. I tried applying formula of the form: ...
0
votes
1answer
301 views

How to lock a cell for editing in LibreOffice Calc?

How can I lock a cell, to prevent edits to its value for the foreseeable future?
1
vote
1answer
53 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
0answers
13 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 ...
1
vote
1answer
402 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
1answer
46 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 ...