Navigating Formula Reference
Worksheets are composed largely of formulas, but the casual viewer cannot
distinguish a formula from a value. Values, once typed in, never change.
Formulas change whenever their precedents—the cells they depend on—change.
To check a formula’s accuracy, double-click inside the cell containing the
formula. If you go to Tools > Options… and check the Edit tab, you’ll
probably see “Edit Directly In Cell” is checked. If that’s how your
options are set, you’ll see something like the graphic below. Each cell
reference (B1,
B3,
B5)
appears in a different color and each cell referred to is outlined in the
matching color.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
You can change this behavior so that double-clicking on a formula selects the
cell references rather than highlighting them. Just remove the check mentioned
above in “Edit Directly In Cell”. For formulas that refer to more than one
cell, each cell reference becomes a part of the Selection—shown
as the three emphasized cells below. Only one cell is the Active Cell—shown
below outlined but not highlighted. You can change the active cell without
changing the selection by using the Tab key. A tab takes you forward to the next
cell in the selection without changing the selection! A mouse click or
arrow key will change the active cell and the selection, losing the information
on the referenced cells. To traverse the selection in reverse you can use
Shift-Tab.
This example is quite simple—all of the related cells are on the same
sheet. The power of this tool comes when cell references are on other sheets or
in other workbooks. You can double-click on a highlighted cell to find it’s
precedent, then click on it to find its precedent, etc… Be careful, if there
are multiple references on multiple sheets, only the first reference’s sheet
is shown.
Watch the video of
the this function for more details.
To go back to where you came from, use F5 and just hit return. You’ll first
see the Go To… dialog box and when you hit return (or click OK) you’ll go to
the default location—back where you came from. It’s kind of like hitting the
Back button on a browser.
|
|