Excel2007:Naming spreadsheet regions

From virtualMV2015wiki
Jump to: navigation, search
MS-Excel 2007 <


Icon Introduction.png Introduction:

Overview

Using names for cells and ranges can greatly simplify formulas. In this example a cell is given a name and used to calculate the discount on a list of computer hardware products.

Icon Objective.png

Objective Named ranges

By the end of this page you will be able to:

  • Name a range of cells (cell, column, row and block)
  • Use a named cell in a formula

A simple named cell

This short video explains how a named cell can be used in an Excel spreadsheet instead of an absolute reference.

Sniplet:Excel2007:Named Reference


Click here to download Excel07_01NamedReference.wmv (3.6mb @ 01:28 min)
.

Icon Activity.png Activity:

Excel 2007: Spreadsheet for Named range example
  1. Create the spreadsheet as shown
  2. Click on the cell containing the discount (G4)
  3. Click on the Name Box top left below ribbon bar(should contain G4).
  4. type in disc then press [Enter] (Note: forgetting to press enter may result in the range not being entered into the spreadsheet)
  5. Enter the formula for the first Hard Disc Discount by..
    1. in G7 enter =
    2. click on F7
    3. click on * (times)
    4. Press [F3] to bring up the named range dialog box
    5. Select disc (it will now be placed into the formula)
    6. press [Enter] to accept the formula
  6. Copy the formula down
    1. With G7 selected, float the mouse over the plus in the bottom right corner of the cell
    2. click and drag downwards
  7. Finally, check that the results look right !!

Notes

  • If you want to remove a named cell use the Formulas tab, and in the Defined Names Group, select Name Manager

Icon References.png References


Excel2007:Naming spreadsheet regions. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 13, 2017, from http://www.virtualmv.com/wiki/index.php?title=Excel2007%3ANaming_spreadsheet_regions    (zotero)