40
votes

### Is there a way to scroll, by pixel, instead of by cell, in either Excel or OpenOffice Calc?

I see you've gotten a lot of unhelpful answers, and this one probably adds to that list. It is not possible to do this in Excel. There was a conversation with an Excel MVP in 2013 on this topic that ...

- 1,359

23
votes

### LibreOffice pivot table group by month

I'm not sure whether this was possible in LibreOffice that was current in the time when this question was asked but now this is quite easy to do, so if anyone stumbles upon this question this might ...

- 331

16
votes

Accepted

### How to find and remove external links in (Libre|Open)Office Calc spreadsheet

I also got that popup on startup:
I had the same problem and found out today that there is an option in the Edit menu called Links.... Select that option and you get a dialog with the list of ...

- 1,448

14
votes

### How to create a formula for every row in a column in Google SpreadSheet?

The suggested answers work well for small sheets but I had thousands of rows and using the mouse or the keyboard to select them was simply too time consuming.
The ARRAYFORMULA method works but it's ...

- 316

13
votes

### How to highlight whole row and column under cursor in OpenOffice Calc?

Not exactly as requested, but SHIFT+SPACE / CTRL+SHIFT+SPACE selects current row / column respectively (at least in LibreOffice Calc - i didn't test it with OpenOffice!). To unselect, just move the ...

- 10.1k

13
votes

Accepted

### In LibreOffice Calc is there a hotkey to edit the current cell?

F2, Same as in Microsoft Excel.
Once done you can press Enter to switch to next row.

- 246

11
votes

### OpenOffice Calc weighted average

Since this is the second result on my Google Search of "LibreOffice Weighted Average" I may aswell give it an answer:
The Weighted Average can be calculated by the use of the function:
=SUMPRODUCT(...

- 503

8
votes

Accepted

### Get DayName from Date in OpenOffice Calc?

You could simply format the cell to display the day name instead of an integer.
In the 'Format Cells' dialogue box (right click and choose 'Format Cells...' to open) enter:
NNNN
NNN
or
NN
in the '...

- 330

6
votes

### How to find and remove external links in (Libre|Open)Office Calc spreadsheet

Every time I opened up a Calc Sheet I had copied, I received the same pop up "This file contains links to other files. Should they be updated?"
The spreadsheet I was opening was copied from another ...

- 71

6
votes

### How to create a formula for every row in a column in Google SpreadSheet?

Very similar to ceoliphant's answer but a little more straightforward, simply add one formula to C1:
=ARRAYFORMULA(iferror(A:A*B:B*1.6))

- 241

6
votes

### How to create a formula for every row in a column in Google SpreadSheet?

I found all of these solutions very frustrating and confusing also.
I will warn you though, this will replace whatever is currently in the cells, but as it is a formula this should not be a problem.
...

- 365

6
votes

### Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

From menu: Format → AutoFormat Styles.. . You could add your own.
Another option is to use macros (as in ngulam's answer).
Color2Rows is an extension that adds a button to the tool bar, for ...

- 658

5
votes

### How to create a formula for every row in a column in Google SpreadSheet?

After you write your forumla, you can double click the bottom, right corner of the selected cell with the blue box, to copy the data down the column down as long as a neighboring cell has data.
This ...

- 332

5
votes

Accepted

### Can you reference an entire column in OpenOffice Calc (like A:A in Excel)?

No, but it will be available in a future version of Calc: https://bugs.documentfoundation.org/show_bug.cgi?id=44419
Edit: Actually, it's already downloadable in "LibreOffice Fresh".
Also this ...

- 7,410

5
votes

### LibreOffice Calc equivalent to Excel "Evaluate Formula" feature

I'm answering my own question with what I know so far, anyone who can provide better solutions/news about new Calc features, really welcome!
As far as I know, what comes closest in Calc to Excel "...

- 1,914

5
votes

Accepted

### Issue with IF statement in Libreoffice Calc

From what I can see, you have a missing quote:
=IF(L7>11,"$500,IF(L7>11,"$1200",IF(L7>15,"$2000",$0)))
^ ^
...

- 1,448

4
votes

### Get DayName from Date in OpenOffice Calc?

If you only want to display day name and retain it's value - use user defined format of NNN.
If you want to actually change it to text - use TEXT(WEEKDAY("2008-06-14"; 1);"NNN")

- 7,042

4
votes

Accepted

### Fill right in OpenOffice/LibreOffice

It turns out there's no hot-key for it (by default anyway). But you can get it by going to menu Edit → Fill → Right.
In LibreOffice version 5 it is menu Sheets → Fill Cells.

- 1,315

3
votes

### Is there a way to scroll, by pixel, instead of by cell, in either Excel or OpenOffice Calc?

I had the same issue. Chosing Excel because of the controlled cells for text.
My solution was to keep the heights of the cells in the sheet, thus scrolling floats smoothly. The cells with text were ...

3
votes

### Inserting an image into a OpenOffice.org Calc cell

Insert image in the normal way, then right click, Anchor > To cell.
There you have it. I don't understand why is it necessary to put in background, it's just working fast and fine.

- 31

3
votes

### Get DayName from Date in OpenOffice Calc?

You can achieve this by converting your result into a text. To do this you write:
=TEXT(7,"DDDD")
The output is:
Saturday
A solid alternative would be:
=TEXT(WEEKDAY("2008-06-14", 1), "DDDD")
...

- 141

3
votes

Accepted

### Pasting Hex Colors Into OpenOffice

I have LibreOffice instead of OpenOffice but I believe the process should be the same since they are mostly the same program:
Copy the table
Go to Calc and Use Edit->Paste Special... or press Ctrl + ...

- 11.1k

3
votes

### Alternate If() function in Excel/OOCalc

User tohuwawohu posted an answer that led me directly to mine. I don't think tohuwawohu quite understood what I wanted, perhaps through my fault, but his macro suggestion helped me to understand. I ...

- 133

3
votes

### Enter the same data into multiple, non-adjacent cells in OpenOffice.Org Calc

AFAIK, you can't do this for non-adjacent cells in OpenOffice or LibreOffice Calc (this is different from Excel - there, you can use CTRL+Enter on non-contiguous ranges). For adjacent cells, use ALT+...

- 10.1k

3
votes

Accepted

### Adding a percentage to every cell in a column in LibreOffice calc

If that update is to be static, as in "just change the numbers in the first column";
Type 1.03 in any cell
Select the cell with 1.03 in it, hold CTRL and hit C
Select the cells with numbers in the ...

- 8,434

2
votes

### Compound Interest in Open Office Calc

There are many financial functions in OpenOffice.org Calc, but you need to be very clear about exactly what kind of "compound interest" transaction you are trying to calculate.
The IPMT(), PPMT(), ...

- 265

2
votes

### How to create diagram in spreadsheet with dates on x-axis?

I suppose by now your boys are already in school. But if you are still logging their growth (or for other similar problems), I have an not bad solution. Here is an example chart that I made based on ...

- 297

2
votes

### How to find and remove external links in (Libre|Open)Office Calc spreadsheet

Had the same problem myself, and after reading the discussion here, I tried copying the sheets I wanted to a new file (in my case this was easy as I only had one sheet), and that fixed it for me. It ...

- 195

2
votes

### How to get VLOOKUP to return the *last* match?

(Answering here as no separate question for sorted data.)
If the data were sorted, you could use VLOOKUP with the range_lookup argument TRUE (or omitted, since it's the default), which is officially ...

- 231

2
votes

### Using ISBLANK formula to write a variance

You want e4 to remain blank until data is entered in d4?
try entering into e4:
=if(d4="","",d4-c4)
If you could have a blank in c4 then try
=if(or(c4="",d4=""),"",d4-c4)
If you're using open ...

- 395

Only top scored, non community-wiki answers of a minimum length are eligible

#### Related Tags

openoffice-calc × 351openoffice × 116

libreoffice-calc × 97

microsoft-excel × 77

worksheet-function × 57

libreoffice × 53

spreadsheet × 42

csv × 17

charts × 12

conditional-formatting × 8

copy-paste × 7

google-spreadsheets × 6

cells × 6

autocomplete × 5

microsoft-excel-2007 × 4

printing × 4

sorting × 4

date × 4

vlookup × 4

openoffice-base × 4

windows × 3

windows-7 × 3

microsoft-excel-2010 × 3

fonts × 3

regex × 3