Excel Interview Question – How to define a range in Excel

What is a range in Excel? And how to define a range in Excel? Why should we name a range in Excel?

A range is simply a group of cells in Excel. By naming a range in Excel allows people to easily understand formulas that calling the range and also, in case of change, simply to change the area of the range, all formulas will be updated automatically.

How to define a range in Excel?

Remember in our previous post “Resolved: Excel sumif / countif with multiple wildcard criteria“, we have the following formulas:

=COUNTIFS(A3:A12,A16,B3:B12,”>”&B16)

=SUMIFS(D3:D12,A3:A12,A16,B3:B12,”>”&B16)

Example of countifs

Is there any better way to write the formula so that it can state what is A3:A12 ,A16, B3:B12, D3:D12 and B16? Is there any better way to write an excel formula so that it is human readable other than something like A3:A12 ,A16, B3:B12, D3:D12 and B16?

Yes! Excel allows user to assign a business name to a range ( a group of cells).

We can use the “Name Manager” to assign a business name to a range (a group of cells). Also the original data table looks like a table, we can also convert the data table to an actual table in excel.

Today, I am going to illustrate how to use Name manger to assign a name to a range (group of cells)

For example, I want to define the column for the sales person (A3 – A12), following is the steps to use name manager to define a range.

  1. Select the range you need to define.Select_Range
  2. Click “Define Name” from the “Formulas Ribbon”
  3. Enter the name for the selected area and click “OK”.
    Sale PersonNote
    The name must:

    1. Starts with a letter or underscore (_)
    2. Does not  include a space or special characters
    3. Does NOT conflict with an existing name in the workbook
  4. After assigned a name to all columns, you can rewrite the format as :
    =COUNTIFS(SalesPerson,Sales_Person_To_Check,NUMBER_OF_ORDER,”>”&B16)=SUMIFS(REVENUE,SalesPerson,Sales_Person_To_Check,NUMBER_OF_ORDER,”>”&B16)

    Define Name

To check the range of a named area simply click “Name Manager” from the “Formula Ribbon” (Or Ctrl + F3 from keyboard)

Formula Ribbon - Name Manager

Following is the range I have defined:

Name Manager

Please be reminded that although I am using a table as illustration. the benefit of using name manager over converting to an actual table in excel is name manger can be move flexible in defining a group of cells. You can define cells from different places all around the sheet into one name.

Let’s take one more example:

I have defined all highlighted cells under the name “Number” and now I simply need to sum(Number) to get the total.

Leave a Comment