Lesson 17: Named ranges in excel!

How to Create a Named Ranges in Microsoft Excel!

This video is a part of free Excel Training Singapore, where I show you how to create a name ranges in Excel from the basics and also cover a lot of advanced Features.

Named range in excel means a single cell or a group of cells that have been given a name. The name so given to a cell or a range of cells can be used to refer to that cell in any formula or any conditional formatting.

Benefits of named ranges in excel –

• Saves time and efforts – Cell naming in excel helps users to get rid of complex and lengthy cell references. This helps days calculation easier and swifter.
• Easy to track the formulas – When cell references are replaced with cell names, tracking of formulas becomes very easy. This benefit is experienced most while working with unending complex data while performing huge calculations on it.
• Create dynamic named ranges – The dynamic named ranges are used to create dependent drop down lists. These lists are updated themselves because of their dynamic nature as and when any changes take place in the named range.
• Manage ranges by using keyboard shortcuts – Most of the keyboard shortcuts created can be managed by key combination all of them involving some use of F3. Numerous key combinations such as F3, F3+ ctrl and many others. These come very handy just in case the user forgets the name or there are many named ranges.

How to name a cell in excel?

Naming a cell in excel is of great benefit while working with huge data where cell reference is of little or no use. Cell names can be used in place of cell reference in complex formulas or conditional formatting. To learn how to work with them, follow the following mentioned steps –

• Start by preparing any data having various entries. Select the numeric part of data and go to the name box that is to the left of the formula bar
• Type any name related to the data entered. Click enter to assign that name to that cell.
• To check if the name has been created or not, click on the arrow next to the name and the name must appear there
• Other way to name a cell is to go to formulas, click on define name option. This opens up a new name dialogue box
• Fill in the required data in the dialogue box such as name, scope, command and hit OK

How to manage named ranges?

After having created a number of named ranges, how to manage them is also very essential to understand. Managing name range allows users to manage and edit the names conveniently. To learn how to manage named ranges, follow the following mentioned steps –

• Prepare any data having numeric values as well. Assign names to the numeric values. To manage these names, click on formulas bar.
• There appears an option, name manager, with a defined names group. Click on names manager. The name manager dialogue box appears that contains names of all the named ranges in the entire worksheet
• The name ranges can be filtered by clicking on the filter option at the right-hand corner. Various filters such as names with error, names without error, table names, names scoped to workbook and names scoped to worksheet. Select your choice of filter to filter out names accordingly
• To edit any of the named ranges, select any of the range and click on edit option. A pop-up menu of the edit name appears. Make the necessary changes and click OK

How to create a dynamic named range in excel?

Dynamic named ranges in excel simply mean to create dependent drop-down lists that changes itself when any changes in the named range is made. The dynamic named range can be created using the INDEX or the OFFSET function.  To learn how to create a dynamic named range just follow the following mentioned steps –

• Start by preparing a data having various years and give them a named range. To make this normal named range, a dynamic one, start by making a dynamic formula in any cell
• Start by typing the OFFSET FORMULA followed by its input in the respective Four arguments. This formula will be more focused on making the height portion dynamic so that it’s becomes self updating in nature
• To do that use the COUNT IF formula in the height section. Fill in the required arguments of the COUNT IF formula which are range and criteria
• For range, select various cells to make the function work in a dynamic manner and press F4 to lock that input. In the criteria input a condition clarifying that it should not be blank. Press enter to get the desires output

Learn Microsoft Excel course in Singapore!

Microsoft excel undeniably holds great importance in almost all types of firms and workplaces. To keep yourself educated as per the required times, take up the best Excel training course in Singapore today!

In this tutorial of Free online Excel Training Singapore, I will cover all that you need to know about Named Ranges in Excel.

Named Range – as the name suggests – is a range that has been named.

This can be extremely useful as it allows you to use this defined name instead of the range. For example, instead of using A1:C10, you could instead use the name Sale Data in the formula.

This video will cover a brief introduction of the named range and then show you how to create one in Excel. It also covers some advanced concepts such as creating a dynamic named range – something that automatically updates in case your data changes.

A very popular use of the dynamic named range is to create dependent drop-down lists (which is covered in detail in this video). You can use functions such as INDEX or OFFSET to create dynamic named ranges.

This video also covers some important named range keyboard shortcuts that can help you save time.

It covers the following topics:

• Creating Named Ranges
• Managing Named Ranges
• Important Keyboard Shortcuts
• Creating Dynamic Named Ranges using OFFSET and INDEX formulas