Ctrl+Select In Excel
When selecting cells and areas in an Excel worksheet, you can select one cell or area with the mouse, then hold the Ctrl key while selecting another cell or area, and the new selection is added to the previous selection. You can select multiple areas at once using this Ctrl+Select sequence.
This is great, until you select one or more cells you didn’t mean to select, or if you just want to deselect something you’ve selected. In Excel worksheets, you can’t use the Ctrl key to unselect something that you’ve selected previously. This is a pain, because you have to start again with your multiple region selection, and hope you don’t mess it up again.
This has been the behavior since Excel 1950 for Univac was introduced.
Ctrl+Select Everywhere Else
In every other application I can think of, when you are selecting objects, you can use Ctrl+Select to add to the collection of selected objects. And if you Ctrl+click on a selected object, it is deselected. This works on objects in PowerPoint, shapes embedded in Word, files and folders displayed in Windows Explorer.
People have grumbled about the inability to deselect a cell in Excel for a long time, and it even has received an entry in Excel UserVoice: Unselecting cells when using Ctrl to select multiple cells.
The New Ctrl+Select Behavior in Excel
Well, the Excel Team was listening, and not long ago they announced that it is possible to Deselect a selection in Excel. According to that announcement, it’s still only available to Insiders, who risk all to get the latest builds with the latest and greatest new features and the occasional “Gotcha!” Read about Office Insiders and What is Office Insider? on the Microsoft Office web site.
I knew that this suggestion had been made in UserVoice, and I might even have heard that Microsoft was working on it. So one day, when I was tooling away as usual, I incorrectly selected some cells, and thought to myself, “When will they ever let me Ctrl+Select to unselect that stupid cell over there?” And I Ctrl+Selected that stupid cell over there, and it was UNSELECTED! Naturally I didn’t really believe it, so I Ctrl+Selected and Ctrl+Deselected that cell about twelve times.
It’s such a seemingly small change, but it removes a frequent and annoying source of frustration.
How Ctrl+Select and Ctrl+Deselect Work
Suppose I have a range of data like that below left, and for some reason I want to select the header row and the rows with even X values. I start by selecting the header row and the row with X = 0.
Now I hold Ctrl and select the row with X = 2 (below left). That’s how Ctrl+Select works.
Then I hold Ctrl and select the row with X = 4, except I’m clumsy and also select the row with X = 5 (below right).
Ordinarily I would just curse at my poor mouse-eye coordination, and go back to the first selection above. But now I can curse, then hold Ctrl and select the row with X = 5. Or rather, DESELECT the row with X = 5 (below left); the resulting selection is shown below right. That’s how Ctrl+Deselect works.
Now I can Ctrl+Select the row with X = 6 (below left), and do what I want with the selected range, which apparently was shading the cells light gold (below right).
Want More Examples?
So I was playing around to suss out the behavior more fully, and I present a few more examples. They all start with a simple rectangular range selected, C2:G6.
Below left, I’m holding Ctrl while selecting part of the previously selected range, C4:E6. The resulting selection is comprised of the two rectangular areas C2:G3 and F4:G6.
So I learned if the cell you click at the beginning of a Ctrl+Select operation is selected, you will deselect any cells in the range you are currently selecting. I selected B3:F7, starting within the previously selected range at cell F3 (shown below left), so all cells in B3:F7 end up not selected (below right).
If the cell you click at the beginning of a Ctrl+Select operation is not selected, you will select all cells in the range you are currently selecting, and no cells become deselected. I selected B3:F7, starting outside the previously selected range at cell B3 (shown below left), so all cells in B3:F7 end up selected, overlapping the original selection of C2:G6 (below right).
If I then Ctrl+Select D4:E5, which is completely enclosed by the previous selection, that small square region is excised from the selection.
The new selection is easier to see if I then shade the selected cells below.
Finally, it’s impossible to unselect all cells on the active sheet. Well, you can, if you select an object on the sheet, such as a shape, a picture, or a chart. But an active worksheet has an active cell.
Below left, C2:G6 was selected, and I am Ctrl+Selecting the same range, from the bottom left corner to the top right. The new active cell is C6, the first cell of my last Ctrl+Select.
Pretty cool, isn’t it?
A Plug for Excel UserVoice
This is the third UserVoice success story I’ve written about, joining Plot Blank Cells and #N/A in Excel Charts and User Voice Fixes Pivot Table Default Settings. There have been several other UserVoice suggestions implemented by the Excel team, as well as numerous being planned and under review.
UserVoice is a great way to make suggestions about issues or new features in Excel. Don’t just start by making a suggestion, though. Search for your idea, and if it’s already there, vote for it and add a comment. Ideas are grouped by platform (Windows, Mac, Online, etc.) and by topic (Charting, Formatting, Pivot Tables, etc.). Among other criteria, when evaluating user feedback, the team looks for ideas with lots of votes.