Excel - Chapters 9 & 10, 11 & 12
It looks like
we're biting off a huge chunk this week, but not really. Mainly,
because I'm not going to be emphasizing some of the material in some of these
chapters. A lot of what you're going to be seeing is "extra" material, like
styles, themes, templates, adapting the Quick Access Toolbar, etc. Some of it will
be important. Chapter 12 won't be on the
test, and you won't be doing any homework assignments from these 3
chapters...just reading and doing the hands-on exercises.
Getting Started: Excel - Chapter 9
Links, and Formula Auditing
One of the most powerful aspects
of Excel is its ability to link multiple worksheets and workbooks
together to consolidate information. The user can create multiple
small specifically targeted workbooks that can then be linked
together to form consolidated information across large amounts of
information. For example, data can be collected at company
locations across several states in individual workbooks locally
centered, and then these workbooks can be consolidated together to
form information about the company as a whole. You can also use this
feature to separate expenses, one expense per spreadsheet and then
consolidate the totals of each spreadsheet to another workbook which
consolidates those expenses with other aspects of the company's
In addition, 3-D worksheet
references include the name of the Worksheet followed by an
exclamation point (!). (The cell reference can be an individual cell
or a range of cells.)
You can combine worksheet
references two ways:
Chicago!C3 Ex. =Atlanta!C3+Boston!C3+Chicago!C3 or
this references each
cell separately--the data does not have to be in the same
cell in each worksheet
There are two methods:
(1) Here, click on the formula bar, press the "=" key, then
click on each worksheet and then cell, one at a time,
pressing the "+" sign between references.
(2) Here, click on the formula bar, press the "=" key, type
in "SUM(" and then click on each worksheet/cell one at a
time, separating each cell reference with a comma and then
press Enter (Excel will close your parentheses for you).
this references all
worksheets (Atlanta, Boston, and Chicago) and the same cell
in each worksheet--the data must be in the same place. The
worksheet references remain absolute (Atlanta!) where as the
cell references can be either absolute, mixed or relative.
The method: click on
the formula bar, press the "=" key, type in "SUM(", and
then click on the first spreadsheet, hold the Shift key
down, and then click on the last spreadsheet in the range.
Then click on the cell you want to access, in this case C3
and then press Enter. (Excel will close your parentheses for
this references the
specific workbook (Expenses.xls), the spreadsheet (Taxes)
and the individual cell (C3). The workbook references and
sheet references are absolute, whereas the cell reference
may be relative, mixed or absolute.
The method: click on
the formula bar, press the "=" key, type in "SUM(", and
then click on each workbook, then worksheet, then cell one
at a time, separating each cell reference with a comma and
then press Enter (Excel will close your parentheses for
To add a workbook reference,
place the name of the workbook in brackets, [ ], followed by the
name of the worksheet and (!) and then the cell reference. Workbook
and worksheet references that come from another workbook will ALWAYS
be Absolute. The cell reference can be Relative, Absolute or Mixed.
Pay particular attention (hint) to the referencing.) You can
certainly type in the reference, but it is much easier to simply
point and click on the cell you want to include in your formula.
Try this out. I think you'll like it much better than having to
type in the entire address...
Organization and documenting
worksheets are an important step in creating a workbook environment
that will be used by more than one person. It is important to keep
consistency in the data, the formatting and structure of the
workbook. The documentation page is designed so that a new user can
view the instructions on how to use the spreadsheet. This used to be
performed in a separate set of instructions (usually created in
Word), but these instructions usually got lost or misplaced. Placing
the instructions within the spreadsheet, the instructions will
always be available to the workbook's users.
I've provided you with a simple
example of a Documentation page below to guide you:
Purpose of Workbook:
amounts listed by quarter, sales rep and product
amounts for company by sales rep, quarter, and media
made from Advanced filter of database
using 3 slicers
Media and Quarter
of Range Names used in Workbook:
||Purpose of Names
||data values with no decimals
||used in advanced filter
||range of data values
||data values for products
||data values by quarter
In the example above, I've wrapped
text, borders and shading, Cell Styles, used the =TODAY() function
for the Revised date, and included a List of Range names to guide
anyone using my spreadsheet. the color scheme should match
that of your other spreadsheets within the workbook.
Excel - Chapter 9
Back to top
the process of creating a
summary worksheet or workbook from several individual
include several individual
worksheets, along with a summary worksheet, to demonstrate a
drill-down concept, a method used to summarize data from
multiple worksheets into a single worksheet.
Copy data from an individual
workbook or copy worksheets from workbook into another
TIP: The horizontal
scrollbar contains four scrolling buttons to scroll through
the worksheet tabs in a workbook.
TIP: The single workbook
is easier to use as all of the data is in one file. The
disadvantage–the worksheets have to be maintained in remote
locations and several people have to have access to the same
workbook. The multiple workbooks make it easier to maintain
the data, but four separate files are required for summary
Automatic except Tables, and Manual. User avoids recalculating
every time a change is made, and is particularly useful when you
have a very large worksheet that only needs one recalculation.
Automatic recalculation does all calculations, revised or not
When manual calculation is enabled, you can recalculate either the
workbook or the worksheet by pressing the F9 key.
ITERATION is the repeated
recalculation of a worksheet until a specific numeric condition
PRECISION is a measure of the
degree of accuracy for a calculation.
reference to a cell on a worksheet not currently active.
Use cell ranges to simplify functions
Worksheet references are always absolute
references; however, the cell reference may be either
relative, absolute, or mixed
TIP: When referencing
multiple workbooks, you must include the workbook name with its
extension placed between brackets .
Ex. [WorkbookName]'Worksheet name'! RangeOfCells.
(3D) FORMULA–a formula or function that that refers
to the same cell or range on multiple worksheets. The individual
reference is called a 3D cell reference
The sheet range is specified with a
colon between the beginning and ending worksheets
An exclamation point follows the last
worksheet, before the cell reference
worksheets to format or enter data in
multiple worksheets at the same time.
worksheets to enter data in a specific worksheet.
TIP: You can use Shift to select contiguous sheets and use Ctrl
to select non-contiguous sheets.
Uses formulas that reference cells in
Create external references that refer to
a cell or range of cells in another workbook
Enables changes in one workbook which
are reflected in another workbook
If the source files are deleted or moved, the drive mappings
change and the links will no longer be valid.
INSERT, EDIT, DELETE OR PRINT
Insert comments by clicking the Review tab
Once a comment is inserted, the options to
Edit and Delete will appear on the Review tab
To print comments, click on the Page Layout
tab. Open the Page Setup dialog box and click the Sheet tab.
In the Comments drop-down list, choose “As displayed on sheet”
–violates correct construction of a formula.
Logical error –correct
syntactically, but uses invalid logic, producing inaccurate
DATA VALIDATION enables you to
restrict values that can be entered into a cell
INPUT MESSAGES are descriptive
text or instructions for data entry
ERROR ALERT MESSAGE
FORMULA AUDITING–enables display
or trace relationships between cells and formulas
PRECEDENT CELLS–cells referred
to by a formula in another cell
formulas that refer to other cells
commands to graphically display the
relationship between these cells and formulas.
Uses tracer arrows
To check for errors select the
ERROR CHECKING command in the
Formula Auditing group on the Formulas tab
Use the Evaluate
Formula dialog box to view different parts of a
nested formula and how they are evaluated in the order the
formula is calculated
A CIRCULAR REFERENCE is a
formula that contains a cell reference that relies on its own value.
Circular references inevitably lead to
Most are created inadvertently
When a formula is started with the active
cell being one that contains a value rather than an empty cell
that will display the result
scrolling through a very large worksheet, formulas in cells that are
not visible can be “watched” using the
WATCH WINDOW box.
include such elements as
the author, the subject of the workbook, the location of the
workbook, and define formulas used in the workbook.
WORKSHEET – describes contents of each worksheet within
Important–spreadsheet very often used by
individuals other than the person who created it
Create a documentation worksheet
describing contents of each worksheet within a workbook
Format attractively; call attention to
the title of the workbook
– change by clicking the Office Button, select Prepare, and
editing of the same workbook--an integral part of financial forecasting
(as you will see...there are several Pits associated with the following)
additions, deletions, and formatting changes made in a workbook
accepting, and rejecting changes enable you to review the changes others
you turn off Track Changes:
the workbook is
no longer shared
the history of
changes made is lost
other users who
are sharing the workbook will not be able to save the changes they made
time you close the workbook and reopen, you must enable Highlight Changes
Lists particular types
of changes made to the workbook
dates, times, new, and original values, and other details about the
made to formulas with dependent values are not listed
not track font changes or hiding/unhiding columns or rows
When several people can
simultaneously edit a workbook stored on a network server
a network, the last person to make changes decides which changes to accept
can arise if a user doesn’t property close the workbook
multiple users attempt to change the same cell at the same time, a
Conflict Resolution dialog box is displayed for the second user.
Workbooks can be saved
in a variety of Excel version formats
Can also be
saved as a text file, a Web page, a PDF, and a template
clicking the Office button and choosing the Save As option
as a PDF requires an add-in
computer programs that you can add to
enhance the functionality of Excel
completed workbook used to create other workbooks.
contains formulas and formatting or themes but no specific data
process of inserting an object or data into a file
to determine what properties of Excel will be available when you save
the document in an older version of Excel
collection of metadata associated with a file
Excel files for hidden data and personal information
you to grant people access to workbooks while restricting their ability
to print, copy, or edit the workbook
electronic notation in a document to authenticate the contents
you to mark a document as final which:
ribbon commands are disabled
EXCEL OPTIONS DIALOG BOX –
Centralized area where you can
change the look, behavior, and calculation options in Excel
Getting Started: Excel - Chapter 10 -
No content will be covered from this chapter.
Getting Started: Excel - Chapter 11
Back to top
partially completed workbook that is used as a model to create other
workbooks that have the same structure and purpose
contains formulas and formatting but no data or only sample data
resides in a
special folder so the latest templates are available for you to use
different file extension (.xltx) than a regular Excel workbook (.xlsx)
A defined set of colors,
fonts, lines, and fill effects applied to an entire workbook or to
specific items in a workbook.
image placed behind the worksheet data.
set of formatting options applied to cells.
be used in all Excel workbooks
custom style you create will appear in the Custom section of the Cell Styles
Some considerations when
creating a template:
A template should be a
Create formulas so
they will not change.
function to trap errors.
Turn off worksheet
meaningful names; delete worksheets that are not used.
modification of formulas and text but enables you to change values in
worksheets involves two steps: unlock the cells that can be changed, and
then protect the worksheet
in an application or WEB PAGE and serve
three basic purposes:
To display another Web
To display a different
application file, such as a Word file.
To display another
worksheet or different location on a large worksheet in Excel
Preview in browser to
ensure how the worksheet will look on the Internet.
Insert pictures or
graphics to enhance the Web page.
color and font combinations, use graphics sparingly, and use the Save As
option to save a worksheet as a Web page.
Excel to go to a particular Web site to obtain the Web based information
the Web address changes, you must change the Web query and URL to ensure
you have the most accurate information and citation
information about the macro, but do not affect its execution and are
every command you execute
you do once you begin recording a macro becomes part of the macro
Microsoft Office uses a
subset of Visual Basic known as Visual Basic for Applications (VBA). The
VBE (Visual Basic Editor) can be used to debug macros. We won't be
covering VBA, but I teach a course in Visual Basic if you're interested in
extending your Office abilities.