Questions tagged [libreoffice-calc]
Spreadsheet component of the LibreOffice suite
700
questions
2
votes
0
answers
16
views
How to scroll down in cell with a lot of text
I have a dataset with extremely long text data (and many newlines).
I've created a mock-up here:
You can see that there's a 5th Lorem Ipsum paragraph at the top, but I can't seem to get it to display ...
- 429
0
votes
0
answers
35
views
How to handle associated column values in LibreOffice calc?
I have a column that is validated with a list from another sheet. I want the column to the right of that column to populate with a value associated with the value from the first column. That is, when ...
1
vote
1
answer
135
views
Calculate best possible lineup from a team of players using performance ratings in Libreoffice/Excel
I'm stuck at what I suppose is an optimization problem.
Here my spreadsheet is showing two tables, the one at the top displays the performance ratings of various players at different football ...
- 13
0
votes
1
answer
42
views
Nested If statement. working in two sheets
its been a long time since I used Excell so my thinking may be complety wrong, but this wont work. Am I using the wrong Syntax for LibreOffice Calc
=IF(M3>$'Shipping Profile'.$A$3,then $'Shipping ...
0
votes
0
answers
57
views
Function in LibreOffice Calc
How do I find the number of factors of a positive integer in LibreOffice Calc? For example, suppose the integers are in A-cells and I want to print the number of factors of them in the B-cells.
- 101
1
vote
2
answers
54
views
How to wrap a series of numbers into a table of selected cells? in LibreOffice Calc
In LibreOffice Calc, how to wrap a series of numbers into a table of selected cells so that they stop at the end of the highlighted row, the right side of the table where it ends and have it continue ...
- 11
-3
votes
1
answer
59
views
Viewing Photos in a LibreOffice Calc file
I have inserted an image into a cell in a LibreOffice Calc file.
What do I have to do to be able to click on that image to view it in original size, and then the same for images in other cells?
- 1
0
votes
0
answers
58
views
How Can LibreOffice Convert Milliseconds To Timestamp Or DateTime?
In LibreOffice I had converted timestamps into milliseconds because I didn't find a built-in way to naturally handle a standard time format. But now for real people, I want to convert that time easily ...
- 103
0
votes
0
answers
10
views
How do I put the series labels overlaying the graphics series lines in Libreoffice Calc?
As they say, a picture is worth a thousand words... so here goes:
I want the series labels of my Libreoffice Calc spreadsheet to be like this:
Instead of this
Is this possible ? How do I do it ? I'...
- 167
0
votes
1
answer
85
views
How to work with HEX strings without native decimal/scientific interpretation?
I am working with hexadecimal values in a workbook, and aside from having the numeric values taken as decimal numbers, some hex numbers are being perceived as scientific notation in for example the ...
- 708
0
votes
2
answers
36
views
Issues changing CSV column type to date in LibreOffice
I encountered some trouble changing cell types in LibreOffice Calc from text to dates.
This happens when I import a CSV file. The import into rows and columns works fine. However, the dates are not ...
0
votes
0
answers
13
views
Editing Calc formula, when clicking cell, it does not enter cell reference
When I am writing a formula, I can type =A1+A2 and it works, but if I type = and then click on A1, the system does not put A1 in the formula bar. It just enters A1 in edit mode.
- 1
0
votes
0
answers
54
views
Is it possible to Copy as CSV from Libreoffice Calc sheet?
Libreoffice Calc allows to "Save as" CSV, but it requires multiple steps to obtain the data on clipboard, if the objective is to paste somewhere e.g. an LLM prompt.
Is there a quick way to ...
- 51
1
vote
0
answers
22
views
Libreoffice Scatter Plot - Gradient Fill Points
I'm using LibreOffice Calc and I'm trying to create a scatter plot with some data that I have.
This is what my graph looks like so far
I want to color the individual points by a gradient of their ...
1
vote
0
answers
23
views
How to change font of all conditional styles?
I want to change a spreadsheet's font. The conditional styles are still using the old font. There are too many to change manually one at a time. What's a practical way to update all the conditional ...
- 21
1
vote
1
answer
44
views
change language for pdf export in libreoffice calc
This must surely be obscure but I'd really like to fix it for an important document.
My system language is in one European language. Still, I managed to change all the languages for a Libreoffice Calc ...
- 175
2
votes
2
answers
74
views
Libreoffice transforms formula from range reference to table reference
I have a weird problem
Given the following libreoffice calc sheet:
a defined range "myrange" with the contents:
One
Two
Three
1
2
3
And a formula field:
=myrange[Two]
The result is 2
Now:...
- 242
2
votes
1
answer
162
views
Can't find Developer Tools in LibreOffice Calc
I recently read that the Development Tools is available in LibreOffice since 7.2, but I can't find it in my 7.5.9.2 (and then updated to 7.6.4.1) installation... It should be found under menu item for ...
- 131
1
vote
1
answer
25
views
Libreoffice calc: countifs over two rows
I have a table with dates in column A and strings in column B. I'd like the countifs function to count the cells where the weekday in column A is thursday and the corresponding cell in column B is ...
- 13
1
vote
3
answers
138
views
Convert an Excel table from one structure to the other
Original table
I have this table in Excel or LibreOffice.
Unit number
Type
Name
1
Object
Top
1
Object
Bottom
1
Object
Left
1
Object
Right
1
Object
Back
1
Object
Front
1
Property
Right-Fixed
1
...
- 270
2
votes
0
answers
44
views
How to update LibreOffice WEBSERVICE data automatically
I know I can force a manual update from Edit/Links.
Is it possible to have LibreOffice update this data automatically, for example once per day?
- 159
1
vote
1
answer
76
views
How to clone conditional formatting to another column?
I want to highlight values that are in the bottom 5th percentile in each column, for about 60 columns. I set this conditional formatting for the first column, but I see no way to apply this to every ...
- 163
0
votes
1
answer
98
views
How to convert % to decimal in excel/ Libre Office Calc formula?
I am having trouble to calculate the compounding trade lot size as it should multiply the keyed value such as 0.01 x the % gain entered. But if I put 100% gain and do starting value*100% value it ...
4
votes
3
answers
894
views
How to apply the same formula to a whole column?
How to subtracts the same values in the next cell to a whole column? Here is an example so you can understand my low English level.
Column 1 Column 2 Result
1000 900 100
...
- 111
1
vote
1
answer
659
views
Cell references for Conditional Formatting in LibreOffice Calc using the "Formula is" condition
In Libreoffice Calc 7.6, I want to have cells that do not contain a number to have a certain style ("error"). If the value has a comma instead of a point as the decimal separator: 1.5 vs 1,5 ...
- 551
1
vote
1
answer
435
views
LibreOffice Calc Basic search and replace macro
I am attempting to create a LibreOffice Basic macro for use in Calc that when invoked in an open spreadsheet file will open a second spreadsheet file that contains two ranges, (the first range ...
- 173
2
votes
3
answers
219
views
How to apply the same percentage to a whole column?
In LibreOffice Calc, I have data in one column. I need to calculate the percentage of a whole column of data without filling the percentage column one by one, without writing the formula one by one ...
- 111
0
votes
0
answers
87
views
How do I automatically generate colour styles in LibreOffice Calc using VBA?
I want to be able to run a macro to automatically create a set of named styles that change the cell background colour.
I tried this, but it doesn't work:
REM ***** BASIC *****
Sub ...
- 251
1
vote
1
answer
1k
views
How do I apply the MATCH() function only to a filtered range? Or is there another way to do that?
I have a filtered list of values. Let's say I have the numbers from 0 to 5, and 2 is filtered out:
A (with auto-filter against "2")
0
1
3
4
5
Now I want to find out whether one of the ...
- 2,431
1
vote
1
answer
81
views
Creating an array with a reduced set of elements matching existence of criterion in multiple columns
I want to do this on a much larger problem, but chose the simplest example for a proof of concept.
At the bottom is a CSV of my file (with formulas).
I have data for points (vertices of tetrahedron):
...
- 312
0
votes
0
answers
103
views
How to make bold appear less bold and the font lighter in LibreOffice?
I am transitioning from Google Sheets to LibreOffice because of privacy concerns and other reasons.
Now, I compare the design of a sheet in LibreOffice Cal with Google Sheets (same sheet opened):
1. ...
- 2,694
1
vote
1
answer
556
views
How to change the insert line break shortcut in LibreOffice Calc from Ctrl + Enter to Alt+ Enter?
To stay within the same cell and go to the next line, in Microsoft Excel and Google Sheets the shortcut is Alt + Enter.
In Libre Calc, however, it is CTRL + Enter.
I would like to stick with the ...
- 2,694
1
vote
1
answer
536
views
How to test if a cell matches given REGEX in LibreOffice Calc?
I have a cell, and I want to test if it passes a specific pattern: 3-4 alphanumeric, minus, 3-4 alphanumeric, minus, 4 digits, minus, 1 or more digits.
In other words, I want "TEST-TST-1234-33&...
- 216
0
votes
1
answer
141
views
Can libreoffice differentiate between .txt and .csv files?
Can libreoffice differentiate between .txt and .csv files?
I have two file's that have a mime type of 'plain text document' text/plain detected.
One file ends in .txt and the other ends in .csv.
Is it ...
- 101
2
votes
1
answer
172
views
Move down one cell with keyboard in Libreoffice Calc
The cursor is on Cell E2. I would like it to be on Cell E3.
Pressing ▼ scrolls the sheet.
Pressing Ctrl+▼ jumps to oblivion.
Shift+▼ selects.
Surely there's a way to do this?
- 3,923
0
votes
0
answers
330
views
How to map value of a cell to another value, depending what it "contains" ? (not a full match)
I have a worksheet C1 with the following data:
A
B
sometext 21 oscar ABC john
sometext 22 foo XXX smith
sometext 23 lucy YYY pedro
sometext 24 bar ZZZ edward
In another worksheet C2, the ...
- 931
0
votes
0
answers
25
views
I want to conditional formatting A1:A25 cells based on another two cell values eg;if cell B1 and C1 contains word "off"then the cells become red
if cells B1 and C1 contains word off then formatted cell become red in background colour and any of the two cells contains other value then green colour
- 1
0
votes
1
answer
59
views
How can be column incluted into rows?
There is the table in MS Excel or LO Calc.
How can be it converted to this view of the table?
The transformation using links (within the same table) to the initial version is allowed.
- 101
1
vote
1
answer
104
views
LibreOffice Calc clears clipboard
My use case is: I am running Chrome and filling in a web form. For one text box that I need to fill, I need to get the data from a spreadsheet. So I open the spreadsheet using LibreOffice Calc, copy ...
- 307
1
vote
0
answers
160
views
Easiest way to copy many conditional formats
What is the easiest way to copy many conditional formats without Calc collapsing column-based formats down to a multi-range format?
- 21
0
votes
0
answers
78
views
Copying conditional formats
I have conditional formats with ranges like this:
A:A
B:B
C:C
D:D
E:E
F:F
But when I duplicate the sheet, the new sheet has ranges like this:
A:A
B:B,C:E
F:F
Is there a way to fix or work around ...
- 21
-1
votes
1
answer
360
views
Find latest non-blank value no later than given date - and quickly
I'm really struggling to find a solution to this deceptively simple problem. I've even enlisted ChatGPT's help, but none of its solutions have worked. Maybe you can prove you're smarter than a machine ...
- 31
1
vote
0
answers
116
views
How to add a chart that would display 2 values from single row as {X,Y}
In LibreOffice, I have a table like this
Element,X,Y
name1,0,5
name2,1,2
name3,5,7
...
so, I would like a chart that displays dots at {0,5}, {1,2}, {5,7}
as I am trying to use Scatter (XY), all the ...
- 161
0
votes
1
answer
510
views
How to convert time to seconds
I have csv file with cells that have values like 0:20.555 and 12:05.001. The format is mm:ss.sss where mm is minutes,ss.sss is seconds with thousandths of seconds. It is a duration, not time of day. A ...
0
votes
1
answer
130
views
Get values on cells from first column that match a string on a range of cells [closed]
Hi, I have the following table:
Name
Sport 1
Sport 2
Sport 3
Mark
basketball
baseball
Luke
skating
John
baseball
football
soccer
Peter
soccer
basketball
I want to know how to get the names that ...
- 195
0
votes
0
answers
46
views
Libreoffice Calc 5.1.6.2 - How to match several rows using a single criteria?
This is my first post and I'm very much a novice at spreadsheets, so please be gentle! I'm also conscious that I don't quite know enough to know how to ask the question properly, so please do excuse.
...
- 61
3
votes
1
answer
379
views
Spreadsheet: View the entire (long) text of selected cell
I am not using spreadsheets very often and I am not sure whether this is a feature of common software (I'm using libreoffice calc).
I have a spreadsheet in which 50 to 200 word texts are entered in ...
- 1,070
-1
votes
1
answer
83
views
LibreOffice Calc - view the zoom button
I am using the Spanish versión but it should be the same thing.
I need tot see the Zoom button on the top of the window - under the menu - I don't know how that area is called.
- 765
0
votes
1
answer
128
views
How to redo filtering with LibreOffice Calc?
In Microsoft Excel, I can reapply a filter with these commands:
I put this command via shortcut in the top bar and can redo the filtering with just one click:
How could I do the same with ...
- 111
0
votes
0
answers
66
views
Create Binary Search in LibreOffice Calc?
Does anybody know of a way to organize a binary search in LibreOffice Calc?
To take a simple example, say I have column A with the numbers 1-16 listed, sorted in ascending order: (1,2,3,...); and ...
