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

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

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

Protect and share workbooks.                      Office Button  Save As  at bottom - Tools  General Options

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,

Office-Prepare-Inspect Doc

Office-Prepare-Restrict Permission  Restricted Access

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

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
Changing the Appearance of Data Based on Its Value
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
Creating Dynamic Charts Using PivotCharts
Creating Diagrams by Using SmartArt
Chapter 12. Printing
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
Pasting Charts into Other Documents
Chapter 15. Collaborating with Colleagues
Sharing Data Lists
Tracking and Managing Colleagues' Changes
Protecting Workbooks and Worksheets
Authenticating Workbooks
Saving Workbooks for the Web

5```

1. ### Microsoft Excel 2007

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

2. ### Chapter 1 Microsoft Office Basics

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

3. ### VBA Programming for Microsoft Office Project

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

4. ### Excel 2007

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

• ### 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 …
• ### Medical Office Communication – Coursewareobjects.com

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

Medical Office 26 Communication http://evolve.elsevier.com/klieger/medicalassisting 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 …
• ### 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 taxamendment.com 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 …
• ### OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL

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 …
• ### 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 …