Text Preview:

Self Study Group for:

        Microsoft Office
Certified Application Specialist:
        Excel 2007 exam

   October/November 2008




                                    1
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,
                                                     Window)

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




                                                                                                                         2
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
                                                  Checker

Set print options for printing data, worksheets, and Page Layout  Page Setup  Print Area  Set Print Area
workbooks.
                                                     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




                                                                                                                  3
                           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


                                                                          4
            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
            Documents
            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




                                                                                     5

Download Link: Share Link: Forum Link: