## Hot answers tagged spreadsheet

49

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

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

26

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

22

Its a custom sort in the Data sorting.
From the Data tab click on the Sort button to open the Sort dialog box, click on Options to open the Sort Options dialog box and select Sort left to right. 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.

9

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

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

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

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

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

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

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

You can absolutely do this with Tables, as well as Powerpivot and Data Explorer. The easiest, and my preferred method currently, is to create a parameterized query in Excel. To do this use the Query Wizard and at the end add constraints for each parameter you want to pass. Once this is done, you can edit the Connection Properties and assign the Parameter ...

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

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

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

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

5

The way I understand your description of the problem, you have a list of employee names in column A ("Name") and a list of hours for each employee in column B ("HoursWorked"). Some employees are listed multiple times. You want to get a list of each employee once along with their total number hours.
The easiest way to do this would be a pivot table:
Select ...

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