MOS 2016 Study Guide for Microsoft Excel Expert

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