If your selection is at the top of a column, and you want to quickly select the entire column, you can use the keyboard shortcut Ctrl + Shift + ↓. However, this ONLY works if there are no blank cells in the column.
Columns that have blank cells require you to repeatedly hit the down arrow to make it all the way down to the end.
So, is there a workaround?
There are two things you could do to achieve the same result and potentially save you some time.
Video Tutorial
Watch on YouTube & Subscribe to our Channel
Downloads
Option 1
The first is to hit Crtl + Shift + End. This will take you all the way to the bottom right cell in your data range.
From there, you can hold Shift down while you hit the left arrow until only your desired column is selected.
Option 2
Another option is available if you have a column with no blank cells directly adjacent to your desired column selection. If so. You can start from the top of that non-blank column and hit Ctrl + Shift + ↓.
Then Shift + →.
Then hit Tab to put the active cell at the top of your desired column.
And finally, Shift + → to deselect the original column.
Excel Tables
If you are using an Excel Table, the process is much easier. With any cell in the desired column selected, all you have to do is hit Ctrl + Space. That will select the entire column.
Do you use any of these methods, or do you find yourself just hitting the down arrow multiple times? Or maybe you tend to use the mouse instead? I'm curious to know your preference. Tell me in the comments, and feel free to leave any questions there as well.
I tend to left click then scroll down with my mouse and then shift+left click to selct the entire column. I didnt realise you could tab across while the column was selected and then ctrl+shift+right to make the single column selection! Will be doing that from now on. Cheers
Thank you for those great workarounds! Until now, I would just keep hitting ctrl shift down arrow till the end. Now I think option 1 will be the fastest way to go in most cases. What about a quick way to highlight only the non blank cells. I either use sumif or a data filter to filter out the blanks.
When will the new CHECK BOX in cells feature be available to all 365 users?
thank you
Thank you Jon, love it. Did not know this one and gonna make user of this one a lot. Enjoy these short quick info session.
Hi. If the cells are filled manually it’s easy just to put a space in emty cells.
This option works and doesn’t disturb formulas.
I have used all of these shortcuts in the past, but for years I’ve been using a much easier solution, though it does require a 3rd party extension.
ASAP-Utilities is a cheap ($50 for commercial use; free for non-profit, student or personal use) suite of macros that streamlines a ton of common tasks (and lots of uncommon tasks). Two of these tools extend the current selection to the last row or column of data, and can be linked to personalized keyboard shortcuts. It’s an enormous time-saver!
Disclaimer: I have no connection to ASAP, except as a long-time, satisfied user, and wish to spread the news of a great tool to other Excel users.
Since my datasets are not overly huge, I usually just click on the top cell of the column, then use my mouse wheel to scroll down to the bottom cell of the column. Then while holding the Shift key down, I click on the bottom cell. This highlights the column for me. I know that’s not what you were looking for, but with smaller datasets, it is easy.
Happy holidays, Jon! I usually freeze the top row(s) so that I can scroll all the way down, select the bottom-most cell of the first column, shift + click the cell right above the frozen line, then shift + down. This is particularly useful when I need to select several columns in a big table for a vlookup function (I know, so old school of me lol). I also use this as a way to select column(s) in non-tables and/or ctrl + space.
Thanks for this tutorial, the method I usually apply is your first example with control shift end.
I usually use (when I think of it and don’t just do it the hard way) the following quick process:
1. Press Ctrl-Home
2. With row 1 showing, either click the column label (“J”, say) or the row 1 cell
3. Noticing that either thing in Step 2 makes the selected cell the row 1 cell, and highlights the rest of the column, I then press Ctrl-Shift-Up Arrow
and now I have highlighted (selected) the cells from row 1 down to the last cell with content (or more exactly, any that are marked as having been used even if they ought to be considered never used).
So for “not weird, or stupid-Excel” situations, I have selected every last cell that is in use in that column.