Creating Named Ranges in Excel | Dynamic Named Range in Excel | FREE Excel Course

In this video of this Free online Excel Training, 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 SaleData 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

This video is a part of my Free Online Excel Training where I show you how to use Excel from the basics and also cover a lot of advanced topics.

⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[ellisiverdavid]

7:16: That *Highlight cells + Alt + H + B + A* combo is a killer! 🔥
Perfect as always. Thank you, Mr. Sumit!

[ellisiverdavid]

There's a 3rd way to create the "dynamic year dropdown feature" without OFFSET, INDEX, & hardcoding:
1. Convert your data into a table using *Highlight + Ctrl + T* ;
2. Using that table, create a Data Validation List ( *Alt + A + V + V* ), then select *List*, and finally, highlight the *cells below the header* as your Source; so whenever you update the table, the dropdown list gets updated as well.

On the other hand, if your aim is to grasp the concepts of OFFSET & INDEX and drive the key applications home, Mr. Sumit's examples are one of the best practical applications of those functions.

As I progress in these videos, I realize that there is indeed more than one way to do a specific task. Learning one function is good, but learning about others too is a lot better because being familiar with various methods will equip us with additional "tools", thereby making us more flexible in solving a much diverse range of Excel problems. :)

[wayneedmondson]

Hi Sumit.. these tips are GOLD! Thanks for sharing them.. especially the dynamic range formulas. Thumbs up!

[palaksharma]

Great Video, are there any cons in using Counta() function to count the number of cells that are not blank?

[GoGetSP]

When I used the MAX function on created Dynamic Range at 18:59, it gave VALUE error?

[GoGetSP]

didn't understand the part at 20:07? locking K5?

[rkdsahu]

Maine 5 Cell ka ek col. Me number data liya or is range ko "l" nam diya aise hi maine ek or range "b" banaya ab mai 3rd col ke first row me 2(l+b) formula calculate krna chahta hu but l range ka 1st row or b range ka bhi 1st row ka data hi is formula me use ho or aise hi next row k liye ho ise step by step excel me kaise karenge

[harshitdudani]

I've been getting an error for too long at dynamic named ranges...
How can I be helped at that?

[deanullerich]

Your video shows that the Excel program is in the "File" ribbon. But I cannot select a range of cells to create a "Range". I can only see the Spreadsheet data when I am in the "Home", "Insert". "Page Laayout" or any of the other ribbons. I cannot see it in the"File" ribbon.

Also, in your video you only select one cell for a "Range". I want to select a print range which has many cells.

What am I doing wrong?

[kannanmassey]

Hi, can we copy a name range into another workbook while workbook containing the name range is close
Without using vba

[rahulshewale]

Hi sir,

Could you please make video on indirect function ?

Thank you so much

I love your videos

[SHIVAKUMARSOMA]

Sir how to use correlation function with offset ???

[varshasharmamusic]

my excel doesnt have create from selection

[vidhisajnani]

Hello sir, please please reply
I am not able to copy the formula of index and offset on name manager, as I click the cell it copy the cell no but not the formula in it, so how to edit that in name manager, I am taking about 22.08 part in video

[muazzamalisiddiquie]

Android phone me name ranges kaise den

[sumitha]

My f3 and f4 keys are not working when used alone.Any help??