89

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.

3
  • 1
    May I suggest changing the accepted answer to BBK's largely overlooked answer? While the solution of manually entering the maximum number of rows, and the currently accepted answer of how to quickly select from a given cell to the last row, both do work, they're kludges that aren't portable across all existing versions and probably won't be portable to future versions. BBK's answer gives a formula that actually means "from the specified cell to whatever the last row may be", and allows the spreadsheet to be seamlessly ported across all versions, past and future.
    – Adi Inbar
    Jan 17, 2019 at 23:34
  • @AdiInbar I prefer dkusleika's answer because it accounts for Text and doesn't limit you to Numbers. You may need to use a Range for Conditional Formatting where you want to highlight any Values not equal to a Specific String - without causing a false-positive match on the Header Name because you selected the entire Column.
    – MikeTeeVee
    Aug 1, 2019 at 6:37
  • 1
    @User1973: For bounty, you should state what's wrong with the existing answers.
    – harrymc
    Aug 6, 2020 at 11:47

13 Answers 13

92

Click on the first cell you want to be selected and then press Ctrl + Shift + to select a block of non-blank cells, or a block of blank cells (including the first non-blank cell below it), downwards.  Press again to extend the selection through further blocks.

This may cause the top of the worksheet to scroll off the screen.  Press Ctrl+Backspace to scroll back up quickly.

5
  • 8
    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. Dec 28, 2009 at 11:35
  • 5
    For Excel for Mac users, this is Command+Shift+DownArrow Nov 6, 2015 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. Mar 2, 2016 at 18:00
  • Added Mario's comment to the answer. Apr 28, 2018 at 2:46
  • I am concerned that applying data validation to every single row in the column will increase the size of the file. How much of a problem would that be?
    – ahorn
    May 23, 2023 at 12:09
25

Universal

=SUM(A2:INDEX(A:A, ROWS(A:A)))

or

=SUM(A:A) – A1

Both will return summary for "column A", except for header in A1

4
  • 1
    The weakness of the second one is that if the formula is in the same column (which I think is probably the most common use case for this issue - wanting a sum of the column to appear at the top), it creates a circular reference. I think the first one, however, is the best answer and should be the accepted answer. The solutions of manually entering the maximum number of rows or of quickly selecting to the end do work, but they're kludges that aren't portable across all past and future versions. This answer gives a formula that actually means "from the specified cell to the end of the column".
    – Adi Inbar
    Jan 17, 2019 at 23:24
  • 1
    I'm genuinely curious how this answer has 5% of the accepted answer's score. This answer is just so much better.
    – pateksan
    Jan 16, 2020 at 14:50
  • 1
    I get an error when I try to do this for data validation: i.stack.imgur.com/YS2u6.png
    – User1974
    Aug 5, 2020 at 19:55
  • Try to use a semicolon instead of a comma in the formula if having issues.
    – sibidiba
    May 21, 2021 at 10:34
12

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

2
  • 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. Nov 9, 2012 at 11:34
  • 4
    I agree, but once you name it, you don't have to remember the row numbers.
    – Peachy
    Nov 9, 2012 at 12:20
11

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.

3
  • 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, 2012 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, 2013 at 16:48
  • This is perfect for the Conditional Formatting of an entire Column. Say I want to match on any String that does NOT have the Value "Complete" and color it Red while excluding Blank-Cells (other values could be "Scheduled", "In Progress", "Paused", etc...). I'd use the Rule: =AND(ISBLANK(D2)=FALSE,D2<>"Complete") and the Range: =$D$2:$D$1048576 to Exclude my Header Column in D1. This allows me to create a Reusable Template with Conditional Formatting in Place to Paste in however many Rows I may have without adjusting the Range for all my Rules, every time, on every sheet.
    – MikeTeeVee
    Aug 1, 2019 at 7:04
7

If you wanted to exclude Header which I assume to be at row one, you can do this:

A2:A

For exmaple:

=SUM(A2:A)

which will include A2 and also everything after that in Column A.

Hope this answer help you.

Edit: I did this in Google sheets, and it doesn't work in Excel.

4
  • 2
    I figured this would be usefull but when I try it in excel it don't work at all. Is there some specific version of excel? (I use office 365)
    – Griffin
    Oct 2, 2019 at 6:37
  • Hi Griffin, thanks for the comment. I forget to mention that I did it in Google sheets. I dint have excel in my macbook, so I can't test whether it is working in excel. Sorry if I disappointed you.
    – TienPing
    Oct 4, 2019 at 4:58
  • 2
    Okay, that explains why i could not get it to work, and I don't really think it's relevant for the person asking as the question was about excel. But it's a darn nice feature I didn't know existed and I will use in my google sheets, so thanks for that. Hopefully microsoft will pick this up and include it in future versions of excel :)
    – Griffin
    Oct 4, 2019 at 6:31
  • Glad to hear that, do update us on whether it work on your google sheets after you try it. Curious to know the result as well
    – TienPing
    Oct 4, 2019 at 7:05
2

You can use the Ctrl Shift function and instead of using the to scroll through to the end you can just hit End and then one time and you will be at the end of the sheet instead of holding the forever ever.

You can also use the F5 function as well by hitting Ctrl+G then typing in whichever cell number your range starts in, ex: B5. It will take you to that cell. Once highlighted hit Ctrl+G again and type in any cell number in the range you want selected, ex: B7024 and BEFORE you hit Enter, hold down Shift and that exact range will be selected.

Or you can just exclude headers when doing data validation. Also, to select all cells if you go into A1 and hit Ctrl A once. It will select cells with data only, this is good to avoid blanks.

1

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 .

1

Some of the answers here are good and some are wrong... If you want a more complex, but more flexible answer, here is my solution.

For example, to start from the 3rd row of the column C, you can use this

=SUM(OFFSET(C3,0,0,LOOKUP(2,1/(NOT(ISBLANK(C:C))),ROW(C:C))))

Where C3 is the first cell to be counted and both C:C from the last part are referring to the column you want to specify. The first 0 is if you want to shift the first (starting) cell down to another row and the second 0 is to shift the starting cell to another column. For example, if you still want to go for C3, but you want a more general example, you can use this (starting cell A1 + 2 rows + 2 columns = C3):

=SUM(OFFSET(A1,2,2,LOOKUP(2,1/(NOT(ISBLANK(C:C))),ROW(C:C))))

Success!...

0

An alternative method of selecting an entire column with the exception of the top cell is to use OFFSET to shift the range down by one cell. However, it is essential to prevent the range of the formula from overrunning the bounds of the sheet. Fortunately, OFFSET includes the facility to specify not only the shift but also the length of the range:

use OFFSET(A:A,1,0,COUNTA(A:A)-1)

e.g. SUM(OFFSET(A:A,1,0,COUNTA(A:A)-1))

Of course, this approach will also work for skipping more than one row at the top:

e.g. SUM(OFFSET(A:A,3,0,COUNTA(A:A)-3))

0

Write a VBA script that can be (de)activated with a shortcut.

Details of the VBA script:

  • The VBA script understands when a full column is selected and adds the last small step of taking away the x first cells.

  • Or the VBA script assigns names to the needed addresses of all filled columns, so that the mere column letter in the name field will select what you need, see @Peachy, or you just build the address automatically when you click on the column and afterwards press a key.

  • You might also be able to adjust the VBA script to mark everything below the frozen window line.

  • There are surely a lot more options using answers from here in VBA scripts.

0

Currently, if you are trying to select a column for a formula, it is as simple as using the term A2:A, or something similar.

In general, this can be used to select one or more columns, excluding some header space at the top, by including one of the top corner cells, followed by a colon, then the column at the other end of the range (this would be the same column if you are only trying to select one). For example, if I wanted the average of column C, but didn't want to include the two header cells at the top, I'd use =AVERAGE(C3:C), because C3 is the top cell I want to select. If I wanted the sum of columns B-E from row 3 down, I'd use =SUM(B3:E).

I believe this is a rather recent change, hence why the older answers are more complicated, but it makes this much more convenient.

1
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    May 28, 2022 at 16:37
-1
  • Choose the header field that you want to exclude from Data Validation rule
  • From the Menu choose Data -> Data Validation -> Data Validation
  • Press "Clear All" button
  • Press OK button

Now your header field will not have the data validation that was applicable for the rest of the column fields

1
  • 1
    -1. The question explicitly states: "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 it possible to do the correct selection in the first step to avoid this second one". It looks like your solution is exactly what the OP wants to avoid. Correct me if I'm wrong. Aug 28, 2018 at 16:50
-2

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

1
  • 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, 2012 at 10:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .