Advanced Microsoft Excel 2016

Advanced Microsoft Excel 2016 Courseware (EXC2016.3)

Microsoft ExcelMicrosoftMicrosoft Office

This Advanced Microsoft Excel 2016 equips you with essential Excel skills for data manipulation and analysis, workbook auditing, and collaborative work. Master the art of creating and working with pivot tables, and learn how to audit worksheets for data dependencies and precedents. Further, delve into various data tools such as text-to-columns conversion, data validation, and goal seeking. Enhance your collaborative work efficiency with workbook and worksheet protection methods, and empower your Excel proficiency with macro recording and editing. Learn to use and customize sparklines, prepare your workbook for internationalization and accessibility, and understand how to import and export data. The course also covers updates in Excel 2013 and Excel 2016, focusing on features like slicers, PivotTable timelines, multi-select options in slicers, and workbook sharing.

This is the updated version of 55167AC from the retired Microsoft Courseware Marketplace. Note that we have newer versions: one that covers Excel 2019 and one that covers Excel 2021 and 365.

Publisher: Webucator

Benefits

  • Develop skills to manipulate and analyze data using Pivot Tables, PowerPivot, and Pivot Charts.
  • Learn to audit worksheets to identify errors and ensure data integrity.
  • Use advanced data tools for text conversion, data validation, and goal seeking.
  • Understand how to protect worksheets and workbooks for secure and efficient collaboration.
  • Gain competence in recording, running, and editing macros to automate tasks.
  • Master the use of Sparklines for concise data visualization within a single cell.
  • Learn to prepare your workbook for internationalization and accessibility.
  • Stay updated with the latest features introduced in Excel 2013 and Excel 2016.
  • Get hands-on practice through numerous exercises to reinforce your learning and skills.

Outline

  1. Using Pivot Tables
    1. Creating Pivot Tables
      1. Preparing Your Data
      2. Inserting a Pivot Table
    2. Inserting Slicers
    3. Working with Pivot Tables
      1. Grouping Data
      2. Using PowerPivot
      3. Managing Relationships
    4. Inserting Pivot Charts
    5. More Pivot Table Functionality
    6. Exercise: Working with Pivot Tables
  2. Auditing Worksheets
    1. Tracing Precedents
    2. Exercise: Tracing Precedents
    3. Tracing Dependents
    4. Exercise: Tracing Dependents
    5. Showing Formulas
  3. Data Tools
    1. Converting Text to Columns
    2. Exercise: Converting Text to Columns
    3. Linking to External Data
    4. Controlling Calculation Options
    5. Data Validation
    6. Exercise: Using Data Validation
    7. Consolidating Data
    8. Exercise: Consolidating Data
    9. Goal Seek
    10. Exercise: Using Goal Seek
  4. Working with Others
    1. Protecting Worksheets and Workbooks
      1. Password Protecting a Workbook
      2. Removing Workbook Metadata
    2. Exercise: Password Protecting a Workbook
      1. Password Protecting a Worksheet
    3. Exercise: Password Protecting a Worksheet
      1. Password Protecting Ranges in a Worksheet
    4. Exercise: Password Protecting Ranges in a Worksheet
    5. Marking a Workbook as Final
  5. Recording and Using Macros
    1. Recording Macros
      1. Copy a Macro from Workbook to Workbook
    2. Exercise: Recording a Macro
    3. Running Macros
    4. Editing Macros
    5. Adding Macros to the Quick Access Toolbar
      1. Managing Macro Security
    6. Exercise: Adding a Macro to the Quick Access Toolbar
  6. Random Useful Items
    1. Sparklines
      1. Inserting Sparklines
      2. Customizing Sparklines
    2. Exercise: Inserting and Customizing Sparklines
    3. Preparing a Workbook for Internationalization and Accessibility
      1. Display Data in Multiple International Formats
      2. Modify Worksheets for Use with Accessibility Tools
      3. Use International Symbols
      4. Manage Multiple Options for +Body and +Heading Fonts
    4. Importing and Exporting Files
      1. Importing Delimited Text Files
    5. Exercise: Importing Text Files
      1. Exporting Worksheet Data to Microsoft Word
    6. Exercise: Copying Data from Excel to Word
      1. Exporting Excel Charts to Microsoft Word
    7. Exercise: Copying Charts from Excel to Word
  7. Features New in Excel 2013
    1. Using Slicers to Filter Data
    2. Exercise: Filtering Data with Slicers
    3. Creating a PivotTable Timeline
    4. Exercise: Creating a Timeline
    5. Creating a Standalone PivotChart
    6. Workspaces in Excel 2013
  8. New Features in Excel 2016
    1. PivotTable Updates
    2. Ink Equations
    3. Multi-Select Option in Slicers
    4. Quick Shape Formatting
    5. Sharing
    6. Exercise: Sharing a Workbook

Required Prerequisites

None

License

Length: 1 day | $20.00 per copy

LicenseRequest More InformationDownload Sample CopyRequest Trainer Evaluation Copy
What is Included?
  • Student Manual
  • Student Class Files