Excel 2007 Range Names

Excel 2007 Range Names - A Range Name is used to refer to a cell, group of cells, constant value or formula. Once a Range Name is defined, it can be used in place of values or cell references in formulas. Range Names are also useful for navigation.

 

Range Names – Cell

To create a Range Name for a single cell:

  1. Select the cell to be named. In the example below, this is cell D2, displaying $74,356.00.

    Range Names Cell

  2. Click in the Name Box and type JonesQ1. Press Enter.

    Range Name Box

  3. Repeat the steps outlined above for cells D3 through D5.

    Cell Range Name
    D3 AndersonQ1
    D4 SmithQ1
    D5 LowellQ1

Excel 2007 Range Names – Group of Cells

To create a Range Name for a group of cells:

  1. Select the group of cells to be named. In the example below, highlight cells D2 through D5.


    Range Name for Group of Cells

  2. Click in the Name Box and type SalesQ1. Press Enter.

    Named Range for Group of Cells

  3. Repeat the steps outlined above for Quarters 2 through 4.

    Group of Cells Range Name
    D8:D11 SalesQ2
    D14:D17 SalesQ3
    D20:D23 SalesQ4

Moving to a Named Range

Excek 2007 Named Ranges are a simple way to navigate a worksheet.
To move to a Named Range,

  1. Click in the Name Box.
  2. Type the name of the Named Range.

    name box

  3. Press Enter. The Named Range is selected in the Workbook.

    named range box

Using Range Names in Formulas

By using Range Names in Formulas, you are able to use meaningful words instead of cell references.

Cell References are sometimes confusing, particularly when multiple references are used in a single formula. Let’s suppose we want to find out the total sales for Jones for the year. We could write the formula so that it looks like this.

range name in formula

This formula is confusing to look at. I am not able to tell what information the referenced cells contain. However, if I use Range Names, it is easy for me to see that I have chosen the correct cells for my formula.

To find the total sales for Jones for the year using Range Names,

  1. Click in the cell to contain the formula. In the example below, this is cell C26.
  2. Type: =SUM(JonesQ1,JonesQ2,JonesQ3,JonesQ4)

    named range in formula
  3. Press Enter. The results display.

Managing Excel 2007 Named Ranges

Use the Name Manager to perform Named Range-related tasks. Tasks include: creating, editing and deleting Named Ranges, sorting, fixing errors and determining scope.

To access the Name Manager click the Name Manager button in the Defined Names group of the Formula tab.

managing namred ranges

The Name Manager screen opens.

name manager

Creating

To create a new range,

  1. Click the New button. The New Name screen opens.

    creating a new range

  2. Fill out the fields as appropriate.
  3. Click OK. The new range is created.

Editing

Let’s suppose we’ve added Quarter 1 sales figures for the following year to our existing data. We want to include this information in our “All” ranges for each of the sales people. To do this, we must edit the Named Ranges for each.

To edit a Named Range,

  1. Highlight the Named Range to edit. In the example below, this is Anderson_All.
  2. Click the Edit button. The Edit Name screen opens.

    editing a range

  3. Click the Refers to button. The Edit Name – Refers to screen opens.

    editing a named range

  4. Click in the cells to add to the formula. For the example below, press Ctrl and click in the cell to add to the range.
  5. Close the Edit Names – Refers to screen.
  6. Edit anything else on the Edit Names screen.
  7. Click OK. The Named Range is updated.

Deleting

To delete a Named Range, highlight the name to delete and click Delete.

Scope

Named Ranges can be set for a specific worksheet or the entire workbook. By default Named Ranges are set for the entire Workbook.

To change the scope, click the Scope dropdown on the Edit Names screen and select the appropriate scope.

deleting a range

Creating a 3-d Range Name

3-d Range Names are cell references or Range Names that span two or more worksheets in a workbook. 3-d Range Names refer to the same cell on multiple worksheets.

Let’s suppose we have our sales information setup on a different worksheet for each year. We can setup a 3-d Named Range that will include the Quarter 1 sales information for Jones for the years 2009, 2010 and 2011.

To create a 3-d Range Name,

  1. Choose Define Names from the Define Names dropdown button in the Defined Names group of the Formulas tab.

    create a 3d range

  2. Type a name for the 3-d Range. In the example below, we’ll use Jones_Q1_All_Years.
  3. Delete the entry in the Refers to field.
  4. Type =.
  5. Hold down Shift and click the worksheet tabs to be included in the reference.

    Naming a range

    The New Name – Refers to screen looks like this:

    new nambed range

  6. Click the cell to include. In the example below, this is cell D2.
  7. Close the New Name – Refers to screen.
  8. Click OK. The 3-d reference is created.

How to Link to Excel 2007 Range Names

It will appear on your page as: Excel 2007 Range Names

 


Search for Tutorials
 

Excel Tutorials: Excel 2007

Excel 2007 Tutorials
Excel 2007 Charts
Excel 2007 Graphics
Excel 2007 Worksheets
Excel 2007 Worksheet Views
Excel 2007 Formulas
Excel 2007 Named Ranges
Excel 2007 Cell Comments

Excel Video Tutorials
Lesson 1: Excel Basics
Lesson 2: Editing a Worksheet
Lesson 3: Making Bar and Circle Graphs
Lesson 4: Making a Gantt Chart
Lesson 5: Excel Keyboard Shortcuts

More Excel Tutorials at GuidesandTutorials.net

All Other Guides and Tutorials

Guides and Tutorials Blog
Listing of the latest tutorials published on the GuidesandTutorials website.
GuidesandTutorials: Audacity Tutorial - Record and Edit Audio
This Audacity tutorial will show you how to use the basic tools and procedures in the open source audio editing program, Audacity. Not just screen shots, but free narrated multimedia tutorials - You'll learn to use Audacity for recording and editing audio files that can be used for podcasting, mixing music and vocals, creating sound tracks, and other audio projects.
Audacity Tips & Tricks
Audacity tips and tricks submitted by users
Digital Photography Guide
This Digital Photography Guide will provide you with the basic information you need to take professional quality photogrphs with your digital camera.
GuidesandTutorials: Dreamweaver Tutorial
Dreamweaver Tutorial - this series of free Dreamweaver tutorials is just what you need to get up and running with Dreamweaver to create interesting and compelling web sites
GuidesandTutorials: Microsoft Excel 2003 Tutorial
This Excel 2003 Tutorial will introduce you to the basics of using the Excel 2003 spreadsheet program. You'll learn to create a spreadsheet, basic Excel workbook skills, work with cells, enter and manipulate data, apply formulas, format data, create and work with charts, and manage Excel workbooks.
Podcasting Tutorial
This Podcasting tutorial includes a step by step guide that will show you how to record, publish and promote your podcast. Using inexpensive equipment and free software, you'll be publishing podcasts in no time.
PowerPoint Tutorial - Microsoft PowerPoint 2003
This PowerPoint tutorial is just what you need to learn how to use PowerPoint 2003 to create effective and engaging presentations.
The PowerPoint 2007 Window
This PowerPoint 2007 tutorial will show you the features and how to get around the PowerPoint Window.
GuidesandTutorials: Free PowerPoint Templates
Free PowerPoint templates that you can download and use today. This is a growing library of free resources for PowerPoint users.
LCD Projector Guide and Tutorial
This LCD Projector Guide will assist you in the use and purchase of a digital LCD projector or DLP projector. It will discuss the differences between LCD and DLP projectors, size and portability considerations, lamp types and brightness, resolution, image quality and other considerations in the use and purchase of a digital projector.
Word Tutorial: Microsoft Word 2003
This Word tutorial is just what you need to learn how to use Word 2003 to create professional looking documents.
Word 2007 Workspace
This Microsoft Word 2007 Workspace tutorial will acquaint you with the new tools and features in the Word 2007 Window. It's changed significantly from previus versions and even experienced users will find this helpful.
Work at Home Jobs
This Work at Home Jobs tutorial will provide information on ways that you can put your skills and knowledge to use in creating part or full time income while working from home.
Guides and Tutorials: Free Guides and Tutoials Site Map
Free guides and tutorials on podcasting, digital video and audio, Audacity, RSS, multimedia development, educational technology, technology integration, K12 Technology, PowerPoint.
Suggest a New Technology Tutorial
Free Technology Tutorials on software and technology applications including PowerPoint tutorials, Podcasting tutorials, Dreamweaver tutorials, Audacity, Non-Linear PowerPoint, Digital Photography, RSS tutorial, and everything educational technology.
Adobe Acrobat 9 Tutorial
This Adobe Acrobat 9 Tutorial will show you how to create PDFs from MS Office applications, from Multipe files, from web pages and from several other types of documents.
GuidesandTutorials: Free Tutorials
Free Tutorials, software, hardware, and technology applications including PowerPoint, Podcasting, Dreamweaver, Audacity,
Excel 2007 Tutorial | GuidesandTutorials: Free Tutorials
This free Excel 2007 Tutorial will show you how to use all of the begginer and intermediate features in Excel 2007

This site proudly build with Site Build It!

Powered By SiteBuildIt

Excel 2007 Ebook: The Ultimate Guide - $12.95

Excel 2007 Ebook

Get the Excel 2007 Ebook: The Ultimate Guide and receive all of the Excel 2007 tutorials in one handy reference guide. Purchase online easily with your credit card or PayPal and have the Ebook emailed to your email address (pdf format)

Price: $12.95. California residents pay 7.25% Sales tax. Delivered via email.

Excel Video Tutorials
1. Excel Basics
2. Editing a Worksheet
3. Making a Gantt Chart
5. Excel Keyboard Shortcuts

Excel 2007 Tutorials
Excel 2007 Charts
Excel 2007 Graphics
Excel 2007 Worksheets
Excel 2007 Worksheet Views
Excel 2007 Formulas
Excel 2007 Named Ranges
Excel 2007 Cell Comments

More Excel Tutorials at
GuidesandTutorials.net

[?] Subscribe To This Site

XML RSS
follow us in feedly
Add to My Yahoo!
Add to My MSN
Subscribe with Bloglines