## Hot answers tagged spreadsheet

37

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

25

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

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

18

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

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

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.

12

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

10

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

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

8

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 (" ").

8

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

7

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

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

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

What you are looking for is called "Cartesian Product" or "outer join".
The solutuion as VBA. Copy the following code into the right-hand side of the Visual Basic Editor. You will see that your macro now has a name "cartesianproduct": (Source)
Sub cartesianproduct()
Dim startrange As Range
range1 = Application.InputBox(Prompt:="Please Select First Range", ...

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.

5

Create a Macro that iterates through your cells, reads the value in the cell and assigns it to the color like this:
Range("A1:A6").Interior.Color = RGB(200,160,35)

5

Try this formula:
=24*(B1+(A1>B1)-A1)
Where:
A1 is your Start Time ("Started At")
B1 is your End Time ("Left At")
It'll help you compute the number of hours elapsed between 2 time stamps. It should work even if your "End Time" is past midnight (or even 2 days later). Don't forget to set the cell format to General or 0.00.
Some examples:
If you ...

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

Your value type in D6 might be a number and you might be comparing it with a text value, try removing the quotes around the number 30. This might solve your problem. (I don't have excel around).

5

You need to use FALSE for the range_lookup value.
From VLOOKUP help
Range_lookup A logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match:
If TRUE or omitted, an exact or approximate match is returned. If an
exact match is not found, the next largest value that is less than
lookup_value is ...

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