Tag Info

Hot answers tagged

39

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, ...


31

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.


23

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!!


22

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 ...


14

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 ...


14

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 ...


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 ...


11

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.


11

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 ...


10

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.


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

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, ...


8

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 ...


8

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 ...


7

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), ...


7

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


7

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.


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

Copy > Paste Special > Check/Select "Transpose"


6

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 ...


6

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


6

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.


5

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

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


5

This is a little long winded, but it's the only way I have found so far. Create the chart as normal in Calc. Select the chart and copy it. Open Draw and paste the chart. Resize to suit your needs. Make sure the chart is selected. Export the Draw page as a PNG making sure to turn on the "Selection" option.


5

Select chart and copy it. Click empty cell Edit / Paste special Choose paste as... GDI Metafile. Copy the just pasted chart Paste into your favourite image editor. Save as PNG. GDI metafile is an image, not the real plot. Keep the original graph in Calc always.


5

Yes, you need other software, such as MS Excel. What you are looking for is a missing feature in Libre Office Charts. Many people would like to have the ability to plot a date on the X-axis when using area charts, but the software only supports the use of ORDINAL numbers. This is a glaring defect in the software that has been present for a long time. ...


5

In Microsoft Excel 2007: Copy (not cut) the data to be "flipped". Click in the upper left corner of a non-overlapping region where you want the data to be flipped into.  (You might want to do this in a new worksheet.) Click on the arrow (triangle) below the Paste icon. Click on Transpose.  


5

What you're looking for is called an Indirect Reference. INDIRECT(B3&".A2") I believe would do what you're looking for. To explain, it's the concatenation of the string value of cell B3 in the current sheet and the string ".A2". The resulting string, "Sheet2.A2", is then evaluated as a reference.



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