Tag Info

Hot answers tagged

23

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


17

Go to "Format" , "Page...", Header/Footer to set up the pages how you want them


12

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.


10

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


9

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.


9

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


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

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


8

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.


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

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., Create a filter criterion that would include all the data in your column, e.g. in cell D1 enter ...


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

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

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


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

Copy > Paste Special > Check/Select "Transpose"


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

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

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.


5

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


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

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


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

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


5

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


4

Preface: I'll assume you're familiar with the Calc way of referring to Cells, which is ColumnLetterRowNumber and ranges are expressed like this: A10:C12 mean all cells between A10 and C12, so 3 rows and three columns Ok, I assume your data is in A1:B100 (or however many rows you have). Now, in D1 enter Project#, in E1 enter Start date and in F1 enter End ...


4

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.


4

It's the number of days passed from Friday, December 29, 1899 and your date. References & proofs: Wolfram|Alpha - calculation proof System time (Wikipedia) - note that on other systems / software this date is different. Data types in VB - scroll down to "Notes for Date data type". Microsoft Support - "Office spreadsheet" proof. Note that Excel's ...



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