Excel - Select entire column from the selected row

Question

No need for VBA. I just need a keyboard shortcut.

I am trying to select the entire column minus the first 5 rows and do Format Cell.

I tried Ctrl + Shift + Down Array, but it takes me all the way to the bottom. It is hard to navigate back. Is there a better short cut ?

Solution 1

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.

Solution 2

The easiest way is to select the cell in row 6, then press Ctrl+Shift+Down which will select the entire column except the first five cells. It takes you to the bottom, but all the cells you want are selected, so then do your right-click on the selection and choose format cells.

There's no need to go to the bottom first.

Solution 3

Start at the bottom. For column A, for instance, select call A65536, press Ctrl + Shift + Up to get to cell A1, then still holding Shift, press Down five times.

Comments

[jonsca]

Ctrl-shift-down scrolls to the bottom automatically, I don't think there's a setting to change that.

[Lance Roberts]

Yes, but it selects exactly what you need, so it doesn't matter that you're at the bottom. As opposed to removing the cells at the top manually (which I've had to do plenty of myself for ranges that are midway in the page). Edited for clarity.

[jonsca]

Ah, okay. That's the way I was trying to steer the OP, yes. I just assumed he/she would start out at the original row that they had tried. I see what you are saying, though.

[Lance Roberts]

You're welcome to include that in your answer, and I'll delete mine, just @ me when you do.

[jonsca]

I fixed it up a bit, but don't feel obligated to delete yours... I thought it was worth an upvote.

[chris neilsen]

FYI having selected a range like this, you can use Tab to move the active cell around the selected range. Having used Ctrl-Shift-Down the active cell is still to top of the range, although its off screen. Pressing Tab then Shift-Tab refreshes the screen with the active cell at the top of the range, and on screen, and maintains the selection