2

I have a keyboard with a trackpad that's missing some of the normal keys available on a full-sized keyboard (among them Home and End).

Also, there are 30,000 rows in the sheet and dragging the corner of the cell down the length of the column takes too long. It scrolls for minutes, and I have to create many formulas.

Is it possible in the formula itself to say that it applies to the entire column?

I want to the formula for this new column to grab the first two characters of another column:

=LEFT(E1|2)

Is there a way to specify "column E" rather than Cell E1?

4

Supposing you want to put the formulas in column F

I would try in this way:

  1. write your formula on F1 (so the first cell will contain =LEFT(E1,2) )

  2. select and copy the cell containing the formula (F1)

  3. select the entire F column (click with your mouse on the column letter on top)

  4. finally paste-special/formula and your formula will be in the entire F column (even a simple paste should do)

  • Thanks for paste-special. It works. I can accept your answer in a few minutes :) – Tim Jun 24 '15 at 19:10
2
  1. Enter your formula in the first row.
  2. Arrow over into the column E.
  3. Press Ctrl+Down. If E has any blanks, you'll have to push this a few times to get to the last row with data.
  4. Arrow over back to your formula column (in the last row).
  5. Press Ctrl+Shift+Up. This will select all the cells above, including the top row that includes the formula you entered.
  6. Press Ctrl+D to fill the formula throughout the selection.
2

Select a cell in a column in your table that has values filled in all the way down to the bottom row of your table (if there is such a column), hit Ctrl + down, which will jump you to the bottom row.

Then move over to the column where you want to fill the formula down, hit ctrl+shift+up to select all the cells from there up.

If you have a header row or something that you don't want to copy into all those cells, hit shift+down to contract the top of the selection down to the first cell with the formula you want to copy. Then select "fill down" in the editing panel.

Note: This is if you only want the formula filled in down to the lowest row you are actually using - if you want the formula filled into the entire column, follow the copy and paste suggestion by danicotra.

2

Assuming that you want to put the formula into Column F:

  • Type F1:F30000 into the Name Box (to the left of the Formula Bar) and press Enter.  This will select that range.

  • Type =LEFT(E1, 2)

    … and press Ctrl+Enter (not Ctrl+Shift+Enter).

That’s it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.