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

Relative Named Ranges in Excel

| | Information Helpful? Why Not Donate.

Using Relative Range Names in Excel

Got any Excel Questions? Free Excel Help

Relative Named Ranges Download Workbook With Data

The most common type of range name is an Absolute Range Name . An absolute range name refers to a specific cell or range as an absolute address like $A$1. This is because when you create a named range, you are usually creating a range to refer to a specific part of a worksheet.

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. When you create a relative named range it is VERY important that you have the position of the cell pointer in the correct cell before naming.

Let�s say we have a spreadsheet and we want to create relative names for it so that we can create a running Total.

Column A contains invoice numbers and is called Inv. No. Column B contains Vendors names, and is headed Vendors, Column C is called Debit and column D is called Credit. In column E we want to place our formula. This column is titled Total. Assume there are a mixture of numbers and blank cells in columns C and D.

The first thing that we need to do is go to Insert>Name>Define. Under Names in Workbook type in Vendor., now press the tab key three times to jump to the Refers to: area of the dialog. In this area type =$B:$B, (absolute) then select Add, then Close.

We need to also add another three named ranges:

Go to Insert>Name>Define and add the following absolute named ranges:

Debit =$C:$C
Credit =$D:$D
Total =$E:$E

Now we need to create names for our rows in order for our formula to work.

Go to Insert>Name>Define and under Names in Workbook type in This, then jump to the Refers to box and type in the relative range: =2:2

We need to add one more relative named range:

Prior =1:1

Click Add, then Close.

Because we had the cell pointer firstly in row 2, This will always refer to the current row. Prior will always refer to the row above.

Now to write the formula.

In cell E2, type in the following formula:

=Debit-Credit+IF(Vendor=Prior Vendor, Prior Total)

Hit Enter, then with your fill handle , copy down the page.

See also Excel Named Ranges for many more types of named ranges

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