O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MOS 2016 Study Guide for Microsoft Excel Expert

Book Description

Hone your advanced Excel 2016 skills. And earn the credential that proves it!


Demonstrate your expertise with Microsoft Excel! Designed to help you practice and prepare for Microsoft Office Specialist (MOS): Excel Expert 2016 certification, this official Study Guide delivers:

  • In-depth preparation for each MOS objective

  • Detailed procedures to help build the skills measured by the exam

  • Hands-on tasks to practice what you’ve learned

  • Ready-made practice files

  • Sharpen the skills measured by these objectives:

  • Create and Manage Workbooks

  • Apply Custom Formatting and Layouts

  • Create Advanced Formulas

  • Perform Data Analysis

  • Create Advanced Charts and PivotTables

  • Table of Contents

    1. Cover
    2. Title Page
    3. Copyright Page
    4. Contents
    5. Introduction
      1. Who this book is for
      2. How this book is organized
      3. Download the practice files
      4. Ebook edition
      5. Errata, updates, and book support
      6. We want to hear from you
      7. Stay in touch
    6. 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
    7. Exam 77-728. Excel 2016 Expert: Interpreting Data for Insights
      1. Prerequisites
    8. Objective group 1. Manage workbook options and settings
      1. Objective 1.1: Manage workbooks
        1. Save a workbook as a template
        2. Hide or display ribbon tabs
        3. Enable macros in a workbook
        4. Copy macros between workbooks
        5. Reference data in another workbook
        6. Reference table data by using structured references
      2. Objective 1.1 practice tasks
      3. Objective 1.2: Manage workbook review
        1. Restrict editing
        2. Protect workbook structure
        3. Encrypt a workbook with a password
        4. Manage workbook versions
        5. Configure formula calculation options
      4. Objective 1.2 practice tasks
    9. Objective group 2. Apply custom data formats and layouts
      1. Objective 2.1: Apply custom data formats and validation
        1. Create custom data formats
        2. Populate cells by using advanced Fill Series options
        3. Configure data validation
      2. Objective 2.1 practice tasks
      3. Objective 2.2: Apply advanced conditional formatting and filtering
        1. Create custom conditional formatting rules
        2. Create conditional formatting rules that use formulas
        3. Manage conditional formatting rules
      4. Objective 2.2 practice tasks
      5. Objective 2.3: Create and modify custom workbook elements
        1. Create and modify cell styles
        2. Create custom themes and theme elements
        3. Create and modify simple macros
        4. Insert and configure form controls
      6. Objective 2.3 practice tasks
      7. Objective 2.4: Prepare a workbook for internationalization
      8. Objective 2.4 practice tasks
    10. Objective group 3. Create advanced formulas
      1. Objective 3.1: Apply functions in formulas
        1. Insert functions into a formula
        2. Perform logical operations by using the IF, AND, OR, and NOT functions
        3. Perform logical operations by using nested functions
        4. Perform statistical operations by using the SUMIFS, AVERAGEIFS, and COUNTIFS functions
      2. Objective 3.1 practice tasks
      3. Objective 3.2: Look up data by using functions
      4. Objective 3.2 practice tasks
      5. Objective 3.3: Apply advanced date and time functions
        1. Reference the date and time by using the NOW and TODAY functions
        2. Serialize numbers by using date and time functions
      6. Objective 3.3 practice tasks
      7. Objective 3.4: Perform data analysis and business intelligence
        1. Import, transform, combine, display, and connect to data
        2. Consolidate data
        3. Perform what-if analysis by using Goal Seek and Scenario Manager
        4. Use cube functions to get data out of the Excel data model
        5. Calculate data by using financial functions
      8. Objective 3.4 practice tasks
      9. Objective 3.5: Troubleshoot formulas
        1. Trace precedence and dependence
        2. Monitor cells and formulas by using the Watch Window
        3. Validate formulas by using error-checking rules
        4. Evaluate formulas
      10. Objective 3.5 practice tasks
      11. Objective 3.6: Define named ranges and objects
        1. Name a cell or range
        2. Name a table
        3. Manage named ranges and objects
      12. Objective 3.6 practice tasks
    11. Objective group 4. Create advanced charts and tables
      1. Objective 4.1: Create advanced charts
        1. Add trendlines to charts
        2. Create dual-axis charts
        3. Save a chart as a template
      2. Objective 4.1 practice tasks
      3. Objective 4.2: Create and manage PivotTables
        1. Create PivotTables
        2. Modify PivotTable field selections and options
        3. Create slicers
        4. Group PivotTable data
        5. Reference data in a PivotTable by using the GETPIVOTDATA function
        6. Add calculated fields
        7. Format data
      4. Objective 4.2 practice tasks
      5. Objective 4.3: Create and manage PivotCharts
        1. Create PivotCharts
        2. Modify PivotCharts
        3. Drill down into PivotChart details
      6. Objective 4.3 practice tasks
    12. Index
    13. About the author
    14. Survey