Questions tagged [libreoffice-calc]
Spreadsheet component of the LibreOffice suite
691
questions
2
votes
2
answers
5k
views
Why does LibreOffice Calc change CSV file Character Set?
I have a .csv file export (actually printed to csv file) made on a Windows 10 machine. Working in Libreoffice Calc on Archlinux, Why can Calc open a file using settings Character Set: Western Europe (...
CommunityBot
- 1
5
votes
3
answers
3k
views
Is there a way to convert 1K to 1000 and 1M to 1,000,000 In LibreOffice Calc
I need to sum a large data set, but the values are entered as K or M. Is there a way to change these convert these to numerical values? I was thinking of using find and replace to change M to 000000, ...
- 1
1
vote
2
answers
2k
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 ...
0
votes
1
answer
40
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 ...
- 9,337
0
votes
4
answers
1k
views
Excel: How to convert a column letter to the corresponding column number without using COLUMN() and INDIRECT()?
The standard solution seems to be =COLUMN(INDIRECT(letter&"1")) but there should be a better solution that avoids the volatile INDIRECT function.
Sample Column Letter
Corresponding ...
- 1
0
votes
2
answers
18
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 ...
- 29.7k
0
votes
0
answers
11
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
24
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
13
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
19
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
4k
views
How can I add duplicate rows to LibreOffice Calc to make there be one per day?
I have some data like this (it's stock market data)
29/07/2015 35972.82 32056.2 17751.39
30/07/2015 35961.86 32046.43 17745.98
31/07/2015 35848.13 31945.08 17689.86
03/08/2015 ...
CommunityBot
- 1
1
vote
1
answer
35
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
1
vote
2
answers
52
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:...
- 121
0
votes
1
answer
4k
views
Auto fit column value without increasing row height in LibreOffice
I m using Libre Office Calc on my Ubuntu 12.10 desktop and I need a help.
I am writing some data from other resource and save it for each row. I have only 2 columns which values are lengthy and rest ...
CommunityBot
- 1
12
votes
2
answers
5k
views
How to highlight the current row and column like a crosshair in Libreoffice?
How can I highlight the current row and column like a crosshair in Libreoffice as guide for the eye?
There seems to be an open feature request from 2011 and a very similar question for openoffice - ...
73
votes
4
answers
165k
views
How to color rows based on cell value in OpenOffice and LibreOffice
How do you automatically set the background color of an entire row based on the value of a specific column in that row in OpenOffice and LibreOffice?
There are similar questions for Excel, but this ...
- 159
5
votes
3
answers
11k
views
libreoffice what is a fast way to paste special > text and numbers only (aka paste special values)?
I copy and then paste values in excel quite a bit (when I use Excel). When I use LibreOffice you can do the same thing (see related question here) but it is oh so tedious!
It gets especially tedious ...
- 55.6k
3
votes
1
answer
2k
views
LibreOffice Calc: how to convert minutes into decimal hours
I have the value 0:15:00 h:mm:ss - I'd like to get the value 0,25 hours - how can this be done? I tried simply dividing 0:15:00 by 60 but that didn't return what's expected.
- 10.7k
8
votes
2
answers
5k
views
Creating a column of working days in LibreOffice Calc
In LibreOffice Calc, I would like to create a column of working days, like Date from below:
Date
Day of week
2012-01-30
Monday
2012-01-27
Friday
2012-01-26
Thursday
2012-01-25
Wednesday
2012-01-...
- 5,583
2
votes
1
answer
84
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 ...
- 121
1
vote
1
answer
19
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 ...
- 18.7k
1
vote
3
answers
131
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
...
- 8,748
5
votes
2
answers
5k
views
How do I select non-adjacent ranges in libreoffice calc?
I'd like to perform an operation on a set of non adjacent ranges in LibreOffice calc.
I can type: C4:GA4 into the "name" box in order to select a single range. Is there a way to select non adjacent ...
CommunityBot
- 1
3
votes
2
answers
990
views
Libreoffice, how to copy from Writer and pasting unformatted in Calc, by default?
I have the revision of a translated text, made by the client, I am the third party reviewer and have to fill up an .xls module with all the source texts, the translated one, and the amended by the ...
- 649
2
votes
0
answers
37
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?
CommunityBot
- 1
1
vote
1
answer
59
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 ...
- 1,890
0
votes
1
answer
88
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 ...
- 16
2
votes
1
answer
3k
views
Libre Office Calc paste as column
I want to copy data from the clipboard to a column on Libre Office Calc
This is my data example
48032, 50201/C, 50286, 50285, 50254, 50253, 50252, 50251, 50250, 50248, 50206/C, 50204/C, >50202/C, ...
- 3,519
1
vote
1
answer
376
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 ...
- 10.7k
4
votes
3
answers
673
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
...
- 101
2
votes
1
answer
2k
views
How to fill hundreds of rows with the same value in LibreOffice Calc?
How can I fill hundreds of rows with the same value without dragging?
I know the start row is A2 and the end row is A5000, and I want to fill them with 1.
I am looking for a function that looks like
...
- 55.6k
2
votes
3
answers
176
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 ...
- 4,362
1
vote
1
answer
314
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 ...
- 1,890
3
votes
2
answers
5k
views
How do I generate a ranking/sort cells automatically in LibreOffice Calc?
For example I have a table with a caption and some numerical data, such as:
Mary | Sue | Mike
-----------------
100 | 20 | 30
-----------------
I want to generate a ranking, i.e.
1. Mary
2. Mike
...
- 11
3
votes
2
answers
800
views
LibreOffice calc: create multiple chart series from column values
Sorry for the basic Calc question, but I'm very inexperienced with spreadsheet charts and need some help. I've tried searching for the answer but surprised that I can't find anything that answers what ...
- 202
1
vote
1
answer
741
views
How to 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,331
0
votes
0
answers
65
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
80
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):
...
- 302
0
votes
0
answers
78
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,684
1
vote
1
answer
314
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 ...
- 4,029
1
vote
1
answer
407
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&...
- 10.7k
0
votes
1
answer
136
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
1
vote
1
answer
127
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?
- 271
0
votes
0
answers
173
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 ...
- 921
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
52
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.
- 8,748
2
votes
1
answer
795
views
LibreOffice calc: how to filter one or several tags in a multiple tags column
I have libreoffice-calc file like this:
books author tags
Book 1 Author 1 ghost; romance; france
Book 2 Author 2 romance; mystery
Book 3 Author 3 biographie; england
Book 4 Author ...
- 183
1
vote
1
answer
93
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 ...
- 481k
-1
votes
1
answer
317
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
2
answers
755
views
LibreOffice Calc: How to make AutoFilter changes permanent?
When I apply an AutoFilter to my LibreOffice Calc spreadsheet, it's natural to have some rows removed, like in this screenshot. However, it's been clunky dealing with it, and I don't like this ...
- 602