How to Select a Range of Cells in an Excel Formula (4 Methods)

Dataset Overview

Let’s consider an Excel worksheet that contains information about various outlets of a chain restaurant across the United States. The worksheet includes sales amounts for each of these restaurants during the months of January and February. Our goal is to sum up these sales amounts using different methods for selecting cell ranges.

Method 1 – Select a Range of Adjacent Cells in an Excel Formula

Suppose we want to sum up the sales amounts for the month of January. Specifically, we want to sum the adjacent cells in the range C5:C9. Here’s how we can achieve this using the Excel SUM formula:

Enter the SUM function in cell C11. Excel will prompt you to specify the range of cells to sum.
Select the first cell in the range (C5).
Drag the fill handle downward to include all cells in the range, stopping at cell C9 (the last cell in the range).

Alternatively, you can hold down the SHIFT key and use the DOWN ARROW key to select all cells below C5.

Press ENTER to calculate the total sales amount for January.

We can also sum up the adjacent cells in a row. Let’s say we want to sum up all the sales amount of the Nashville outlet for both the months of January and February. That means, we want to sum up the adjacent cells of the range C5:D5.
Select the first cell of the range, C5.
Press the RIGHT ARROW until it reaches the last cell of the range, D5.

Alternatively, we can also drag the fill handle to the right to select all the cells in the range up to cell D5 (the last cell of the range).

Press ENTER.
We will get the total sales amount of Nashville.

Method 2 – Inserting a Range of Non-adjacent Cells in an Excel Formula

We can also sum up non-adjacent cells using the Excel formula. For example, let’s sum the sales amounts for the Nashville, Atlanta, and Seattle outlets in February (cells D5, D7, and D9). Follow these steps:

Enter the SUM function in cell D11.
While writing the function, hold down the CTRL key and select the desired cells (D5, D7, and D9).

Press ENTER to get the total sales amount for these outlets in February.

Method 3 – Selecting an Entire Column or Row in an Excel Formula

Sometimes we need to sum an entire column or row in Excel formulas. Here’s how to do it:

To select an entire column (e.g., Column C), click on the column header.

To select an entire row (e.g., Row 7), click on the row number (7).

Method 4 – Combining the SUM and INDEX Functions to Define a Range in Excel

In addition to the previous methods, we can use the INDEX function to define a custom range for an Excel formula. Let’s explore how to use the INDEX function to sum up all the sales amounts for both January and February. Follow these steps:

Enter the following formula in cell D11:

=SUM(C5:INDEX(C5:D9,G6,G7))

Formula Breakdown:

Index function returns a value or reference of the cell at the intersection of a specific row and column, within a given range.
In our case, the range of cells for the INDEX function is C5:D9.
The row number is 5 (G6) and the column number is 2 (G7).
The cell in the 5th row and 2nd column for this data range (C5:D9) corresponds to cell D9.
Therefore, the range for the SUM function will be C5:D9, allowing us to sum up all the sales amounts for both January and February.

Press ENTER to calculate the total sales amounts.

Quick Notes

If you encounter a #REF! error while using the INDEX function, it means you’ve passed a row_num argument higher than the existing row numbers in the range.
Similarly, passing a col_num argument higher than the existing column numbers will also result in a #REF! error.
Exceeding the existing area numbers with the area_num argument will yield a #REF! error.