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

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:

How to Select a Range of Cells in Excel Formula


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

Select a Range of Adjacent Cells in Excel Formula

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

Select a Range of Adjacent Cells in Excel Formula

  • Press ENTER to calculate the total sales amount for January.

Select a Range of Adjacent Cells in Excel Formula

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

Select a Range of Adjacent Cells in Excel Formula

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

Select a Range of Adjacent Cells in Excel Formula

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

Select a Range of Adjacent Cells in Excel Formula


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

Insert a Range of Non-adjacent Cells in Excel Formula

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

Insert a Range of Non-adjacent Cells in Excel Formula

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.

Select an Entire Column or Row

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

Select an Entire Column or Row


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:
=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.

SUM and INDEX functions

  • Press ENTER to calculate the total sales amounts.

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


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

Get FREE Advanced Excel Exercises with Solutions!
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo