Course Links

Course Outline

Assignment Schedule

Project Instructions

See News for
Up-to-the-Minute
Updates!

CGS1570 - Lesson Nine
Contains Reading & Practice

Links to Dr. Meg McManus

 

Home Page 

Office Hours

Fast Track Through Material:

Excel
Chapter 9
Excel
Chapter 10
Excel
Chapter 11
 
Summary Summary Summary Jump To Assignments
  What You Should Know What You Should Know Next Lesson
 
 

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 

Data Consolidation, 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 accounting records.

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: 

    Atlanta!C3, Boston!C3, Chicago!C3         Ex.  =Atlanta!C3+Boston!C3+Chicago!C3  or  =SUM(Atlanta!C3,Boston!C3,Chicago!C3)

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).

    Atlanta:Chicago!C3                                  Ex.  =SUM(Atlanta:Chicago!C3)

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 you).

    [Expenses.xls]Taxes!C3      

Ex. =SUM([Expenses1stQtr.xls]Taxes!C3, [Expenses2ndQtr.xls]Taxes!C3, [Expenses2ndQtr.xls]Taxes!C3)

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 you). 

 

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...

Documenting Worksheets

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:

Author: Meg McManus
Date of Creation: 6/19/2012
Revision Author: Dana Scully
Revised Date: 6/21/2012
Purpose of Workbook: Sales amounts listed by quarter, sales rep and product
Spreadsheets used: Description:
database Sales amounts for company by sales rep, quarter, and media
Pie Chart Chart made from Advanced filter of database
Pivot Table Table using 3 slicers
Pivot Chart Based on Media and Quarter
List of Range Names used in Workbook: Named Ranges Purpose of Names
Amount =database!$D$1:$D$100 data values with no decimals
Criteria =database!$A$110:$D$111 used in advanced filter
Database =database!$A$1:$D$100 range of data values
Media =database!$C$1:$C$100 data values for products
Quarter =database!$B$1:$B$100 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.


Getting Started  Excel - Chapter 9                                             Back to top

DATA CONSOLIDATION

the process of creating a summary worksheet or workbook from several individual worksheets/workbooks.

THREE DIMENSIONAL WORKBOOKS

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 data.

RECALCULATION OPTIONS

include Automatic, 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 revised.

 PIT:  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 is met.

PRECISION is a measure of the degree of accuracy for a calculation.

WORKSHEET REFERENCEa reference to a cell on a worksheet not currently active.

  • Use cell ranges to simplify functions and formulas

  • 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.

THREE-DIMENSIONAL (3D) FORMULAa 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

GROUP worksheets to format or enter data in multiple worksheets at the same time.

UNGROUP 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.

WORKBOOK LINKING:

  • Uses formulas that reference cells in other workbooks

  • 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

 PIT:  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 COMMENTS

  • 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”

WORKBOOK AUDITING:

  • Syntax error violates correct construction of a formula.

  • Logical error –correct syntactically, but uses invalid logic, producing inaccurate results.

DATA VALIDATION enables you to restrict values that can be entered into a cell

  • Create a drop-down list of valid entries to provide more accurate data entry.

INPUT MESSAGES are descriptive text or instructions for data entry

  • Are displayed when a user moves to a cell that has some data entry restriction

ERROR ALERT MESSAGE

  • Politely and clearly state the nature of the error

FORMULA AUDITINGenables display or trace relationships between cells and formulas

PRECEDENT CELLS–cells referred to by a formula in another cell

DEPENDENT CELLS–contain formulas that refer to other cells

Use the TRACE PRECEDENTS and TRACE DEPENDENTS 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 invalid data. 

  • 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

To avoid scrolling through a very large worksheet, formulas in cells that are not visible can be “watched” using the WATCH WINDOW box.

 

DOCUMENTING A WORKBOOK

include such elements as the author, the subject of the workbook, the location of the workbook, and define formulas used in the workbook.

 DOCUMENTATION WORKSHEET – describes contents of each worksheet within the workbook. 

  • 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

WORKBOOK PROPERTIESchange by clicking the Office Button, select Prepare, and then Properties.

 

 

COLLABORATION

Involves group editing of the same workbook--an integral part of financial forecasting

TRACK CHANGES  (as you will see...there are several Pits associated with the following)

Monitors all additions, deletions, and formatting changes made in a workbook

Reviewing, accepting, and rejecting changes enable you to review the changes others have made

PIT  If 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

PIT  Each time you close the workbook and reopen, you must enable Highlight Changes

CHANGE LOG

Lists particular types of changes made to the workbook

  • Shows the dates, times, new, and original values, and other details about the respective changes

PIT:  Changes made to formulas with dependent values are not listed

PIT:  Does not track font changes or hiding/unhiding columns or rows

WORKBOOK SHARING

When several people can simultaneously edit a workbook stored on a network server

  • Enables simultaneous changes by multiple users

  • Workbooks can be e-mailed or uploaded to a SharePoint Server

PIT:  On a network, the last person to make changes decides which changes to accept

PIT:  Problems can arise if a user doesn’t property close the workbook

TIP:  If 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

  • Accessed by clicking the Office button and choosing the Save As option

  • PORTABLE DOCUMENT FORMAT (PDF)A universal file format that preserves the original data and formatting for multi-platform use

PIT:  Saving as a PDF requires an add-in

  • ADD-INSIndependent computer programs that you can add to enhance the functionality of Excel

  • TEMPLATEPartially completed workbook used to create other workbooks. 

  • Generally contains formulas and formatting or themes but no specific data

EMBEDDINGThe process of inserting an object or data into a file

  • Copy and paste Excel data into Word or PowerPoint

  • Must embed file to maintain formulas

COMPATIBILITY CHECKERUsed to determine what properties of Excel will be available when you save the document in an older version of Excel

PROPERTYA collection of metadata associated with a file

DOCUMENT INSPECTORChecks Excel files for hidden data and personal information

RESTRICT PERMISSIONEnables you to grant people access to workbooks while restricting their ability to print, copy, or edit the workbook

DIGITAL SIGNATUREAn electronic notation in a document to authenticate the contents

Excel allows you to mark a document as final which:

  • Makes workbook read-only

  • Workbook cannot be saved with the same name

  • Prohibits anyone from changing anything

PIT:  Most ribbon commands are disabled

EXCEL OPTIONS DIALOG BOX –

Centralized area where you can change the look, behavior, and calculation options in Excel

  • Nearly impossible to change the Ribbon; however can make changes to the Quick Access Toolbar

  • Excel Add-Ins Manager allows you to provide new worksheet functionality easily.


Getting Started:  Excel - Chapter 10 -  No content will be covered from this chapter.

 


 

Getting Started:  Excel - Chapter 11                                               Back to top   

 

TEMPLATE

A partially completed workbook that is used as a model to create other workbooks that have the same structure and purpose

  • typically 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

  • uses a different file extension (.xltx) than a regular Excel workbook (.xlsx)

DOCUMENT THEME

A defined set of colors, fonts, lines, and fill effects applied to an entire workbook or to specific items in a workbook.

  • Apply themes to existing worksheets that use Themed Cell styles

BACKGROUNDAn image placed behind the worksheet data.

STYLEA set of formatting options applied to cells.

CUSTOM STYLESCan be used in all Excel workbooks

TIPThe custom style you create will appear in the Custom section of the Cell Styles palette

Some considerations when creating a template:

  • A template should be a blank form.

  • Create formulas so they will not change.

  • Use appropriate function to trap errors.

  • Include instructions

  • Turn off worksheet gridlines

  • Apply appropriate formatting

  • Give worksheets meaningful names; delete worksheets that are not used.

PROTECTING WORKSHEETSPrevents modification of formulas and text but enables you to change values in unprotected cells.

PITProtecting worksheets involves two steps:  unlock the cells that can be changed, and then protect the worksheet

HYPERLINKSLinks in an application or WEB PAGE and serve three basic purposes:

  • To display another Web page

  • 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.

  • SmartArtIs a graphic organizer diagram that visually presents information to communicate a message

PITWatch color and font combinations, use graphics sparingly, and use the Save As option to save a worksheet as a Web page.

WEB QUERYEnables Excel to go to a particular Web site to obtain the Web based information

PITIf the Web address changes, you must change the Web query and URL to ensure you have the most accurate information and citation

COMMENTSProvide information about the macro, but do not affect its execution and are considered documentation

MACRO RECORDERRecords every command you execute

PITEverything 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.

 

 

The Assignment                   back to top 

Part 1

Read Chapters 9, 10, 11 and 12 and do the Hands On Exercises for practice.
Part 2 No Assignments in This Lesson.

Next week will be the Excel Test.

Back to top

What you should know when you complete Chapter 10:

bullet

be able to utilize collaboration to create group editing
bullet be able to track changes among different users
bullet be able to recognize the advantages and disadvantages of workbook sharing
bullet be able to Object Link or Embed Excel charts into other applications (you might need this one for your PowerPoint project)
bullet be able to recognize the issues brought up by the Compatibility Checker (why is it important?)
bullet be able to establish permissions (be careful with these...you have to remember the password)
bullet be able to use and know what are the advantages and disadvantages of digital signatures
bullet be able to use the Document Inspector
 

What you should know when you complete Chapter 12:  (will not be on test!!!)

bullet

be able to create and use templates
bullet be able to create and use document themes
bullet be able to create custom styles
bullet be able to include SmartArt into an Excel spreadsheet
bullet be able to create a Web Query
bullet be able to create and use macros

Previous Lesson       Back to top      Next Lesson

© Dr. M.L. McManus Last Updated 11/24/14