Don’t know how to select multiple cells in Excel? In this article, let me tell you how.
Cells are an integral component of an Excel spreadsheet. Any data in Excel is placed within its cells. You need to select the cells to do calculations, data analysis, and visualization in Excel.
In many cases, like summing up the numbers of multiple cells, selecting individual cells at a time isn’t enough. You need to select multiple cells in Excel to do such calculations.
Yes, there are many ways to select multiple cells in Excel, no matter where the cells are positioned in your spreadsheet. But do you know the essential methods to do so?
In this article, I’ll describe the cell selection methods so you can use them conveniently.
Reasons to Select Multiple Cells in Excel
- When you need to include data from various cells for data entry or editing, you have to select multiple cells to streamline the process.
- When applying formulas, you should select multiple cells to apply to the entire selected range without any chance of human error.
- If you want to generate charts or graphs, selecting the relevant data range is necessary. This requires you to select multiple cells.
- You can select multiple cells during conditional formatting to simplify the process of highlighting specific cell values and ensure consistent formatting rules.
- Multiple cell selection allows you to perform uniform data entry across the chosen range while implementing data validation rules.
- Selecting multiple cells is crucial for accuracy When copying data within the same worksheet or across different sheets.
- If you want to apply sorting and filtering functions in Excel, you have to select multiple cells to sort or filter the data based on specified criteria.
Select Non-Contiguous Multiple Cells
Find below two methods to select non-adjacent cells in Excel:
Using the Mouse
Open your worksheet and select the first cell using a single left button click on the mouse.
Now, press the Ctrl key on the keyboard and select other cells that aren’t adjacent to the first cell.
Don’t let go of the Ctrl key until you’re done selecting multiple cells. For example, I’ve selected A2, B4, C6, and D8 in the above screenshot.
Using the Keyboard
You can’t specifically choose non-contiguous cells using your keyboard as shown in the first example. However, you can choose non-adjacent cell ranges only using the keyboard.
Here, you’ll learn how to use the Add or Remove Selection feature in Excel using Shift + F8 to highlight multiple non-adjacent cell ranges.
Access your worksheet and let go of the mouse. Use the navigational arrows on your keyboard to select the first cell or cell range.
Now, press the Shift + F8 keys once. You should then see the Add or Remove Selection notification on the Excel status bar.
Then, use the navigational arrows to go to the first cell of the next non-adjacent cell range you need to select. Press the Shift key and use navigational arrows to highlight the target cell range.
To add more non-adjacent cell ranges to the existing select, hit the Shift + F8 keys again. Then, repeat the process until you’re done selecting all the non-contiguous cells in Excel.
Highlight Contiguous Multiple Cells
Here are various ways to select multiple adjacent cells in Excel:
Using the Mouse
Suppose you need to select cell ranges
B2:C6. Highlight B2 with the mouse. Then, take the cursor on C6 and click on it. You should find all the cells selected in between.
In another scenario, you might want to select multiple adjacent cell ranges using a mouse manually. Press the Ctrl key after selecting the first cell or cell range. Keep the Ctrl key pressed and now select as many contiguous cells or cell ranges you want to select.
Using the Keyboard
When selecting multiple cells in Excel using the keyboard you must use the Shift key with navigational arrows. Shift + Right Arrow selects one cell to the right of the current row. Or, you can press Shift + Down Arrow to select one cell down the column.
Find below other key combinations to select multiple cells in Excel:
|Shift + End + Down Arrow
|Selects the entire column up to the last value from the current cell
|Shift + End + Right Arrow
|Selects the entire row up to the last value from the current cell
|Ctrl + Shift + Home
|Selects all the adjacent cells toward A1
|Ctrl + Shift + End
|Selects all the adjacent cells toward the end of the dataset
|Ctrl + Shift + Down Arrow
|Extends the selection to the last non-empty cell in the column
|Ctrl + Shift + Right Arrow
|Extends the selection to the last non-empty cell in the row
Select All the Cells in the Worksheet
On your Excel worksheet, press the Ctrl + A keys to select all the non-empty cells.
If you want also to select the blank cells, press the Ctrl + A keys again to highlight the whole worksheet.
Another way to select all the cells containing values in the current dataset is Ctrl + Shift + * (on the keyboard).
Highlight Multiple Cells Using the Name Box
The Name Box allows you to name a cell or cell range in Excel. Then, you can use the Name Box again to select the named cell or cell range instantly.
Select the target dataset to assign a name to a cell range or cell. Then, click on the Name Box and enter a custom name for the selected range.
Repeat the steps a few times to create multiple Named Ranges.
Now, click the Name Box and choose a Named Range from the drop-down menu. If you want to choose another adjacent Named Range, press the Shift key and then select another name from the Name Box.
To select non-adjacent Named Ranges, press Ctrl before selecting the second name from the Name Box.
Select Only Visible Cells
Suppose there are both hidden and visible cells in your Excel worksheet. You only want to select all the visible cells. You can do that by pressing Alt + ; keys.
Highlight Multiple Cells Using Go To
Suppose you want to select multiple non-adjacent cells throughout the dataset. Instead of using the multi-step processes you can use the Go To feature in Excel.
Firstly, go to your worksheet that contains the dataset. Press the Ctrl + G keys to launch the Go To dialog box.
On the Go To dialog, specify the cell addresses separated by commas. Hit OK on the dialog to highlight multiple non-contagious cells.
Similarly, you can enter multiple adjacent or non-adjacent cell range references to highlight those in your Excel dataset.
Moreover, you can use the Named Ranges on the Go To dialog to select those cells or cell ranges instantly. Simply, bring up the Go To dialog and type in the named cells, like flsales, nysales, txsales as shown in the above image.
Select Multiple Cells in Excel Using Excel VBA
You can also use an Excel VBA Macro to quickly select multiple adjacent and non-adjacent cell ranges.
The VBA Macro accepts input references like A:A, Named Ranges, cell ranges like A2:D11, and so on. For multiple references, you need to separate the entries using commas.
Here’s the script you can use:
Dim selectedRanges As Range
Dim userInput As Variant
Dim cellRange As Range
' Loop until the user clicks "Cancel"
' Prompt user for input
userInput = InputBox("Enter cell reference or cell range (e.g., A1 or A1:B10):", "Cell Selection")
' Exit loop if user clicks "Cancel"
If userInput = "" Then Exit Do
' Validate and set the cell range
On Error Resume Next
Set cellRange = Range(userInput)
On Error GoTo 0
' Check if a valid cell range is selected
If Not cellRange Is Nothing Then
' Accumulate selected ranges
If selectedRanges Is Nothing Then
Set selectedRanges = cellRange
Set selectedRanges = Union(selectedRanges, cellRange)
' Inform the user about the invalid input
MsgBox "Invalid cell reference or range. Please try again.", vbExclamation
' Highlight the accumulated selected ranges
If Not selectedRanges Is Nothing Then
' Inform the user about the selected ranges
MsgBox "Selected Ranges: " & selectedRanges.Address, vbInformation
Here are the steps you can follow to create a macro using the above script:
- Open your Excel workbook and press the Alt + F11 to bring up the Excel VBA Editor.
- Click Insert on the toolbar and choose the Module option.
- In the new module, enter the above script.
- Hit the Save button to save the script.
- You should see the Microsoft Excel dialog.
- Click Go back to find the Save As dialog box.
- Select Excel Macro-Enabled Workbook (XLSM) from the Save as type drop-down menu.
- Click Save to save the changes you made.
- Close the Excel VBA Editor.
To run the Excel VBA Macro, follow these steps:
- Press Alt + F8 to launch the Macro dialog.
- Select the SelectAndHighlightCells macro.
- Click Run to execute the script.
While working on Excel, you need to select different cells. While selecting the adjacent cells are super-easy, selecting non-adjacent cells could be tricky at time, especially if you’re a newbie in using Excel spreadsheet.
As I’ve listed some effortless and quick methods of multiple cell selection, you can stop worrying about how to select multiple cells in Excel. Pick the approach you liked most or that suits your situation and start implementing the steps.
When done, you may share your experience of working with these methods with your fellow readers in the comment section. You can also tell us if there are other methods that I may have missed.