Questions tagged [libreoffice-calc]

Spreadsheet component of the LibreOffice suite

Filter by
Sorted by
Tagged with
4 votes
3 answers
406 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 ...
Feuergeist's user avatar
1 vote
1 answer
22 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 ...
mcnesium's user avatar
  • 549
0 votes
1 answer
78 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 ...
JCB's user avatar
  • 133
0 votes
3 answers
116 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 ...
Feuergeist's user avatar
0 votes
0 answers
33 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 ...
skeetastax's user avatar
1 vote
1 answer
65 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 ...
questionto42's user avatar
  • 1,967
0 votes
1 answer
75 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): ...
Eric Marceau's user avatar
0 votes
0 answers
33 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. ...
Avatar's user avatar
  • 2,603
1 vote
1 answer
72 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 ...
Avatar's user avatar
  • 2,603
1 vote
1 answer
159 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&...
v010dya's user avatar
  • 216
0 votes
1 answer
103 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 ...
turtle's user avatar
  • 101
1 vote
1 answer
48 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?
chx's user avatar
  • 3,783
0 votes
0 answers
47 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 ...
tigrou's user avatar
  • 911
0 votes
0 answers
18 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
shaji 's user avatar
0 votes
1 answer
48 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.
Imyaf's user avatar
  • 1
1 vote
1 answer
42 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 ...
k314159's user avatar
  • 287
1 vote
0 answers
56 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?
latitude's user avatar
0 votes
0 answers
38 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 ...
latitude's user avatar
-1 votes
1 answer
173 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 ...
Dion's user avatar
  • 31
1 vote
0 answers
47 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 ...
62mkv's user avatar
  • 161
0 votes
1 answer
171 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 ...
user1813005's user avatar
0 votes
1 answer
75 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 ...
Ventolinmono's user avatar
0 votes
0 answers
39 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. ...
Beaver's user avatar
  • 31
2 votes
1 answer
79 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 ...
Joce's user avatar
  • 1,030
-1 votes
1 answer
50 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.
Joe Jobs's user avatar
  • 723
0 votes
1 answer
58 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 ...
user7468395's user avatar
0 votes
0 answers
49 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 ...
Mychal Simonian's user avatar
0 votes
0 answers
172 views

Multiple Format Conditions with Conditional Formatting

I have conditional formatting applied to a column in LibreOffice Calc. There are two conditions. The first bolds the text and the second sets the background color. If the first condition is true the ...
gmvar's user avatar
  • 1
0 votes
0 answers
42 views

Sum of matrix rows

With these inputs to MMULT: I'd like to calculate the sum of the rows. This would work if COLUMN accepted an array: =MMULT(--MMULT(K$4:K$9,TRANSPOSE($L4:$L6)),TRANSPOSE((COLUMN(MMULT(K$4:K$9,...
Hedgehog's user avatar
  • 101
0 votes
1 answer
46 views

How to set 2 criteria in LibreOffice

What I want to do is print column C to column H if A=F and B=G LibreOffice:
SRTM's user avatar
  • 5
0 votes
1 answer
292 views

Cannot execute LibreOffice macro on Docker Linux

I want to execute a macro file on Linux created by Docker with the following docker file. from ubuntu:22.04 run apt-get update \ && apt-get -y install --no-install-recommends \ default-jre ...
Issei Morita's user avatar
0 votes
1 answer
41 views

Excel/LibreOffice Calc - Setting a value dependent on another value as that second value rises at a uniform rate

Essentially the value in C24 is dependent on the value in C18. The value in C24 should be 33 if the value in C18 is 2230 or less. The value in C24 should be 39.5 if the value in C18 is 2950 exactly. ...
ShadowLine's user avatar
1 vote
2 answers
515 views

Subtract values of two cells relative to a cell, without directly referencing them

I want to create the following table: Value Difference to previous row 10 0 20 10 80 60 I would like the formula to be generic, without referencing any cells directly. The following formula works:...
Mitch's user avatar
  • 139
1 vote
0 answers
30 views

How to copy text styling in LibreOffic Calc

I'm using LibreOffice to edit a spreadsheet originally created using Excel. The text in some of the cells is styled, and I'd like to copy the formatting -- but not the contents -- from one cell to ...
Mark's user avatar
  • 1,511
0 votes
2 answers
1k views

How do you move cells in LibreOffice Calc

I want to move cells in a LibreOffice calc document. For example, suppose I have: a b c d I want to use the mouse or keyboard to select cell 'b' and get to: a c b d I've tried various key ...
joachim's user avatar
  • 788
1 vote
2 answers
1k views

Subtotal and Countif combined on filtered data

Is it possible to do a calculation on filtered data? In this case filter the data by year and calculate the "No of Bookings made less than x days before check-in" where x = D4 e.g. 100 I'm ...
Tmc_m's user avatar
  • 11
1 vote
0 answers
61 views

How to draw empty cell values in LibreOffice Calc

my problem is the following (didn't find info how to do it in the official documentation) - recorded ~108k ping results to LibreOffice table with columns DATE (letter A), LATENCY (letter B), STATUS (...
1000Gbps's user avatar
  • 177
0 votes
0 answers
849 views

Libre office, automatically color cell based on value

I’m trying to make a planning grid with various tasks and I would like a task to be colorized to easily spot it anywhere else. Each different task should get a different colour. I can’t find a way to ...
djo0012's user avatar
  • 101
0 votes
0 answers
48 views

how to avoid to delete referenced cells (delete row, delete column delete sheet)

Referenced cells must not be deleted without warning as this operation destroys the sheet. The resulting ref error is often discovered much later - sometimes to late to undo the transaction. Any idea ...
ferdiga's user avatar
1 vote
0 answers
139 views

Limit default column and row number in LibreOffice Calc

Every time I open a file with Calc, no matter how many columns it has, by default I get for free a ludicrous amount of columns and rows (eg: till col AML and row 100.000) I can't find the ...
ranemirusG's user avatar
1 vote
1 answer
337 views

How can I add a new or custom toolbar in LibreOffice?

There is the so-called "Quick Access toolbar" (link to its Microsoft's Excel help page for it) in Microsoft Office products available. It consists of little icons for the included functions. ...
henry's user avatar
  • 346
0 votes
0 answers
242 views

Alternative for ADDRESS function that returns an array

I primarily use LibreOffice Calc instead of Excel. When creating my spreadsheet, I relied upon the fact that the ADDRESS function was able to take a cell range and return an array of cell reference ...
Nicolas's user avatar
  • 101
1 vote
2 answers
512 views

Libre Office "Create" is not enabled for Python Macro

Libreoffice 7.4.2.3 (x86) on Win7 Home (x64). That's the current version, but problem exists for any version. New (empty) spreadsheet. I'd like to create a new Python macro. From Tools|Macros|Organise ...
user165568's user avatar
0 votes
1 answer
412 views

How can I copy the plain text of equation in libreoffice calc

I made a trend line and its equation in libreoffice calc: Now, I want to copy the equation's plan text, but the copy function copies the equation as an object: And then I can only paste it inside ...
Christianidis Vasilis's user avatar
0 votes
1 answer
303 views

How to highlight all cells that contain formulas

In LibreOffice Calc, I want to be able to quickly distinguish between cells that contain input data and formulas. Enabling "View → Value Highlighting" changes the font color in cells such ...
Flux's user avatar
  • 209
0 votes
1 answer
111 views

Creating a sortable monthly "period" formula in libreoffice

Every month I export data into a CSV format from a 3rd party site. The data is rows of daily data, the first column being a date field. Each month I have to manually add a new "Month Period" ...
Andrew Johns's user avatar
0 votes
1 answer
371 views

Handle physical units in LibreOffice Calc

I tend to use LibreOffice Calc most for keeping track of some sort of (measurement) data (e.g. from performed experiments). In almost all cases this data will be numbers expressing a quantity in a ...
Raven's user avatar
  • 101
3 votes
2 answers
352 views

How can I convert time values with a letter suffix like (30m, 2h, 30s) into an actual time format?

I have decimal values followed by a letter to indicate the time unit (hours/minutes) values like the following: 60m 1.5h 15m 20h How can I convert these to a numeric value of minutes or time value? ...
Chris Stryczynski's user avatar
2 votes
2 answers
1k views

How do I address a column in a named cell range

I have a named multi-column, labeled cell range. How do I access values from one of the columns? Here's an example of the range: Now, Calc will help me get the entire cell-range: But what do I do to ...
Zlatko's user avatar
  • 266
0 votes
1 answer
184 views

Libre Office Calc List every wednesday

I'm trying to list the date every Wednesday in a column for a budget sheet. I think I need the DATE function, but I don't know how to find the "day" value of Wednesdays in an arbitrary month....
christopher ogle's user avatar

1
2 3 4 5
14