Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I know I can select all cells in a particular column by clicking on column header descriptor (ie. A or AB). But is it possible to then exclude a few cells out of it, like my data table headings?

Example

I would like to select data cells of a particular column to set Data Validation (that would eventually display a drop down of list values defined in a named range). But I don't want my data header cells to be included in this selection (so they won't have these drop downs displayed nor will they be validated). What if I later decide to change validation settings of these cells?

How can I selection my column then?

A sidenote

I know I can set data validation on the whole column and then select only those cells that I want to exclude and clear their data validation. What I would like to know is is ti possible to do the correct selection in the first step to avoid this second one.

I tried clicking on the column descriptor to select the whole column and then CTRL-click those cells I don't want to include in my selection, but it didn't work as expected.

share|improve this question
up vote 47 down vote accepted

Click on the first cell you want to be selected and then press Ctrl + Shift + .

share|improve this answer
3  
This is fine when I have an empty data part of my worksheet. But if I have some filled-in rows this only selects cells up to the last cell that has any data within the same row. – Robert Koritnik Dec 28 '09 at 11:35
9  
What if you press it again? – Mario Marinato -br- Dec 28 '09 at 12:27
2  
+1 for simply awesome. I was just searching for something like this, and it works beautifully. – Breakthrough Jul 6 '11 at 13:21
    
For Excel for Mac users, this is Command+Shift+DownArrow – Brad Thomas Nov 6 '15 at 21:43
    
And on the Mac, this only selects up to the next cell that has a value, not the last one. For a column with many values, this solution is cumbersome. – spinup Mar 2 at 18:00

You can type F5 to bring up the name box. Then type B3:B65536 and click OK. 65536 for Excel 2003 and earlier, 1048576 for 2007 and 2010.

share|improve this answer
    
Name box is located to the right of the Formula bar. Pressing F5 doesn't bring it up but opens the "Go to" window instead. – Peachy Nov 9 '12 at 10:40
    
This one fits better for me as I need to prepare template for the customer. I do not know know many lines will exactly be but probably much less than 65536 – h22 Jan 28 '13 at 16:48

You can simply use the Name box, to the left of the Formula bar, and type the cell range you want selected. Once selected, you can also name this range so that you can use it's name as a reference in formulas and functions.


enter image description here

share|improve this answer
1  
This is of course true, but you have to remember the number. Using the accepted answer's way you don't have to remember anything and I'd argue it's also faster. :) But thanks for adding such a visually complete answer. It may help others. – Robert Koritnik Nov 9 '12 at 11:34
2  
I agree, but once you name it, you don't have to remember the row numbers. – Peachy Nov 9 '12 at 12:20

Mario's answer up top (which was chosen) works, but I feel it is lacking since you are forced to go all the way to the bottom of the sheet - you then have to use another shortcut to center the screen back at the top of the sheet. Here is my solution (only tested in Excel 2013):

I tried clicking on the column descriptor to select the whole column and then CTRL-click those cells I don't want to include in my selection, but it didn't work as expected.

After clicking on the column descriptor and highlighting the entire column, hold down Shift and then press Tab, ,

The time between the Press of the Tab and the press of the first needs to be short or you will start tabbing from the bottom of the screen up, but not simultaneous or you then have to do more to get it to work.

If you then continue to hold the Shift button down you can now start removing cells from your list of highlighted cells from the top down by using .

share|improve this answer

Right-click and choose Hide on the rows you don't want to get selected and then select whole columns.

share|improve this answer
1  
There is no such thing as "Hide on the rows" context menu option also, even if the rows are hidden, the contents will still be selected. – Peachy Nov 9 '12 at 10:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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