Excel – How to apply a formula to an entire Excel column using a keyboard missing its END key

keyboardmicrosoft excelworksheet-function

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?

Best Answer

Supposing you want to put the formulas in column F

I would try in this way:

write your formula on F1 (so the first cell will contain =LEFT(E1,2) )
select and copy the cell containing the formula (F1)
select the entire F column (click with your mouse on the column letter on top)
finally paste-special/formula and your formula will be in the entire F column (even a simple paste should do)

Related Solutions

Excel – Select entire column from the selected row

Use the Ctrl + Shift + ↓ (down arrow) that you suggest (starting at the 6th cell down in that column). Then, right click to select one of the cells at the bottom, and choose Format Cells... etc. When you apply the formatting the cursor will jump back to the top of the range (in Excel 2007 anyway).

As an aside, if you're at the bottom, you can hit Ctrl+Home to get back to A1 quickly.

Excel – How to change a single character in an excel formula for 1500 rows

There are several ways to do this

Use VBA » ALT+F11 to insert and ALT+F8 to execute this little macro

This has the benefit that you can insert even invalid references (e.g. the reference will be there only on another PC or in the future) without a update data dialog for every cell

If you want, you can select single columns with columns("A:A") instead of usedrange
```
Sub replace()
 Application.DisplayAlerts = False
 For Each cell In UsedRange
    cell.Formula = replace(cell.Formula, "='4", "='3")
 Next
 Application.DisplayAlerts = True
End Sub
```
Use Excel find & replace function » CTRL+H and search in formulas.
Select your columns you want to alter before you open the dialog

Best Answer

Related Solutions

Excel – Select entire column from the selected row

Excel – How to change a single character in an excel formula for 1500 rows

Related Question