MOS 2013 Study Guide for Microsoft® Excel® Expert

Table of Contents

1. Special Upgrade Offer
2. A Note Regarding Supplemental Files
3. Introduction
   1. Who this book is for
   2. How this book is organized
   3. Download the practice files
   4. Ebook edition
   5. Get support and give feedback
      1. Errata
      2. We want to hear from you
      3. Stay in touch
4. 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
5. I. Exams 77-427 and 77-428: Microsoft Excel 2013 Expert
   1. 1. Manage and Share Workbooks
      1. 1.1 Manage multiple workbooks
         1. Modifying workbook templates
         2. Managing workbook versions
         3. Copying styles between templates
         4. Copying macros between workbooks
         5. Connecting to external data
      2. 1.2 Prepare workbooks for review
         1. Tracking changes
         2. Protecting workbooks for sharing
            1. Restricting editing and limiting editors
            2. Protecting worksheet structure
            3. Removing workbook metadata
            4. Controlling calculation
            5. Encrypting workbooks by using a password
            6. Marking as final
      3. 1.3 Manage workbook changes
         1. Displaying all changes
         2. Reviewing changes
         3. Managing comments
         4. Merging workbooks
         5. Identifying errors
         6. Troubleshooting by using tracing
      4. Objective review
   2. 2. Apply Custom Formats and Layouts
      1. 2.1 Apply custom data formats
         1. Creating custom formats (number, time, date)
            1. Creating custom accounting formats
            2. Creating custom date formats
            3. Creating custom time formats
         2. Using advanced Fill Series options
            1. Creating custom fill sequences
      2. 2.2 Apply advanced conditional formatting and filtering
         1. Creating custom conditional formats
         2. Using functions to format cells
         3. Creating advanced filters
      3. 2.3 Apply custom styles and templates
         1. Creating custom templates
         2. Creating and modifying cell styles
         3. Creating custom color and font formats
         4. Creating themes
         5. Creating form fields
            1. Inserting form controls
      4. 2.4 Prepare workbooks for internationalization and accessibility
         1. Modifying worksheets for use with accessibility tools
         2. Displaying data in multiple international formats
            1. Working in other languages
            2. Utilizing international symbols
      5. Objective review
   3. 3. Create Advanced Formulas
      1. 3.1 Apply functions in formulas
         1. Using nested functions
         2. Using the IF, AND, and OR functions
         3. Using the SUMIFS, AVERAGEIFS, and COUNTIFS functions
         4. Using financial functions
            1. Calculating investments
            2. Calculating depreciation
            3. Analyzing securities
      2. 3.2 Look up data by using functions
         1. Using the VLOOKUP and HLOOKUP functions
         2. Using the LOOKUP function
         3. Using the TRANSPOSE function
      3. 3.3 Apply advanced date and time functions
         1. Using the NOW and TODAY functions
         2. Using functions to serialize dates and times
            1. Using the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions
            2. Using the DATEVALUE and TIMEVALUE functions
            3. Using the WEEKDAY, WORKDAY, and NETWORKDAYS functions
      4. 3.4 Create scenarios
         1. Using what-if analysis tools
            1. Using Goal Seek
            2. Using Solver
         2. Using the Scenario Manager
         3. Consolidating data
            1. Consolidating by position
            2. Consolidating by category
      5. Objective review
   4. 4. Create Advanced Charts and Tables
      1. 4.1 Create advanced chart elements
         1. Adding trendlines to charts
         2. Creating dual-axis charts
         3. Creating custom chart templates
      2. 4.2 Create and manage PivotTables
         1. Creating PivotTables
            1. Formatting PivotTables
         2. Modifying field selections and options
            1. Using field headers
            2. Changing your view of the data
            3. Using calculated fields and items
         3. Creating slicers
         4. Using PowerPivot
            1. Managing relationships
            2. Creating hierarchies
      3. 4.3 Create and manage PivotCharts
         1. Creating PivotCharts
         2. Manipulating options in existing PivotCharts
         3. Applying styles to PivotCharts
      4. Objective review
6. Index
7. About the Author
8. Special Upgrade Offer
9. Copyright