Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Named Ranges

| | Information Helpful? Why Not Donate.

Excel Named Ranges/Using Names in Excel

<< Back to Excel Formulas Index

Excel Named Ranges

Named Ranges in Excel have been around a long time. Using them frequently is a very good habit to form. The advantages are;

1) Formulas are easier to read and understand.
2) VBA code is not adversely affected by moving cells.
3) Range reference can be changed globally in one place.

Naming any Excel range is very easy and can be done in seconds.

1) Select the range of cells to name
2) Click in the Name Box (left of formula bar) and enter a one word name (no spaces allowed) and push Enter.

Then you can replace a formula like: =SUM($A$1:A$100) with: =SUM(Yr2005Figures)

This is basics of Excel named ranges. See the list below for many tip, tricks and uses for named ranges.

The only way to delete an existing range name, or modify its cell reference, is via Insert>Name>Define.

Sheet Level Named Ranges Normally, when you name a range the name is at the Workbook level, meaning the name refers to a specified range on a specified Worksheet. Once the name has been used it cannot be used again to represent a range on another Worksheet. However, sometimes it can be very handy to have one name that will refer to a specified range on the active sheet at the time. Here is how it is done!

Named Constants Let's look at Named Constants. These are simply values that are given a meaningful name.

Named Formulas Now we have seen how easy Named Ranges and Named Constants are, we can look at a little known ability of Names where they can used to create custom formulas

Relative Named Ranges A relative named range is a named range that refers to a range that is relative to the position of the cell pointer at the time they are created. This makes them perfect for formulas that are required to be moved around a worksheet and are easy to create.

Dynamic Named Ranges Possibly one of Excels most underutilized aspects is its ability to create dynamic named ranges that will expand and contract according to the data in them.

Advanced Dynamic Named Ranges They are ideal to be used in a vast array of different situations. I thought I would show you some Dynamic Named Ranges with a twist.

Excel Named Range Manager . Do all the above and more!

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft