10

In Excel, you can select an entire row by clicking its number at the left edge of the window. But doing so selects the entire row all the way to infinity. If you apply a fill color to a row selected in this way, the fill color also extends infinitely.

I need to select multiple rows which are not adjacent -- for example, rows 4, 6, 14, and 27 -- and so clicking and dragging a box won't help.

So, is there some easy way to select JUST to the end of columns containing data?

2
  • 3
    If you are applying fill color, is conditional formatting a possible way to accomplish your goal? May 18, 2011 at 16:06
  • That's a good suggestion, but not in this case -- I have a list of web pages, and need to highlight a specific group of those pages. They're identified by page title, not URL, and don't share any common data I could use as a hook for conditional formatting. So I've basically concluded I'll have to do it manually. Oh well. It'll be tedious, but shouldn't take more than an hour. May 18, 2011 at 16:34

7 Answers 7

6

No, there is no easy way of selecting multiple non-continuous rows without selecting the entire row of each. You would need to do each row independently.

Using Just the Keyboard

If your rows contain consecutive cells with data, you can click the first cell, hold ctrl and shift and press the right arrow key to select the relevant area. If any cells in these rows are empty, you will only be able to select up to that point (unless you press the arrow key again).

Using the Keyboard and the Mouse

Per music2myear's edits: You can select non-contiguous regions by holding ctrl while clicking cells with your mouse. This would allow you to apply formatting to multiple regions simultaneously.

3
  • Yes, I read the question and gave what I consider to be a best fit answer - it does not meet ALL the criteria but it meets half of it and there is no simple trick to meet all the requirements.
    – Linker3000
    May 18, 2011 at 16:27
  • 1
    @Linker3000 - his question is how "to select JUST to the end of columns containing data" not "How do I select multiple rows".
    – variant
    May 18, 2011 at 16:29
  • My original answer and Linker3000's dealt with this as well. It could be applied to selecting whole rows as easily as it could be for selecting only the desired data rows. And yes, you can select multiple non-continuous and non-adjacent rows of data. You just have to use the mouse while holding down CTRL. May 18, 2011 at 16:34
3

If you know the header of the last column, you can use the Name Box (the text box located to the left of the Formula bar).

For example, if your data ranges from column A to J, you can select the rows you mentioned (4,6,14,27) without using the mouse or cursor keys by typing this into the Name box:

A4:J4,A6:J6,A14:J14,A27:J27

The letters don't have to be uppercase. I don't know of any shortcut to the Name Box so you'll have to use your mouse to click it. Just that once. :D

Press Enter and those ranges will be selected.

You'll get the same result when you use that range on the Go To dialog box, which you can open by pressing Ctrl-G

1

Hold the CTRL key while clicking on each successive desired row. CTRL allows multiple non-adjacent selections. Shift allows multiple adjacent selections. These keys work on files in a file manager and in most applications.

EDIT: CTRL-arrow allows selection to the end of the current data row.

To use this along with CTRL for multiple non-adjacent selections you can use your mouse click-drag select and hold down the ctrl key while making selections.

Or you can select the first cell of the first column of data you desire to select, press SHIFT-CTRL-Arrow (direction of the end of the row or column of data. Keep hold CTRL but release SHIFT (playing piano helps with this), mouse click the first cell of the next desired row (or column) of data, re-press SHIFT-Arrow (still holding down CTRL) to select to the end of that data column. Etc.

Unless you are comfortable doing complex keyboarding, I'd use the hold CTRL, Mouse click-drag select method.

3
  • I'm going to upvote this for taking the time to edit and expand on the answer after realizing that the initial version didn't fully address the question. May 18, 2011 at 16:38
  • I appreciate that. Did that expansion of the answer assist any more with your specific issue? It sounded as though you still weren't quite completely happy with @variant's answer, which this is pretty much the same as. May 18, 2011 at 18:31
  • I just had to do it manually with control click drag in the end. May 18, 2011 at 20:53
0

Hold down the CTRL key as you click and you can select multiple rows as you wish.

0

The question and the OP's subsequent comment reply specify that the end goal is to visually format only the cells in a row that contain text, and not apply fill color to the entire row. The question of "how to do a selection" appears to me to be aimed at achieving that goal, and does not appear to be the goal in and of itself. I'll offer a solution that accomplishes the end goal.

I find the selection behavior strange and frustrating, as well. My workaround (usually) is to change the text color instead of a) higlighting the row, or b) using the "styles" color selector. Since there is no text in the empty cells, you see no messy color where you don't want it to be.

2
  • The question asks how to do a selection. Your answer doesn't really address that (which is likely why it attracted a downvote). With a little more rep, you will be able to post comments, which is the way to add helpful, tangential information. In the site's Q&A format, answers are reserved for solutions to the question.
    – fixer1234
    Mar 16, 2018 at 22:26
  • 1
    the user's question and their subsequent comment reply specify that the end goal is to visually format only the cells in a row that contain text, and not apply fill color to the entire row. the question of "how to do a selection" appears to me to be aimed at achieving that goal, and does not appear to be the goal in and of itself. my text color solution does the thing they says they're trying to do. Mar 18, 2018 at 15:35
-1

Click the cell you want to start at - press CNTRL + SHFT + the down Arrow

It will highligt all cells from the start point until it hits a blank cell

1
  • The question asks how to select rows, and you're suggesting down arrow?  I think you mean right arrow — which was given in the accepted answer almost four years ago. Apr 24, 2015 at 4:03
-1

There is a quick way in Excel to select visible cells (or rows or columns) only.

You can apply filter to make only the rows with data visible which is easy enough. As in your example, make only rows 4, 6, 14, and 27 visible, and select all rows (e.g. rows 4 to 27 including the hidden ones).

Then select Home > Find & Select > Go to special... > Visible cells only; Alternatively use keyboard shortcut Alt+;. Note that selection changes so that only visible cells (i.e. those containing data) are selected. Apply any formatting or copying or other operations would not affect the hidden cells (i.e. those without data).

2
  • (1) You seem to be claiming to have a “quick” method.  How is hiding rows 5, 7-13, and 15-26 (and then selecting rows 4-27) any faster than just selecting rows 4, 6, 14, and 27 in the first place?  (2) The keyboard shortcut for Select Visible is (Alt)+(;), not (Ctrl)+(;).  (3) You say, “selection changes so that only visible cells (i.e. those containing data) are selected.”  What connection is there between “visible” and “containing data”?  “Visible” just means not hidden; it will include blank cells — and not selecting and affecting the blank cells is the whole point of this question. Apr 24, 2015 at 5:55
  • @G-Man (1) Applying a quick filter and uncheck the "blank" does the job easily. In the question it didn't state how many rows to select, if there are tens or hundreds of rows, applying a filter will be faster than clicking one by one (2) Thanks for the comments, I've updated the hotkey (3) after applying the filter to show cells with data only (as stated in my answer), only cells containing data are visible. This is explained in my answer.
    – Kenneth L
    Apr 24, 2015 at 8:20

You must log in to answer this question.

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