QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Create Named Range in Excel?

By Neha T Leave a Comment

It easy to use a cell or the range of cells by a name instead of their references. In excel we can create named range which makes the working in excel easier. However, using the named range also saves our time and provide efficiency thereby avoiding mistakes which occur while typing the reference for the range of cells.

In this section, we will discuss the steps to:

  1. Name a Single Cell
  2. Name Range of Cells

Steps to Create Named Range in Excel 2016:

Create Named Cell in Excel

Step 1: Select a cell that you want to provide a name. In the ‘name box’, you will see the cell number as in the image below the cell I have selected lies in column B & row 3. Hence, in the name box, the name of the selected cell is showing B3.

Create a named range in Excel 1

Step 2: You can directly rename the selected cell in the name box itself or you can name it using the Name Manger or Define Name in the Defined Names group of Formulas Tab. For example, in the image below, I have named the selected cell as ‘Joseph_Staff_id’ as the cell containing the staff_id of Joseph.

Create a named range in Excel 2

Step 3: Once you name a cell you can use that cell by its name anywhere on the workbook. Like, in the image below you can see I have clicked on a random cell in the sheet and have typed:

=joseph_Staff_id

As I have just started typing the name of the cell, the excel recognizes it and starts displaying similar options for it. You can select from those options.

Create a named range in Excel 3

After typing the name of the cell press Enter and you will observe that the value of the named cell is displayed in the selected cell. Look at the image below the cell where I have used the named cell is displaying the value of the named cell.
Create a named range in Excel 4

Similarly, you can name a cell that contains a formula and use that formula just by using the name of the cell. It will avoid the mistakes that you may make while retyping the formula in any other cell or while using the reference of the cell containing the formula.

Create Named Range of Cells

If you have your data in the tabular form and you want to create a name for each row or column, based upon their labels then you can do it by performing the following steps.

Step 1: To create a named range, select the range of cells along with their column or row labels to which you want to provide a name.

Create a named range in Excel 5

Step 2: In Formulas tab under the Defined Names group click on Create from Selection. A Create Names from Selection dialog box will pop up, where you can decide the location that will be considered as the labels for your selected range.
You have options to create the names of the values in the top row, left column, bottom row & right column.

In the image below, you can see that I have created named ranges from the values in the Top row. So, 4 named ranges are created which are Staff, Staff-Id, Sales & Days.

Create a named range in Excel 6

Step 3: To use the created named range let us encounter an example:

Let’s say I want to sum all the days that each staff has worked for.

So, anywhere in the workbook type:

=Sum(Days)  // press enter

Create a named range in Excel 7

It will sum up all the days that each staff has worked for.

Create a named range in Excel 8

Let us create named range for the values in the left column:

Step 1: Select the range of cells along with the column or row labels if any. In the Formulas tab under the Defined Names group, click on Create from Selection.

In the Create Names from Selection box click on Left column and click OK. It will create 14 named ranges which are Staff, Joseph, Stieve, Fred, Charles, Davis Clawson, Natalia, Sheril, Jenny, Emiliy, Annie Rotus, Emma, Jhona, Roma.

Create a named range in Excel 9

Step 2: Let’s see the example for the created named range.

Anywhere on the workbook, I have typed:

= Natalia

Create a named range in Excel 11

Or you can get the name of range by clicking on Use in Formula under Defined Names group of Formulas tab. It will show you the names of all the named ranges. Select the named range of your use.

Create a named range in Excel 10

Press Enter, it will show you the value of the named range.

Create a named range in Excel 12

So, this is all about how one can create named range in Excel. It is very easy to use the name instead of using the reference of the range in formulas.

You Might Also Like:

Lock Formulas or a Cell in Excel - feature ImageHow to Lock Formulas or Cells in Excel? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016? Edit a Drop-Down List featured ImageHow to Edit Dropdown List In Excel 2016? How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? Merge Cell in Excel feature imageHow to Merge Cells, Columns & Rows in Excel? how-to-use-vlookup-in-ms-excel-2016-featured-imageHow to use VLOOKUP in MS Excel 2016? Graph in Excel - featured ImageHow to Create a Graph in Excel? Pie Chart in Excel featured ImageHow Create a Pie Chart in Excel?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Close Apps on iPhone?
  • How to Disable Java Update Notification / jucheck.exe in Windows 7
  • How to Install Firebug in Mozilla Firefox
  • How to add slide numbers in MS PowerPoint 2016?
  • How to Check RAM Type, Size on Windows 7

Copyright © 2023 · QueHow · Contact Us