OzGrid

Lesson 23 - Excel's Named Ranges

< Back to Search results

 Category: [Excel]  Demo Available 

EXCEL'S NAMED RANGES

 

Lesson 23 - Using Named Ranges in Excel as an Alternative to Cell References

NAMED RANGES IN EXCEL

Excel allows us to give Worksheet ranges names that can make our formulas easier to read. For instance if we use the above example that we used for the SUMIF Function, we could name our Criteria range (B1:B7) "Names" and our Sum_range (A1:A7) "Amounts". This would make our formula a bit easier to read.

There are however some basic rules for naming cells that we must adhere to. These are listed below and are from the Excel help file.

Guidelines for naming cells, formulas, and constants in Microsoft Excel

  • The First Character Of A Name Must Be A Letter Or An Underscore Character. Remaining Characters In The Name Can Be Letters, Numbers, Periods, And Underscore Characters.
  • Names Cannot Be The Same As A Cell Reference, Such As Z$100 Or R1C1.
  • Spaces Are Not Allowed. Underscore Characters And Periods May Be Used As Word Separators ¾ For Example, First. Quarter Or Sales_Tax.
  • A Name Can Contain Up To 255 Characters.
  • Names Can Contain Uppercase And Lowercase Letters. Microsoft Excel Does Not Distinguish Between Uppercase And Lowercase Characters In Names. For Example, If You Have Created The Name Sales And Then Create Another Name Called SALES In The Same Workbook, The Second Name Will Replace The First One.

There are a couple of ways we can name ranges so let's jump straight in with an example.

  • Delete The Contents Of Cells A1 And B1 And Type The Word Amounts In Cell A1 And Names In Cell B1.
  • Type 5, 20, 40, 50, 100, 200 In Cell A2:A7 Respectively. Type The Names Bob, Dave, John, Fred, Dick, Jill In Cells B2:B7 Respectively, If They Are Not There Already.
  • Now Either Select The Range A1:B7 With The Mouse, Or Push Ctrl + Shift + * This Will Make Excel Select The Current Region. The Current Region Is Defined As All The Non-Empty Adjoining Cells Surrounding The Active Cell. The * (Asterisk) Must Be The One On The Same Key As The 8).
  • Go To Formulas>Create from Selection>. This Is The Create Names Dialog Box And Is Used To Create Names Based On The Current Region Row And/Or Column Headings. In This Case We Only Have Column Headings So Ensure That Only The "Top Row" Check Box Is Checked.

  • What We Have Done By Doing This Is Told Excel That We Wish To Name The Range A2:A7 Amounts And The Range B2:B7 Names. In Other Words Use The Headings In The Top Row To Name The Selected Ranges Directly Below Them. Click OK
  • Now Select Cell C8 And Push Shift + F3 Or Go To Formulas>fx. Locate The SUMIF Function Either From Within The Category Most Recently Used Or Math & Trig Then Click OK.
  • Click The Collapse Dialog Button On The Range Argument Box And Select Range B2:B7. You Should See Excel Place The Name Names In The Range Argument Box. This Is Our Named Range B2:B7. Click The Expand Dialog Button.
  • Type "D*" In The Criteria Argument Box.
  • Click The Collapse Dialog Button On The Sum_range Argument Box And Select Range A2:A7. You Should Again See Excel Place The Name Amounts, This Time In The Sum_range Argument Box. Click The Expand Dialog Button. Now Click OK.

You should have the formula =SUMIF(Names,"D*",Amounts) giving the result of 120.

Using the Create names dialog is the easiest method to use if we are going to be calling our ranges the same names as the column and/or row headings as it saves typing and typos. We could, if we wanted, type the names in ourselves, by typing them directly into the Name box. The Name box is on the left of the Formula bar. If we click the small drop arrow on the Name box, we should see both our named ranges Amounts and Names. If you select either one Excel will take you straight to the chosen named range and select it. It is in this box that we can type a name in directly.

To see what I mean select the range A1:B7 and click in the Name box. Type the word Data and push Enter. Now select any cell outside of range A1:B7 and select the name Data from the Name box, you will be transferred automatically to the Data selection.

The other thing we need to know when dealing with named ranges is how to delete them. This can only be done in one way and this is via the Insert name dialog box.

  • Go To Insert>Name>Define Or Push Ctrl + F3. This Will Display Our Insert Name Dialog Box.
  • You Should See The Three Names We Had Created Amongst The Listing. Select The Name Amounts.
  • If You Now Look In The Refers To Box You Should See =Sheet1!$A$2:$A$7. The Sheet1! May Be Different If The Worksheet Is Called Something Else.
  • All We Need To Do Now Is Click Delete And The Named Range Amounts Will Be Deleted.
  • Do The Same For Data And Names Then Click OK.

You will notice that our SUMIF formula is now retuning #NAME? This is Excels way of telling us the formula within the cell contains text is does not recognise. Delete the #NAME?

More on Named Ranges Here

 

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

Go back to:

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle
Lesson 8 - Excel Paste Special
Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing
Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions

See also:

Lesson 24 - Excel's Constants and the Paste Name Dialog
Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 


Gallery



stars (0 Reviews)