You are previewing MOS 2010 Study Guide for Microsoft® Excel® Expert.
O'Reilly logo
MOS 2010 Study Guide for Microsoft® Excel® Expert

Book Description

Demonstrate your expertise with Microsoft Excel 2010 with a MOS Expert certification. Use this Study Guide and downloadable practice files to help advance your proficiency with Excel and earn the expert-level credential that proves it.

Table of Contents

  1. MOS 2010 Study Guide for Microsoft® Excel® Expert
  2. A Note Regarding Supplemental Files
  3. Taking a Microsoft Office Specialist Exam
    1. Microsoft Office Specialist Certification
    2. Selecting a Certification Path
    3. Test-Taking Tips
    4. Certification Benefits
    5. For More Information
  4. Using This Book to Study for a Certification Exam
    1. Features and Conventions of This Book
  5. Using the Book’s Companion Content
  6. Modifying the Display of the Ribbon
  7. How to Get Support and Provide Feedback
    1. Errata & Book Support
    2. We Want to Hear from You
    3. Stay in Touch
  8. 1. Exam 77-888 Microsoft Excel 2010 Expert
    1. 1. Sharing and Maintaining Workbooks
      1. 1.1 Apply Workbook Settings, Properties, and Data Options
        1. Saving a Workbook as a Template
        2. Setting Workbook Properties
        3. Exchanging Data with XML
      2. 1.2 Apply Protection and Sharing Properties to Workbooks and Worksheets
        1. Protecting Worksheets and Workbooks
        2. Restricting User Access
        3. Adding a Password to a Workbook
      3. 1.3 Maintain Shared Workbooks
        1. Preparing to Share a Workbook
        2. Sharing a Workbook and Tracking Changes
        3. Reviewing Changes
        4. Merging Workbooks
      4. Objective Review
    2. 2. Applying Formulas and Functions
      1. 2.1 Audit Formulas
        1. Using Auditing Tools to Trace Formulas
        2. Identifying Invalid Data and Formulas
        3. Correcting Formula Errors
          1. Error Checking Options
          2. The Error Checking and Trace Error Commands
        4. Using the Watch Window
      2. 2.2 Manipulate Formula Options
        1. Using Automatic Workbook Calculation
        2. Setting Options for Iterative Calculations
      3. 2.3 Perform Data Summary Tasks
        1. Summing Data with the SUMIF and SUMIFS Functions
        2. Understanding and Defining Array Formulas
          1. Working with Array Formulas
          2. Defining Array Constants
          3. Examples of Array Functions and Formulas
      4. 2.4 Apply Functions in Formulas
        1. Using Built-in Functions
        2. Correcting Function Errors
      5. Objective Review
    3. 3. Presenting Data Visually
      1. 3.1 Apply Advanced Chart Features
        1. Adding Trendlines to Data
        2. Using a Chart Template
        3. Visualizing the Data in a Cell by Using Sparklines
          1. Sparkline Group
          2. Type Group
          3. Show Group
          4. Style Group
          5. Group Group
        4. Creating Dual-Axes Charts
      2. 3.2 Apply Data Analysis
        1. Performing a What-If Analysis
          1. Using Goal Seek
          2. Using Data Tables
          3. Building What-If Scenarios
          4. Using Solver
        2. Using Automated Analysis Tools
          1. Generating Descriptive Statistics
          2. Displaying a Histogram
          3. Using Rank And Percentile
          4. Generating Random Numbers
          5. Sampling Data
      3. 3.3 Apply and Manipulate PivotTables
        1. Building a PivotTable
        2. Working with Data in a PivotTable
          1. Using Field Headers
          2. Managing the Data
          3. Changing Your View of the Data
          4. Using the Calculations Group
          5. Using a Report Filter
        3. Slicing the Data in a PivotTable
      4. 3.4 Apply and Manipulate PivotCharts
        1. Creating a PivotChart
        2. Changing the View for a PivotChart
      5. 3.5 Use External Data Sources
        1. Creating a Data Connection
          1. From Access
          2. From Web
          3. From Text
          4. From Other Sources
        2. Using Existing Connections
        3. Editing Links
      6. Objective Review
    4. 4. Working with Macros and Forms
      1. 4.1 Create and Manipulate Macros
        1. Recording and Storing a Macro
        2. Modifying a Macro
        3. Run a Macro from the Macro Dialog Box
        4. Assigning a Keystroke to a Macro
        5. Running a Macro When You Open a Workbook
        6. Adding a Macro Button to the Ribbon
        7. Adding a Macro Button to the Quick Access Toolbar
        8. Creating an Action Macro
      2. 4.2 Insert and Manipulate Form Controls
        1. Inserting Form Controls
          1. Inserting a Command Button
          2. Setting Up a Combo Box or a List Box
          3. Inserting Check Boxes and Option Buttons
          4. Using Spin Buttons and Scroll Bars
          5. Adding a Label
        2. Defining Form Control Properties
        3. Making Use of ActiveX Controls
        4. A Simple Order Form
        5. Objective Review
  9. About the Author
  10. Copyright