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.
Josh says
I’ve been waiting for this for years! When do you think they’ll roll this out to normal users?
Jon Peltier says
Josh –
I think it’s only been around for a month or so. Insider Fast subscribers get a new build about every week, so it is hard to keep track of when features appear. Regular Office 365 subscribers get their own new build every month, three months, or six months, depending on their options or what their IT department has imposed (if I understand it correctly). Perpetual license holders (i.e., Excel 2016 but not Office 365) get security fixes and a few other features, such as the Treat #N/A as Blank Cells I wrote about last week, but most new features are reserved for Office 365 subscribers.
derek says
Would you say the blessed coming of chart data labels based on worksheet ranges [*] is also a UserVoice success story? If so, hats off to them.
[not in any version my employers have yet bought the licence for, but at least I know it’s now in the pipeline]
Jon Peltier says
Derek –
‘Data labels from ranges’ was introduced in Excel 2013, so it predates UserVoice. But you might say it served as a template, since users have voiced their desire for this feature since about 1995.
PATRICK says
Hello,
is it available for Excel 2016 (not 365 version) ? [ Excel French, i’m in Belgium :) ]
Patrick
ps: a tip–> whern you have a range contiguously selected, make CTRL “.” (dot) it’s very useful :)
Jon Peltier says
Patrick –
So far this feature is only available in Office 365, I think only for the Insider builds and not the mainstream Office 365 licenses. I don’t see it on my other laptop, which has non-Office-365 Excel 2016.
Olaf (MSFT) says
Thanks for the nice review. I am the PM of the feature. To answer the Q’s, It should now be available for all Office 365 subscribers (depending on how quick you have said you want to get updates. This can be anywhere from 1 month to 6 months and sometimes 12 months). It won’t be available for non-subscription like 2016.
Jon Peltier says
Olaf –
Thanks for chiming in, and thanks for implementing the feature.
Robert Lalonde says
Hi Jon,
I would not have even known about this enhancement without this blog.
Merci
Bob
Jon Peltier says
Bob –
De rien. I found it by accident, and I’m just happy to let people know.
Paul Croteau says
Jon,
I happened upon this particular topic as I occasionally will look up certain obvious Excel functionality that I wish would make their way into Excel. Low and behold you have demonstrated the unselect capability of selected cells, something that appears to be still only available to a “Select” few…(pun intended). What gives? Do you think enjoys frustrating its captive audience of users?