How to Select a Range of Cells in 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. Below, I’ll explain four methods:

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:

• Start by entering 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.

Read More: How to Select Specific Data in Excel

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:

• Start by entering the following formula in cell D11:

• 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.
• Lastly, exceeding the existing area numbers with the area_num argument will yield a #REF! error.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to Select Range in Excel | Excel Range | Learn Excel