55166A: Intermediate Microsoft Excel 2016




    1 Day(s) Course


    Official Courseware


    Certified Trainer


    Exam Voucher


    1:1 Training Available on Demand


    Customize Your Batch

    Course Information

    The Advanced Microsoft Excel 2016 training is a one-day class that is designed for students who have basic knowledge of Excel 2013 or 2016 yet want to advance their skill set by learning the skill set of working with advanced formulas, lists, and illustrations. During this class, students will also work with charts and advanced formatting styles.

    Completion of the course equips the participants with the following skill set:

    • Learning to use formulas and functions.
    • Converting, sorting, filtering, and managing lists.
    • Creating and modifying charts.
    • Learning to use conditional formatting and styles.
    • Inserting and modifying illustrations in a worksheet.
    • Learning to work with tables.


    Module 1: Advanced Formulas

    In this module, participants will learn how to work with formulas.


    • Use Named Ranges in Formulas
    • Name a Single Cell
    • Name a Range of Cells
    • Name Multiple Single Cells Quickly
    • Use Formulas That Span Multiple Worksheets
    • Use the IF Function
    • Use AND/OR Functions
    • Use the SUMIF, AVERAGEIF, and COUNTIF Functions
    • Use the PMT Function
    • Use the LOOKUP Function
    • Use the VLOOKUP Function
    • Use the HLOOKUP Function
    • Use the CONCATENATE Function
    • Use the TRANSPOSE Function
    • Use the PROPER, UPPER, and LOWER Functions
    • The UPPER Function
    • The LOWER function
    • The TRIM Function
    • Use the LEFT, RIGHT, and MID Functions
    • The MID Function
    • Use Date Functions
    • Use the NOW and TODAY Functions
    • Create Scenarios
    • Utilize the Watch Window
    • Consolidate Data
    • Enable Iterative Calculations
    • What-If Analysis
    • Use Financial Functions
    • Use the Scenario Manager

    Module 2: Work with Lists

    In this module, participants will learn how to work with lists.


    • Convert a List to a Table
    • Remove Duplicates from a List
    • Sort Data in a List
    • Filter Data in a List
    • Group and Ungroup Data in a List
    • Add Subtotals to a List

    Module 3: Work with Illustrations

    In this module, participants will learn how to work with illustrations.


    • Work with Clip Art
    • Use Shapes
    • Work with SmartArt

    Module 4: Visualize Your Data

    In this module, participants will learn how to work with charts, objects, and text boxes.


    • Create a Custom Chart Template
    • Insert Charts
    • Add and Format Objects
    • Insert a Text Box

    Module 5: Work with Tables

    In this module, participants will learn how to work with tables.


    • Format Data as a Table
    • Move between Tables and Ranges
    • Define Titles
    • Modify Tables

    Module 6: Advanced Formatting

    In this module, participants will learn how work with formatting options in Excel.


    • Apply Conditional Formatting
    • Work with Styles
    • Create and Modify Templates

    Module 7: Microsoft Excel Features that Were New in 2013

    In this module, participants will learn how to use some features that were new in 2013.


    • New Functions in Excel 2013
    • Use New Chart Tools
    • Use the Chart Recommendation Feature
    • Use the Quick Analysis Tool

    Module 8: Features New in 2016

    In this module, participants will learn how to use the new charts in 2016.


    • New Charts
    • Treemap
    • Sunburst
    • Histogram
    • Waterfall
    • Box and Whisker
    • Pareto


    This course is beneficial for students who have little or no familiarity with Microsoft Excel 2013 or more experienced Excel users who are interested in learning the topics covered in this course on the 2013 interface.


    Before attending the course, a candidate must have basic Excel experience.


    Name Code Fee



    What Our Trainees Say

    Mercury Solutions Ltd. is rated 4.6 stars by based on 18 reviews.