7 Best Ways to Select Multiple Cells in Excel

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

Select non adjacent cells using mouse
Select non-adjacent cells using 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.

Add or remove selection
Add or remove selection

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

Selecting multiple adjacent cells using mouse
Selecting multiple adjacent cells using 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.

Highlight adjacent cells manually
Highlight adjacent cells manually

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

Select cells with 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:

Keyboard ShortcutsOutcome
Shift + End + Down ArrowSelects the entire column up to the last value from the current cell
Shift + End + Right ArrowSelects the entire row up to the last value from the current cell
Ctrl + Shift + HomeSelects all the adjacent cells toward A1
Ctrl + Shift + EndSelects all the adjacent cells toward the end of the dataset
Ctrl + Shift + Down ArrowExtends the selection to the last non-empty cell in the column
Ctrl + Shift + Right ArrowExtends the selection to the last non-empty cell in the row

Select All the Cells in the Worksheet

Select all cells with values
Select all cells with values

On your Excel worksheet, press the Ctrl + A keys to select all the non-empty cells.

Select entire worksheet
Select entire worksheet

If you want also to select the blank cells, press the Ctrl + A keys again to highlight the whole worksheet.

Cell all cells in the range
Cell all cells in the range

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.

Creating a name
Creating a name

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.

Multiple named ranges
Multiple named ranges

Repeat the steps a few times to create multiple Named Ranges.

Selecting multiple cells using named ranged
Selecting multiple cells using named ranged

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.

Name Box to select non adjacent ranges
Name Box to select non-adjacent ranges

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.

Select adjacent and non-adjacent cell ranges using Go To
Select adjacent and non-adjacent cell ranges using Go To

Similarly, you can enter multiple adjacent or non-adjacent cell range references to highlight those in your Excel dataset.

Selecting multiple cells using Go To
Selecting multiple cells using Go To

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:

Option Explicit

Sub SelectAndHighlightCells()
    Dim selectedRanges As Range
    Dim userInput As Variant
    Dim cellRange As Range

    ' Loop until the user clicks "Cancel"
    Do
        ' 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
            Else
                Set selectedRanges = Union(selectedRanges, cellRange)
            End If
        Else
            ' Inform the user about the invalid input
            MsgBox "Invalid cell reference or range. Please try again.", vbExclamation
        End If
    Loop

    ' Highlight the accumulated selected ranges
    If Not selectedRanges Is Nothing Then
        selectedRanges.Select
        ' Inform the user about the selected ranges
        MsgBox "Selected Ranges: " & selectedRanges.Address, vbInformation
    End If
End Sub

Here are the steps you can follow to create a macro using the above script:

Creating VBA macro
Creating VBA macro
  1. Open your Excel workbook and press the Alt + F11 to bring up the Excel VBA Editor.
  2. Click Insert on the toolbar and choose the Module option.
  3. In the new module, enter the above script.
  4. Hit the Save button to save the script.
Microsoft Excel
Microsoft Excel
  1. You should see the Microsoft Excel dialog.
  2. Click Go back to find the Save As dialog box.
Save as XLSM
Save as XLSM
  1. Select Excel Macro-Enabled Workbook (XLSM) from the Save as type drop-down menu.
  2. Click Save to save the changes you made.
  3. Close the Excel VBA Editor.

To run the Excel VBA Macro, follow these steps:

Macro dialog
Macro dialog
  1. Press Alt + F8 to launch the Macro dialog.
  2. Select the SelectAndHighlightCells macro.
  3. Click Run to execute the script.

Conclusions

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.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃