Hot answers tagged

77

I found a more simple way to do that: Select the whole column Data > Filter > Standard Filter Change 'Field Name' to -none- , click on 'More options' and check on 'No duplication' box That's it. You can copy and paste the filtered fields if you want contiguously numbered cells.


59

Using the Mouse Click the cell whose formula you want to repeat A dark square "handle" will appear in the lower right corner Click and drag that handle, dragging down the column (or right across the row) Stop at the last cell you wish to fill Using the Keyboard Move the cursor to the cell whose formula you want to repeat Hold shift While holding, ...


37

An even easier solution in Google Sheets would be the formula, entered in C1: =ARRAYFORMULA(IF(A5:A,A5:A*(1.6*B5:B),"")) It will automatically replicate ("Continue") to succeeding rows if a value is entered in column A. No need to copy it to each row. In fact, if you copied it to C2 it would be automatically overwritten by the continuation of the formula ...


27

Go to "Format" , "Page...", Header/Footer to set up the pages how you want them Edit 2015 I´m using Libre Office these days but a similar set of commands works there too. I´m suprised this answer still gets so many hits, glad it helps!!


24

It's even easier than pnuts' solution. You don't need to select the cell that holds the value that should be relevant for conditional formatting. Just select all the cells that should get conditionally formatted, and use a formula-based rule. Now, if your formula uses a cell address with fixed column (e.g. '$D5'), OpenOffice will adapt it for every selected ...


18

You can do this with the OpenOffice advanced filter (on the main menu..Data/Filter/Advanced Filter) Ensure your column of data has a title at the top, e.g. title and that the data is contiguous (no empty cells) or select the whole column including empty cells by clicking on the column header., Create a filter criterion that would include all the data in ...


16

Format menu -> Styles and formatting. You should get a dialog that looks similar to this: Right click on Default, and choose modify. In the fonts tab you should be able to choose the new default font.


13

Select the cells you want to copy, and use Ctrl-C to copy them. Select the cell where you want to copy the cells. Open the Edit->Paste Special menu. In the Selection section, ensure that the Paste All and Formulas options are not selected. Select all other options in that section. Click OK, and the cells will be copied without the formulas.


13

If you want to use the customized document as standard template (so it's used if you select menu File -> New -> Spreadsheet), the following steps are required (assuming you've created a fresh spreadsheet document and changed the default font as Kirk has described): Select menu File -> Templates -> Save...; In the Template dialogue, enter a ...


12

here is a another way, go ahead and delete all the formulas that are in there right now, then type in the formula in C1 having it correspond to A1 and B1 and hit enter. so now the correct formula is just in C1, now click the C1 box, a bounding box will appear, the bottom right corner of this bounding box has a dark square, double click this square and the ...


12

I've actually figured this one out, here is the answer: =(C2 - DATEVALUE("1/1/1970"))*86400


11

You can't use the page preview options to scale the PDF output. Output scaling has to be done using the Sheet properties in the Page Style dialogue (Select the Menu Format -> Page... -> Sheet Tab). There, you have different options to make the Calc output fit to a certain number of pages. In the following screenshot, the Scale options are highlighted, ...


9

Select the cells, type the data and then press enter while keeping the alt key pressed. Works on my system (Mac, OO 3.2), maybe the modifier key is different on different systems. In Excel on Windows the modifier key is ctrl.


9

Updated instructions for LibreOffice 4.0.x: The process is still as per tohuwawohu's answer, yet the specific steps have changed a little: To save as template, go to menu File > Save as template... Select "My Templates" in the Template Manager, and then click on the button "Save". See image on the next step. A new window will appear asking for a name ...


9

Select your Microsoft Excel plots. Copy. Open Microsoft PowerPoint. Paste-special as enhanced metafile (EMF) into an otherwise empty slide. Save your PowerPoint slide as an "other format" file, and choose "EMF" (Enhanced Windows Metafile). Import your EMF file into InkScape and ungroup the object. Delete all the A4-sized crappy blank space from the ...


9

Yes, this is possible. You need to select the XY (Scatter)chart type. If you start with this data table: ..., select it, start the chart wizard and select XY (Scatter), the result preview already shows that the data points are córrectly spaced: (NB: i didn't customize the wizard's defaults yet - Calc did recognize the columns correctly). The only ...


9

I found the problem. There were 4 x 65536 (mainly unused) rows with a custom row height. I selected all unused rows, invoked Format > Row > Optimal Height and pressed OK. This shrinked the file from 5 MB to 10 k. For discovering this I saved the file as .xlsx (Excel 2007/2010 XML or as OfficeOpen XML Spreadsheet, makes no difference). This is a zip format ...


8

Yes, there is a way to move whole columns using the mouse like in Excel: To move: press ALT, then left click somewhere in the selected column (not the column header) and drag. To copy: press Ctrl + ALT, then left click somewhere in the selected column (not the column header) and drag. Tested with OpenOffice 3.2 Calc, Windows XP.


8

Though you can indeed copy-'n-paste directely into InkScape you might lose some formatting in the process. Exporting to PDF ensures that the background of your Excel file will be retained as well. Works very well in my experience. In Microsoft Excel, "Save as PDF". In Inkscape "Open PDF". Document properties, expand "Resize page to content" (hiddent), ...


8

Goto Tools -> Autocorrect Options Uncheck Replace in the Double Quotes section of the Localized Options tab.


7

Use the following formula (example for A1): =MROUND(A1; 5)


7

Try this solution: =INDEX(A:B,MATCH(2,B:B,0),1) This chooses a specific cell out of a matrix. MATCH retrieves the row inside the matrix B:B where "2" was found with exact match (0). INDEX uses this row, and a given column (1), to refer to a specific cell inside a matrix A:B. "Foo" would be in column A, "Bar" would be column B.


6

1) Select cell B2 2) Window -> Split 3) Window -> Freeze


6

To return the value from column A, row R, you can use =INDIRECT("A"&R) To count the number of values in column A, you can use =COUNTA(A1:A65536) So for your case, you could use =INDIRECT("A"&RANDBETWEEN(1;COUNTA(A1:A65536))) You might be able to make a macro button that will recalculate for you. I don't know enough about OpenOffice.org Calc's macro ...


6

Try Gnumeric. It supports exporting graphs to SVG natively. There's a windows version, too. So, the toolchain would be Excel -> Gnumeric -> SVG, or even Gnumeric -> SVG without using Excel. IMHO it's the easiest way to get your graph in SVG.


6

Copy > Paste Special > Check/Select "Transpose"


6

I found the solution: the formula in column C should be like this: (C3 cell) =B3*$A$30 This way, on copy-paste only the B3 reference is rewritten, the A30 is left unchanged. The $A$30 syntax can be achieved with Shift+F4 too.


5

Yes, there is a command-line way to convert between these file formats. But a macro must first be installed into OpenOffice. This macro will take a specified OpenOffice file name from the command line and create a Microsoft Excel file (.xls) with the same name except for the file extension. I have tested this with OpenOffice 3.2 on Windows, but I expect it ...


5

One of the tools I always have with me is 'Pentaho Kettle' which is a fantastic cross platform open source ETL. You can find the 'free' edition at http://kettle.pentaho.com/ Once you have downloaded and installed the software, the steps are roughly as follows: Launch Pentaho Kettle (Typically) click on 'No Repository' Create a new transformation. On ...



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