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

While using formulas in Excel, we have to insert a specific range of cells to make the formula work or get the desired result. In this tutorial, I will show you how to select a range of cells in Excel formula in 4 easy and simple easy ways. We will use the Fill Handle, SHIFT, CTRL key, and the INDEX function to select a range of cells in the Excel formula.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

4 Easy Ways to Select a Range of Cells in Excel Formula

Let’s assume we have an Excel worksheet that contains the information of various outlets of a chain restaurant all across the United States. The Excel worksheet contains the sales amounts for each of these restaurants for the month of January and February. We will sum up these sales amounts by selecting the range of cells in different ways. The image below shows the worksheet we are going to work with.

Method 1: Select a Range of Adjacent Cells in Excel Formula

Let’s assume we want to sum up all the sales amount for the month of January. That means, we want to sum up the adjacent cells of the range C5:C9. Let’s see, how we can select this range of adjacent cells in the Excel SUM formula.

Step 1:

• First, we will write down the SUM function in cell C11. While writing the function, Excel will ask for the range of the cells that it will sum up. We will select the first cell in the range C5.
• Then, we will drag the fill handle downward to select all the cells in the range. We will release the fill handle when it reaches cell C9-the last cell of the range.

• Alternatively, we can also use the SHIFT key to select all the adjacent cells of the range. First, we will select the first cell of the range C5. Then, we will keep pressing the DOWN ARROW key until it reaches the last cell of the range. As we press the DOWN ARROW key, it will select all the cells below C5.

• When we get the range of cells, we will press the ENTER key. Upon pressing the ENTER, we will get the total sales amount for the month of January.

Step 2:

• We can also sum up the adjacent cells in a row. For example, 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.
• First, we will select the first cell of the range C5. Then, we will press the RIGHT ARROW key until it reaches the last cell of the range D5.

• Alternatively, we can also drag the fill handle rightward to select all the cells in the range. We will release the fill handle when it reaches cell D5-the last cell of the range.

• When we get the range of cells, we will press the ENTER key. Upon pressing the ENTER, we will get the total sales amount of the Nashville.

Method 2: Insert a Range of Non-adjacent Cells in Excel Formula

We can also select the non-adjacent cells in the Excel formula. For example, we will sum up the sales amount of the Nashville, Atlanta, and Seattle outlets for the month of February. That means, we want to sum up the cells D5, D7, and D9. We will follow the below steps.

Steps:

• First, we will write down the SUM function in cell D11. While writing the function, Excel will ask for the range of the cells that it will sum up.
• Then, we will hold down the CTRL key and select the cells that we want to sum up.

• When we get the range of cells, we will press the ENTER key. Upon pressing the ENTER, we will get the total sales amount of the Nashville, Atlanta, and Seattle outlets for the month of February.

Read More: How to Select Specific Data in Excel

Method 3: Select an Entire Column or Row in Excel Formula

Sometimes we may need to insert the entire column or row in the Excel formulas. We can select the entire column or rows following the below steps.

Steps:

• We can select Column C by clicking on the column header like below.

• We can also select Row 7 by clicking on the row number like below.

Method 4: Combine the SUM and INDEX Functions to Define a Range in Excel

We can also use the INDEX function to define a range for an Excel formula. For example, we will use the INDEX function to define a range that will sum up all the sales amounts for both the month of January and February. We will do the following.

Steps:

• We will write down the below formula in cell D11.

• Upon pressing the ENTER, we will get the total sales amounts.

Quick Notes

• You will see a #REF! error while using the INDEX function, if you pass a row_num argument higher than the existing row numbers in the range.
• Also, if you pass a col_num argument higher than the existing column numbers in the range, you will see a #REF!
• Finally, if the area_num argument is higher than the existing area numbers, you will get a #REF!

Conclusion

In this article, we have learned how to select a range of cells in Excel formula. I hope from now on you can select a range of cells in Excel formula easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!

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