Self Study Group for: Microsoft Office Certified Application

Text Preview:
Self Study Group for:

        Microsoft Office
Certified Application Specialist:
        Excel 2007 exam

   October/November 2008

Skills measured on Excel 2007 Exam
This exam measures your ability to accomplish the technical tasks listed in the following table.

Skills measured by Exam 77-602 (Excel)

Creating and Manipulating Data

Insert data by using AutoFill.                       Home Tab, Editing Group, Fill
                                                     Click and drag. After release, click Auto Fill Options menu.

Ensure data integrity.                               Data Tab, Data Tools  Data Validation

Modify cell contents and formats.                    Paste Special (Transpose, Mathematical)

Change worksheet views.                              View Toolbar at bottom (Normal, Page Layout, Page Break
                                                     Preview), Zoom, Freezing and Splitting Windows (View,

Manage worksheets.                                   Right click worksheet tab, hide / unhide, Move/Copy

Formatting Data and Content

Format worksheets.                                   Page Layout, Themes, Sheet Options  Gridlines, Rename
                                                     Worksheet, Color Code

Insert and modify rows and columns.                  Right mouse click, Insert/Delete, Resize, Hide/Unhide

Format cells and cell content.                       Home  Number  Formats, Custom; Home  Alignment 
                                                     Merge and Center/ Merge Across / Merge Cells, Formatting
                                                     part of cell versus whole cell, color, border, cell styles (Home-
                                                     Styles-Cell Styles)

Format data as a table.                              Home-Styles-Format As Table, changing range by clicking and
                                                     dragging; Table Styles: Design-Table Styles-More

Creating and Modifying Formulas

Reference data in formulas.                          Absolute v Relative, Referencing Worksheets, Named Ranges

Summarize data by using a formula.                   Sum, count, countA (not empty), average, min, max

Summarize data by using subtotals.                   Subtotals (sum, count, average, max, product, count #, stddev,
                                                     stddevp, var, varp)  NOTE: Subtotals are SUMMARIES  not
                                                     necessarily just addition.Data  Outline  Subtotal

Conditionally summarize data by using a formula.     AverageIf, CountIf, SumIf, AverageIfs, Countifs, SumifS 
                                                     multiple criteria up to 127(P. 249)

Look up data by using a formula.                     Hlookup, vlookup (remind on add ins), =vlookup("2% milk",
                                                     Products. 5. False)

Use conditional logic in a formula.                  If, and, or, not, iferror

Format or modify text by using formulas.             Lower, proper, upper, replace, substitute

Display and print formulas.                          Formulas  Formula Auditing  Show Formulas

Presenting Data Visually

Create and format charts.                            Insert-Charts

Modify charts.                                       Moving and resizing, change chart location, Layout tab-Labels-
                                                     Chart Title; Axis Titles; Legend; Data Labels,

Apply conditional formatting.                        Home-Styles-Conditional Formatting; Conditional Formatting 
                                                     Clear Rules,

Insert and modify illustrations.                     Insert-Illustrations Group - Picture, Smart Art, Shapes

Skills measured by Exam 77-602 (Excel)

Outline data.                                     Data-Outline-AutoOutline in Group List. To Ungroup  Data 
                                                  Outline- Ungroup

Sort and filter data.                             Home-Editing  Sort & Filte or Data tab, Sort and Filter group,
                                                  Custom Sort for more than one column. Note that you can
                                                  remove a sort level from a multi-level sort

                                                  Home-Editing-Sort & Filter  Filter/Filter Clear

Collaborating on and Securing Data

Manage changes to workbooks.                      Review Tab  Changes  Track Changes  Highlight Changes,
                                                  Track Changes While Editing; Review  Changes 
                                                  Accept/Reject Changes

                                                  Comments  Review  Comments-New Comment

Protect and share workbooks.                      Office Button  Save As  at bottom - Tools  General Options
                                                   Password to open or modify

                                                  Review  Changes = Protect Workbook  Protect Structure
                                                  and Windows (to reverse as well)

                                                  First you need to go to Home-Cells-Format-Lock Cells that you
                                                  want locked. Then Review Tab  Protect Sheet  Allow all
                                                  users of this worksheet to:

                                                  Review  Changes  Share Workbook.

Prepare workbooks for distribution.               Office-Prepare-Properties  Doc Info Panel  Property Views
                                                  and Options, Doc Prop  Server, Doc Prop  common,
                                                  Advanced Prop

                                                  Office-Prepare-Inspect Doc

                                                  Office-Prepare-Restrict Permission  Restricted Access

                                                  Office-Prepare-Add Digital Signature

                                                  Office-Prepare  Mark as Final (makes read only)

Save workbooks.                                   Office  Save As

                                                  Compatibility Checker  Office-Prepare-Run Compatibility

Set print options for printing data, worksheets, and Page Layout  Page Setup  Print Area  Set Print Area
                                                     Insert Page Break (Page Layout-Page Setup  Breaks, Insert
                                                     Page Break/Remove Page Break) or reset all page breaks to
                                                     remove all manual page breaks

                                                  Page Margins (Page Layout, Page Setup, Margins); PL/PS-
                                                  Orientation Size, Scale

                                                  Headers and Footers Insert-Text-Header & Footer

                           Homework Assignments:

       Required Manual: Microsoft Press Excel 2007 Step by Step

Week One:
     Chapter 1. What's New in Excel 2007?
            Becoming Familiar with the New User Interface
            Managing Larger Data Collections
            Using the New Microsoft Office File Format
            Formatting Cells and Worksheets
            Managing Data Tables More Effectively
            Creating Formulas More Easily by Using Formula AutoComplete
            Summarizing Data Using New Functions
            Creating Powerful Conditional Formats
            Creating More Attractive Charts
            Controlling Printouts More Carefully
     Chapter 2. Setting Up a Workbook
            Creating Workbooks
            Modifying Workbooks
            Modifying Worksheets
            Customizing the Excel 2007 Program Window
     Chapter 3. Working with Data and Data Tables
            Entering and Revising Data
            Moving Data Within a Workbook
            Finding and Replacing Data
            Correcting and Expanding Upon Worksheet Data
            Defining a Table

Week Two:
     Chapter 4. Performing Calculations on Data
            Naming Groups of Data
            Creating Formulas to Calculate Values
            Summarizing Data That Meets Specific Conditions
            Finding and Correcting Errors in Calculations
     Chapter 5. Changing Document Appearance
            Formatting Cells
            Defining Styles
            Applying Workbook Themes and Table Styles
            Making Numbers Easier to Read
            Changing the Appearance of Data Based on Its Value
            Adding Images to a Document
     Chapter 6. Focusing on Specific Data Using Filters
            Limiting Data That Appears on Your Screen
            Manipulating List Data
            Defining Valid Sets of Values for Ranges of Cells

Week Three:
     Chapter 7. Reordering and Summarizing Data
            Sorting Data Lists
            Organizing Data into Levels
            Looking Up Information in a Data List
     Chapter 8. Combining Data from Multiple Sources
            Using Data Lists as Templates for Other Lists
            Linking to Data in Other Worksheets and Workbooks

            Consolidating Multiple Sets of Data into a Single Workbook
            Grouping Multiple Sets of Data
     Chapter 9. Analyzing Alternative Data Sets
            Defining and Editing Alternative Data Sets
            Defining Multiple Alternative Data Sets
            Varying Your Data to Get a Desired Result Using Goal Seek
            Finding Optimal Solutions with Solver
            Analyzing Data with Descriptive Statistics

Week Four:
     Chapter 10. Creating Dynamic Lists with PivotTables
            Analyzing Data Dynamically with PivotTables
            Filtering, Showing, and Hiding PivotTable Data
            Editing PivotTables
            Formatting PivotTables
            Creating PivotTables from External Data
     Chapter 11. Creating Charts and Graphics
            Creating Charts
            Customizing the Appearance of Charts
            Finding Trends in Your Data
            Creating Dynamic Charts Using PivotCharts
            Creating Diagrams by Using SmartArt
     Chapter 12. Printing
            Adding Headers and Footers to Printed Pages
            Preparing Worksheets for Printing
            Printing Data Lists
            Printing Parts of Data Lists
            Printing Charts

Week Five:
     Chapter 13. Automating Repetitive Tasks with Macros
            Introducing Macros
            Creating and Modifying Macros
            Running Macros When a Button Is Clicked
            Running Macros When a Workbook Is Opened
     Chapter 14. Working with Other Microsoft Office System Programs
            Including Microsoft Office System Documents in Excel 2007 Worksheets
            Storing Excel 2007 Documents as Parts of Other Microsoft Office System
            Creating Hyperlinks
            Pasting Charts into Other Documents
     Chapter 15. Collaborating with Colleagues
            Sharing Data Lists
            Managing Comments
            Tracking and Managing Colleagues' Changes
            Protecting Workbooks and Worksheets
            Authenticating Workbooks
            Saving Workbooks for the Web

Download Link:
Share Link: Forum Link:
  • You may also like

    1. Picture: Microsoft Excel 2007

      Microsoft Excel 2007

      2007 by CustomGuide, Inc. 1502 Nicollet Avenue South, Suite 1; Minneapolis, MN 55403 This material i...

    2. Picture: Chapter 1 Microsoft Office Basics

      Chapter 1 Microsoft Office Basics

      Chapter 1 Microsoft Office Basics Key Concepts Practice Data Files Understanding the Office interfac...

    3. Picture: VBA Programming for Microsoft Office Project

      VBA Programming for Microsoft Office Project

      VBA Programming for Microsoft Office Project Versions 98 Through 2007 Rod Gill Microsoft Project MVP...

    4. Picture: Excel 2007

      Excel 2007

      2007 by CustomGuide, Inc. 1502 Nicollet Avenue South, Suite 1; Minneapolis, MN 55403 This material i...

More on Computer & Internet

  • Picture: Name: Period GL UNIT 5: SIMILARITY

    Name: Period GL UNIT 5: SIMILARITY

    File Size: 648.11 KB, Pages: 15, Views: 504,493 views

    Name: Period GL U N IT 5 : S IM IL A R I T Y I can define, identify and illustrate the following terms: Similar Cross products Scale Factor SAS ~ Similar Polygons Similarity Ratio Indirect measurement Ratio Similarity Statement AA ~ Proportion Geometric …
  • Picture: Medical Office Communication -

    Medical Office Communication –

    File Size: 6,159.55 KB, Pages: 40, Views: 474,655 views

    Medical Office 26 Communication The daily functioning of a medical practice relies on good communication skills. As you have learned in previous chapters, effective communication involves excellent skills not only in speaking and listening but also in conveying nonverbal and written messages. Medical assistants …
  • Picture: State Income Tax Return Amendment Form provided by

    State Income Tax Return Amendment Form provided by

    File Size: 2,802.37 KB, Pages: 8, Views: 420,124 views

    State Income Tax Return Amendment Form provided by You can download this form to file a state income tax amendment. For details on how to file or efile the amendment please check the California tax return amendment page. You can also get your tax …


    File Size: 0.00 KB, Pages: 5, Views: 40,055 views

    OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL STARTUP HANDBOOK FEBRUARY 18, 2015 Open Channel Hydraulics Akan Solution Manual Startup Handbook OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL DOWNLOAD: OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL Getting Open Channel Hydraulics Akan Solution Manual is easy and simple. Mostly you …
  • Picture: Lab 9: Respiratory Physiology - College of Charleston

    Lab 9: Respiratory Physiology – College of Charleston

    File Size: 365.27 KB, Pages: 8, Views: 22,725 views

    Lab 8 Respiratory Physiology Laboratory 8 Respiratory Physiology The primary function of the respiratory system is to exchange oxygen and carbon dioxide between air and blood. This function sustains metabolism (via increasing blood oxygen and releasing blood carbon dioxide) and regulates blood pH. To completely …

Leave a Reply

Your email address will not be published. Required fields are marked *