Selecting whole column except first X (header) cells in Excel
Selecting whole column except first X (header) cells in Excel
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?
"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."
"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 ↓."
"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."
"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))))
