Normally, when you need to select multiple cells which are not next to each other, you press and hold the Ctrl key and then select each cell with the mouse.
Right?
That’s the simple method. But I bet you have never heard about selecting non-contiguous cells without using a mouse.
The reason for this is that very few people know this method. Luckily, I found it.
But here’s the kicker: There’s also a way which you can use to select non-contiguous cells [non-adjacent] without using the Ctrl key.
In today’s post, I’m going to show you exactly two different ways to select non-adjacent cells.
1. Without Using Ctrl Key
Just follow these simple steps to do it.
- Select the cell from where you want to start your selection.
- Press Shift + F8 once.
- You get a message “Add To Selection” on your status bar – bottom of your Excel window.
- Now, you can select your cells just by clicking on them.
- Now, you can do anything with your selection (formatting, deleting, etc.).
- Press Esc or Shift + F8 again to turn off the Add to Selection mode.
2. Without Using a Mouse
To select non-contiguous cells without using your mouse, follow these simple steps.
- Select the first range with Shift + arrow keys and then press Shift + F8 to activate the “Add To Selection” mode.
- Navigate with the arrow keys to the start of the next range you want to include, press F8 to extend the selection with the arrow keys, and press Shift + F8 again to add another range.
- And, so on.
Note: While selecting a non-adjacent range without the mouse, adding single cells to your selection doesn’t work well with this method; it works best for ranges.
3. Using the Name Box
If you know the addresses, type them in the Name Box (left of the formula bar) separated by commas, like A1,C3:C10,E5, and press Enter. Excel selects all of them at once. You can do the same with the Go To dialog box (F5).
Deselect a Cell from the Selection
In Microsoft 365, if you select a cell by mistake, hold Ctrl and click it again to remove it from the selection. In older versions, you need to start the selection again.
What You Can’t Do with Non-Adjacent Cells
You can format, clear, or enter the same value in all the selected cells (type the value and press Ctrl + Enter). But copying is limited: Excel can copy non-adjacent ranges only when they are in the same rows or the same columns. Otherwise, it shows the message “This action won’t work on multiple selections.”
Frequently Asked Questions
What is the shortcut to select non-adjacent cells?
Hold Ctrl and click the cells, or press Shift + F8 to turn on Add to Selection and then click the cells without holding any key. On a Mac, hold Command and click the cells.
How do I enter the same value in non-adjacent cells?
Select the cells, type the value or formula, and press Ctrl + Enter. Excel enters it in all the selected cells at once.
Why can’t I copy my selection?
The ranges are not in the same rows or columns, so Excel shows “This action won’t work on multiple selections.” Copy them one by one, or select ranges that line up.
Do you know which keys to press on a Mac? I have Excel for Windows installed on my Mac using Parallels Desktop, but I am using a Mac keyboard. Thanks!
This is great. I’ll never use Ctrl ever again for selecting non-adjacent cells
This is a neat trick that I have not seen before in over 20 years of Excel use. However, you say that your second method “Without Using Mouse” doesn’t work for single cells. It does. The trick is to realise that each press of Shift-F8 toggles you in and out of “Add To Selection” Mode so, for each cell you want to select after the first, press Shift-F8 twice. The first press takes you out of Selection Mode with the present selection and the second one puts you back into it allowing you to move using the keyboard again. Thank you for publishing this though!
thank you for this trick 🙂
but is there any way to cut and delete those non-contiguous cells ?
I dunno
Please do not give so much importance to yourself and claim that all of this has been introduced by you.
BTW good work!
Excellent tricks & useful for office use
,عارف وحید کشمیری Thanks
Hey what a wonderful tip that I have never come across! Great stuff 🙂
John Michaloudis
Hey John, Welcome to Excelchamps.
When a master praise you, its an inspiration.
Thanks for your words.
Love
PG