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

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 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 9 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  -  No content will be covered from this chapter.


Getting Started:  Excel - Chapter 10

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-INS–Independent computer programs that you can add to enhance the functionality of Excel

  • TEMPLATE–Partially completed workbook used to create other workbooks. 

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

EMBEDDING–The 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 CHECKER–Used to determine what properties of Excel will be available when you save the document in an older version of Excel

PROPERTY–A collection of metadata associated with a file

DOCUMENT INSPECTOR–Checks Excel files for hidden data and personal information

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

DIGITAL SIGNATURE–An 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 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

BACKGROUND–An image placed behind the worksheet data.

STYLE–A set of formatting options applied to cells.

CUSTOM STYLES–Can be used in all Excel workbooks

TIP–The 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 WORKSHEETS–Prevents modification of formulas and text but enables you to change values in unprotected cells.

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

HYPERLINKS–Links 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.

  • SmartArt–Is a graphic organizer diagram that visually presents information to communicate a message

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

WEB QUERY–Enables Excel to go to a particular Web site to obtain the Web based information

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

COMMENTS–Provide information about the macro, but do not affect its execution and are considered documentation

MACRO RECORDER–Records every command you execute

PIT–Everything 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 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 create and use templates
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 11:

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 (will not be on test!!!)

Previous Lesson       Back to top      Next Lesson

© Dr. M.L. McManus Last Updated 03/20/14