Selecting a group of cells right next to each other in Excel is simple, just click and drag or use shift and the arrow keys. But what if you need to grab cells that aren’t connected? It can seem tricky, but there are several ways to select non-adjacent cells, and most of them are fairly straightforward. Sometimes you need to format, copy, or delete content in cells that aren’t touching, and thankfully, excel gives us the flexibility to do it. Here are some ways to select those cells.
Method 1: Using the Keyboard and Mouse
Most of us are familiar with using a combination of the keyboard and mouse to select non-adjacent cells. It’s often the quickest and most intuitive method.
To do this, simply hold down the Ctrl key and then click each cell you want to select. You can also click and drag to select a range of cells while still holding the Ctrl key. Don’t let go of the Ctrl key until you have selected all the cells you need or you’ll lose your selection.
You can also select entire rows or columns this way. Just hold Ctrl and then click the column letter (like B or D) or the row number (like 5, 7, 10).
It is possible to select combinations of individual cells, full rows, and columns at once. If you accidentally select a cell or range you didn’t want, just keep Ctrl pressed and click the selected cells again to deselect them.
Method 2: Using the Name Box
The name box, located to the left of the formula bar, is another useful way to select non-contiguous cells. It usually displays the address of the selected cell or group of cells.
When you click a cell, the name box will display its cell address (like A1, B2, etc.). You can also type a cell address in the name box to select that cell.
To select non-adjacent cells, type the addresses of the cells and/or ranges that you want to select in the name box, separated by commas. For example, you might enter A5, B2:B10, D5, D7, E2, E10 then press enter.
The cells you specified will all be highlighted instantly. The name box will display the address of the last cell you specified.
The order you type the cell addresses doesn’t matter.
Method 3: Using the Find and Replace Tool
You can also select cells that contain a specific value using the Find and Replace feature. This is useful when you want to highlight cells based on the content in them.
For example, let’s say you want to select all the cells that contain the retailer type “Walmart” in the table below.
First select the data range in which you want to find specific cells, then press Ctrl + F to open the find and replace dialog. Alternatively, on the Home tab, click ‘Find & Select’ and then ‘Find’.
In the “Find what” field, enter the value you want to highlight (in this case “Walmart”), then click “Find All”.
The find and replace dialog will list all findings that match “Walmart”. Press Ctrl + A to select all of the found cells.
Then, click the ‘Close’ button on the Find and Replace dialog box, and you will see that all cells containing that word have been selected.
Method 4: Using the Go To Feature
Another way to select non-adjacent cells or columns is by using the ‘Go To’ feature.
To access it, go to the Home tab on the ribbon, click the ‘Find & Select’ button, and then select ‘Go To’. Alternatively, you can press the F5 key.
The ‘Go To’ dialog box will appear. Type the cell addresses or cell ranges you want to select, separated by commas, into the ‘Reference’ box. Then click “OK” or just hit Enter.
The specified cells will be selected.
Similar to the Name Box method, the order in which you type addresses doesn’t matter, and you can select individual cells, ranges, and rows or columns together.
Method 5: Using the Keyboard Only
If you prefer to use just the keyboard, there is also a way to select non-adjacent cells. This can be done by using the Extended Selection mode. Here’s how:
First, select the initial cell that you want to include and press F8 to enable ‘Extended Selection mode’. You’ll see a notification in the bottom left of Excel’s status bar.
Use the arrow keys to extend the selection to adjacent cells. This only selects continuous cells since Extend Selection mode is active.
Now, press Shift + F8 to disable ‘Extended Selection Mode’ and enable ‘Add or Remove Selection mode’. This mode preserves your existing selection and allows you to use the mouse or arrow keys to add more selections. Use arrow keys to move to the next cell that you want to select.
Next, press F8 again to select the next range of cells with the arrow keys, and then press Shift + F8 to return to ‘Add or Remove Selection’ mode. Repeat this process to select all the non-adjacent cells you need.
In simpler terms, F8 toggles Selection mode, allowing you to make selections, and Shift + F8 allows you to move between selections.
When ‘Add or Remove Selection’ mode is active, you can use the mouse to select cells as well.
After selecting the cells using any of the methods above, you can then edit or format them.
Entering Data in Non-Adjacent Cells Simultaneously
Often you might want to enter the same data into multiple non-adjacent cells. You can do this very easily using a keyboard shortcut.
First select the cells that you want to fill with the same data using any of the methods described above.
Start typing the value (for example, ‘Sports Goods’) into any of the selected cells.
Instead of pressing only “Enter”, press Ctrl + Enter. The value you typed will be entered into all of the selected cells at once.
This works for text, numbers, and even formulas.