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.

How to Select a Range of Cells in Excel Formula


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.

Select a Range of Adjacent Cells in Excel Formula

  • 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.

Select a Range of Adjacent Cells in Excel Formula

  • 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.

Select a Range of Adjacent Cells in Excel Formula

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.

Select a Range of Adjacent Cells in Excel Formula

  • 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.

Select a Range of Adjacent Cells in Excel Formula

  • 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.

Select a Range of Adjacent Cells in Excel Formula

Read More: How to Select a Range of Cells in Excel (9 Methods)


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.

Insert a Range of Non-adjacent Cells in Excel Formula

  • 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.

Insert a Range of Non-adjacent Cells in Excel Formula

Read More: How to Move Selected Cells in Excel with Keyboard (4 Methods)


Similar Readings


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.

Select an Entire Column or Row

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

Select an Entire Column or Row

Read More: Select All Cells with Data in a Column in Excel (5 Methods+Shortcuts)


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.
=SUM(C5:INDEX(C5:D9,G6,G7))

Formula Breakdown:

  • Index function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
  • Here, 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) is cell D9.
  • So, the range for the SUM function will be C5:D9. Hence, the SUM function will sum up all the sales amounts for both the month of January and February.

SUM and INDEX functions

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

Combine the SUM and INDEX functions to Define a Range in Excel

Related Content: How to Shift Cells Down in Excel without Changing Formula (4 Methods)


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!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo