## Hot answers tagged spreadsheet

29

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

23

Just hit SHIFT+CTRL+V or Paste Special... from the Edit menu, unselect Paste all, and select Text or Numbers:

16

Its a custom sort in the Data sorting. See here for more info.

13

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

12

Put a $ in front of the A column number in your formula, like this:
=B1-A$1
You can then copy and paste the formula and it will preserve the A column number. This should work in both Excel and Open Office.

9

Select a cell in Column M.
Select all cells in the spreadsheet with Ctrl-A.
Open the Data->Filter->Standard Filter menu.
Set the first row of the filter so that Field Name is "Column M", Condition is "Not Equals" and Value is "on".
Click OK to apply the filter to the spreadsheet.
Select all rows in the spreadsheet with Ctrl-A. To unselect any column header ...

9

Insert a column
In first row insert formula =MOD(ROW(),7)
Copy down
Copy/paste special/values
Data/Filter out the ones you want (0 or 6, probably)
Delete the rest of the rows Remove filter Delete column

9

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 first row below that which you would like "frozen" and select Window | Freeze.
EDIT: In fact, this works to freeze rows and columns. For example, if select cell C3 and Window | Freeze, then the first two rows and the first two columns will be frozen.

8

In Excel the $ in front of the Rownumber fixes the row to a constant. A $ in front of the column letter fixes the column.
So you have:
A1 if you copy down, the 1 will change to 2,3, etc; if you copy across, the A will change to B,C, etc
A$1 if you copy down, the 1 stays 1, if you copy across the A changes
$A1 if you copy down the 1 changes, if you copy ...

7

I had the same problem and fixed by using the =HYPERLINK function in the cell rather than the "insert hyperlink" option.
HYPERLINK(link_location, [friendly_name])
Make sure to place the location and friendly_name in quotes (" ").

7

As far as I know there are no built-in features that can parse and summarize comma-separated tags in Excel. You can, of course, create your own solution with worksheet functions and a little VBA. Here's a quick solution for doing this.
Step 1: Press Alt+F11 to open the VBA editor pane in Excel. Insert a new module and paste in this code for a custom ...

6

As the message indicates, you're trying to insert rows into a Table in your sheet. Typically, but not necessarily, tables will have banded formatting. When you click in a cell in a table, the Table tab will appear, like so:
The Insert and Delete commands get grayed out when there's more than one table intersecting the row you're trying to delete (and ...

6

The ADDRESS() function does just that. It provides a reference to a cell and not the contents of the cell. The INDIRECT() function will take the cell references and provide the cell contents so the SUM() function works properly.

6

I dont use spreadsheets other than Excel, but Excel is capable of doing just about anything using VBA, including custom functions. Check out this article to see a basic example.

6

Select the rows you want to delete and hit Ctrl+- (minus).
If you select the whole row, you will not be prompted, if you only select some cells you will be prompted what you want to do exactly.
Edit: btw. Ctrl++ inserts rows.

6

This happens because of the limited precision of floating point numbers representation on computers and cannot be fixed because it is an intrinsic problem of the way number are represented on a computer. There might be workarounds, such as round() or a clever implementation of the formulas to reduce error propagations.
Excel uses 8-byte (64 bit) floating ...

6

The second argument to the COUNTIF formula is interpreted by Excel as a test to be performed (e.g. we can enter ">3", etc.). In this case, it looks like Excel is converting the "true" and "false" strings to 1 and 0. That won't match the original text.
The only solution I know to this problem is to write VBA code to do the counting.
If changing the input ...

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

Couple of thoughts:
(1) Is there a particular reason why the budgeting is not incorporated into your existing ERP?
If you are using SAP or a similar accounting package then these usually have some kind of a built-in forecasting functionality. The added benefit would be that actual vs. budget could be run pretty easily and you could reconcile to the general ...

5

Select both rows (using the Shift key or by dragging the mouse - don't use the Ctrl key)
Copy
Right-click → Paste Special
In Region operation, choose Flip Vertically

5

Here's one of the complicated solutions. It's probably best just to paste this in and hope for the best rather than trying to sit down and parse this.
=INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - ...

5

The search function is located under the "Edit" menu, and can also be reached by it's keyboard shortcut which is pressing Ctrl (or Cmd on a Mac) and F simultaneously.

5

This will do what you want:
Open the Registry Editor, browse to HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open
Delete the ddeexec key, (or just rename it if you are worried - I just renamed it and it worked)
Click on the "command" key and replace the /e on the end of the (Default) and command strings in that key with "%1"
The quotes around %1 are important ...

5

=IF(INDIRECT("D"&ROW())="FOO","BAR","BAZ")
That should work. INDIRECT isn't a very quick formula, so if you have a lot of them it could slow things down for you.

5

The V in VLOOKUP stands for 'Vertical,' which implies that it will work only on columns, where LOOKUP can scan columns or rows. Note also that LOOKUP is restricted to a single column or row, not a range, as VLOOKUP (or HLOOKUP for rows) can span.

5

Try this:
Select the first cell in the column.
Type Ctrl+Shift+Down-Arrow
That will select only the cells with values in the column; at least in Excel.

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