Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [libreoffice-calc]

Spreadsheet component of the LibreOffice suite

0
votes
0answers
17 views

Can I limit a range of cells to a specific set of values?

I'm creating a table in LibreOffice calc. One of my column is actually a choice among several fixed categories - let's say it's a choice of one of several possible strings. Can I constrain the set of ...
1
vote
1answer
12 views

LO Calc won't strip quotation marks from text

Since quotation marks serve a special function, treating them as a character requires workarounds. There are a number of standard methods, such as doubling the quotation mark or using CHAR(32), its ...
1
vote
1answer
26 views

Libreoffice Calc, macro set Text Alignment Indent, or property documentation?

In Libreoffice Calc it is possible to use macro's to set the text properties of each cell, to format the layout and font. I was able to find some property names online, in code examples and forums. ...
0
votes
1answer
42 views

Sort spreedsheet according to groups and dates. Also leave blanks when no group size doesn't match

I have a spreadsheet that looks like: There are two sets of columns, let left one and right one are supposed to be ideally be the same (row by row), but they aren't and I need to filter them such ...
0
votes
0answers
57 views

Drag-to-duplicate row, preserving data underneath, in Calc

I have a spreadsheet in LibreOffice Calc that I use for invoices. In this is a row that lists task and cost. When I select the cells in this row, I can drag the lower-right corner and duplicate the ...
-3
votes
2answers
84 views

Excel: how to count and group rows

I have an Excel file with Column A and Column B. Both have people names (Elizabeth, John, Michael and Robert). I need to count each name of Column A and count each name of Column B. And then I need ...
0
votes
0answers
18 views

How to create readonly table from raw data

If I have a large table of data, essentially like a database, can I create another sheet or range that is a filtered subset of the full dataset? If so, will it stay up to date when the main table ...
1
vote
0answers
20 views

Size of dropdown / selection list in Libre Office Calc

With the data validation tool I made a list of categories that are shown in a selection list for easier data entry. This list is very small and I cannot just enlarge it with the mouse (see screenshot)....
1
vote
0answers
52 views

How to copy multi-line text from LibreOffice Calc WITH quotes

I've been searching and found this question... How to copy multi-line text from Excel without quotes? ...which is almost exactly the opposite problem I have. I am using LibreOffice Calc and I have ...
0
votes
1answer
23 views

Long Calc Sheet - subtotal at bottom of page, transfer to next page?

Assume i have a LibreOffice Calc sheet spanning multiple pages in printout. Is there a way to calculate a subtotal for each page and print it at the bottom of the respective page as well as at the top ...
1
vote
3answers
44 views

How do I format a number in an “=CELL” type of statement?

I have a spreadsheet where cell A1 is 1234.56789. In cell B1, I have ="$"&A1. This shows up in B1 as $1234.56789. How do I make it show up as $1,234.57 in B1?
3
votes
1answer
66 views

How do identify the source of a #NAME? error in Libreoffice Calc?

My LibreOffice Calc spreadsheet contains the following formula: =IF(D8=0,'',C8*D8/B8) If B8, C8, and D8 all have values, then this works. But if they are empty it doesn't work, I get a #NAME? error ...
1
vote
0answers
34 views

Can I use Excel keybindings in Libreoffice Calc?

LibreOffice Calc has its own set of keybindings, which is different to those in Excel. Is there a "compatibility mode", or some way to match Excel's keybindings? I know I can change the bindings one ...
2
votes
2answers
153 views

LibreOffice Calc: conditional formatting based of column / cell value

Unfortunately I cannot grasp how the conditional formatting works. I have a sheet like that: I have defined 2 cell styles "Low" and "High" and I want cells in columns A-E to have one of these styles ...
0
votes
1answer
62 views

LibreOffice Calc - Multiple INDEX-MATCH and copy to other cells

I'm just starting to use LibreOffice Calc to try to manage my finances, and have imported CSVs of statements from my bank. The first task I would like to accomplish is to display, in an easy-to-read ...
3
votes
2answers
114 views

Align data columns in LibreOffice Calc according to numbers in another column

I have three columns: Column A contains a count from 1 to 100, representing different sample stations. The numbers are in sequence. Column B is independent of column A. It contains selected ...
0
votes
1answer
43 views

Copy/Paste a formula in Libreoffice without adjusting the cells

I have a formula in Libreoffice Calc which I would like to move (Cut and Paste) to a different cell. I would like the formula to exactly stay the same without the cells in it being adjusted by the ...
0
votes
1answer
87 views

Disable auto formatting in LibreOffice Calc

Whenever I enter data in LibreOffice, may it be a date or a number, LO is formatting it as such instead of leaving it as text. That terribly annoys me because I want the text I enter to remain in that ...
1
vote
2answers
302 views

Insert the current date and time (DATETIME-function) with a single shortcut into a cell in LibreOffice Calc

I know there is the CTRL + ; shortcut to insert the current date and CTRL + SHIFT + ; to insert the current time. However I have two issues with that: 1) I would like to have one shurtcut for both ...
1
vote
1answer
31 views

CSV file - Combine title if value=YES in another cell - Libreoffice / Excel / Googledocs

I have a file with text divided into cells that I want to group into another cell. Cells are: A, B, C, and D are what I have. In each coluimn, if the value is Yes, I would like the Result column to ...
0
votes
1answer
8 views

LiberOffice Calc apply style with only specifc attributes

Consider this simple time tracking spreadsheet: I figured out how to use conditional format to apply a selected style to a region, in this case I use "Accent" for Fridays and Saturdays, and this ...
0
votes
1answer
26 views

Two related cells

In A1 I have price, for example 1000. In A2 fill percentage, for example 10%. Then in B2, calculate A1*A2 = value. I would like when i fill percentage in A2, in B2 calculate value and vice versa - If ...
0
votes
1answer
242 views

LibreOffice Calc: What is the data range syntax?

I want to specify a graph's data ranges, but I don't know what format the data range specifier strings are in. Specifically, I'd like to specify all cells in Column A starting and including row 2 for ...
0
votes
0answers
8 views

How can I get a report on Autofilter stats in LibreOffice Calc?

When I do Data -> Autofilter, can I have a report of how many different values there are and how many rows are in each for a column?
3
votes
1answer
39 views

In LibreOffice Calc, can be a digit added directly after a regex match in single find/replace operation?

I want to add character 5 after certain pattern found in the column (e.g. 4 digits). Example: aa1234bb → aa12345bb Method: Find: ([0-9]{4}) with Regular Expressions option checked Replace: $15 ...
0
votes
1answer
65 views

Find and Replace in LibreOffice Calc

So I have a Price column and all the values has a £ prefixed. These are formatted string. I tried via the Find & Replace function to replace all of these with nothing, but I can't seem to get it ...
2
votes
0answers
59 views

When I try to edit Y-axis limit on LibreOffice, the data in the chart disappears

Has anyone encountered such an issue? Am I missing something? When I choose axis limits manually from Right Click > Format Axis > Scale, the data in the chart disappears or gets hidden. I am ...
2
votes
1answer
20 views

Format cells to show fractions always with 30 on denominator on LibreOffice Calc

I use LibreOffice Calc to calculate proportional values in fraction format with denominator 30. 30 represents a commercial day in our country, that means the number of days in a month for ...
2
votes
1answer
166 views

How to set line height in Libre Office Calc

I have entered two lines in a wide enough cell. I used CTRL+Return combination at the end of the first line to wrap the text and entered the second line. I was able to set different style for the two ...
10
votes
2answers
697 views

Convert the multi-step sum of booleans into a single formula

I have a few sensor readings and I'm checking to see if the values are in the acceptable range. To do this I use an IF to check if the values are > or < the calculated (average) value. The ...
2
votes
1answer
56 views

References in color-scale conditional formatting formulae for ranges

I'm using Libreoffice Calc (v6.1b). Suppose I have numeric data in sheet column, A and I'm applying conditional formatting to sheet column B. I want each cell of B to have a color on a scale ...
0
votes
0answers
44 views

How to make custom currency in LibreOffice Calc?

I'm trying to create a custom currency (ETH) format for some value tracking in LibreOffice Calc. I followed this instructions on this link, but the numbers (e.g. 0.05) I enter in the formatted cells ...
1
vote
1answer
216 views

LibreOffice Calc #name error

Tell me, please, what's the problem. I'm trying to write an IF, but I'm constantly getting a #name error. Moreover, even with the simplest conditions, like 0 <1. The syntax was looked up in the ...
0
votes
1answer
145 views

LibreOffice does not recalculate sums

I have a Calc sheet in LibreOffice (6.0.3.2 on Ubuntu 18.04). In one field I have the formula =sum(D2:D85). Then I changed some values in the range D2 to D85, but the sum was not recalculated. If I ...
0
votes
1answer
87 views

How to use a cell value as reference in a formula

I have been trying to get a cell reference as text to be used as reference to another sheet: Got a dropdown menu cell (A2) returning a text string (e.g.: Sheet2, Sheet3) Now I wanted to reference ...
1
vote
1answer
242 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 ...
0
votes
0answers
149 views

How to export single sheet to PDF in LibreOffice Calc from CLI?

v5.3.7 scalc --convert-to pdf:calc_pdf_Export *.ods converts all sheets to PDF, but I must only export one named sheet. (All of the files have the same named sheet.) Thus: is there an option to ...
0
votes
1answer
432 views

Format seconds.milliseconds into minutes:seconds.milliseconds

How could I go about having a cell where the display shows a time format, but is edited in raw seconds and milliseconds? I want to have a cell where when clicked on and a value typed, the user types ...
0
votes
0answers
74 views

LibreOffice Calc copy&paste not working for text

I have a column denoting "tasks" and it spans about 10 000 rows. It looks like this (other columns are omitted) task 1 1 1 1 2 2 2 2 3 3 3 4 4 4 5 5 5 Now I need to make these numbers into text ...
1
vote
1answer
138 views

How to do a regex substitution on a string in an LO Basic “macro”?

This seems like something that should be extremely easy to do but it's not - it's certainly not obvious how to do it, anyway. I want to write a function in LibreOffice Basic that takes a string, a ...
1
vote
2answers
109 views

VLOOKUP returns #N/A when criterion is a reference

LibreOffice Calc 5.1.6.2 I want to use VLOOKUP function with criterion being a reference to a cell again containing a reference. But it doesn't work, it returns #N/A. Interestingly, when I replace ...
1
vote
0answers
61 views

Show contiguous time periods without data in Libre Calc pivot table, pivot chart?

A minimal example: our table has sales in 2015, 2017, but not 2016. Date Sales 01/02/15 $500 10/11/17 $1000 You make a Pivot Table: Data > Pivot Table > Create Row Fields: Date Data ...
1
vote
1answer
232 views

LibreOffice Calc color-coding cells categorically

I have a spreadsheet of text data; each column has a limited allowable set of string values. I have a second sheet in the file which, for each column in the first sheet, contains each allowed value ...
0
votes
1answer
189 views

Lag with LibreOffice Calc when scrolling quickly through rows/columns

I have moved to LibreOffice from Microsoft Office, basically because I refuse to pay the Price for Microsoft Office. I like LibreOffice as it offers everything I need and does pretty much the same ...
1
vote
1answer
51 views

How to find the last filled-in cell in a row?

I have the following data sheet. It has a row of dates, where every date has an A and a B value. I would like to find the last filled-in B value for that range. In the following screenshot, I would ...
1
vote
2answers
139 views

How do I preserve column grouping in Libreoffice Calc?

I am using Libreoffice 5.2.0.4 In Calc (the spreadsheet), I am trying to build an appointment book. Since this is designed a bit like a calendar, I have rows and columns that I want to be able to ...
1
vote
1answer
551 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 (...
2
votes
1answer
189 views

Change background color on a cell if that cell doesn't have a number entered

Trying to change the background color of a cell if there isn't a number entered in the cell and the date in an adjacent cell is older than today. in libreoffice calc. I have tried conditional format ...
1
vote
1answer
1k views

Use a Python function as formula in LibreOffice Calc cells

Since LibreOffice Calc supports macros, and the builtin functions are somewhat limited, I'd like to write my own functions in Python and then use them as formulas in individual cells. Ideally, I would ...
0
votes
0answers
56 views

libreoffice calc crashes when I want to add Dialogs

I'm using libreoffice calc 5.3 in Fedora 26. I'm trying to add some dialogs to my sheet. But when I want to edit dialogs my libreoffice crashes and it shows me a dialog with 2 options: "force quit" ...