Skip to content
Microsoft Office Specialist (MOS)

Microsoft Excel 2016 Advance


Course

Getting Started with Microsoft Office Excel 2016; Performing Calculations; Modifying a Worksheet; Formatting a Worksheet; Printing Workbooks; Managing Workbooks; Working with Functions; Working with Lists; Analyzing Data; Visualizing Data with Charts; Using PivotTables and PivotCharts; Working with Multiple Worksheets and Workbooks; Using Lookup Functions and Formula Auditing; Sharing and Protecting Workbooks; Automating Workbook Functionality; Creating Sparklines and Mapping Data; Forecasting Data

Prerequisite

  • Microsoft Excel Intermediate course or equivalent.

Learning Objectives

By the end of the course, you should be able to:

  • Use SUMIF, SUMIFS, COUNTIF, and COUNTIFS functions
  • Use VLookup and Hlookup functions
  • Look for an exact match or closer match.
  • Create a drop-down list for a set of cells to pick the right set of data.
  • For a particular set of cells, set the rules to insert values
  • Create and view multiple scenarios
  • Summarize scenario results in a separate worksheet
  • Filter, Show, and Hide PivotTable Data
  • Create, Edit & Format PivotTables
  • Analyze Data Dynamically Using PivotTables
  • Filter a PivotTable by using multiple methods
  • Create a formula that references PivotTable data
  • Use PivotTables to analyze data dynamically.
  • Creating & Using PivotChart
  • Create Dynamic Charts Using PivotCharts
  • Create, update, modify, and filter a PivotChart
  • Use PivotCharts to create dynamic charts.
  • Use Goal Seek to get the desired result.
  • Use Solver to find the optimal solution.
  • Apply conditional formatting to the table
  • Analyze Data Using Descriptive Statistics
  • Create charts & customize the appearance of the chart.
  • Ability to find trends in data.
  • Use sparklines to summarize data
  • Enable, Create and Modify Macros
  • Examine, step through, and run a macro
  • Run Macros When a Button is Clicked 
  • Add macro buttons to the Quick Access Toolbar
  • Create hyperlinks.
  • Share workbooks.
  • Manage comments.
  • Track and manage colleagues’ changes.
  • Save workbooks for the Web.
  • Save a workbook as a PDF
  • Add, display, review, and delete comments
  • Password-protect a workbook and a worksheet

    Assumptions

    • The student has access to Microsoft Excel software for hands-on exercises. 
    • The student is familiar with writing formula and function in Excel
    • The student has at least 1 year of experience using Microsoft Excel or attended Excel Intermediate class.

      Who should take this Course?

      • The student has at least 1 year of experience using Microsoft Excel or attended Excel Intermediate class.

      What do you need?

      • At least 1 year of experience using Microsoft Excel or attended Excel Intermediate class.
      • Excel 2016 installed on your computer. 

      How Do I Get This Course?

      • This course is available as a single title purchase or as part of a package.
      • If you are currently NRCLC online subscriber, the course will be instantly added to your library.

      Here is the course outline:

      1. Using Formula and Function

      Topics: • Using SUMIF, SUMIFS functions • Using COUNTIF and COUNTIFS functions

      2. HLookup vs VLookup

      Topics: • Use VLookup, HLookup, and Match to display the information by looking in a set of data

      3. Financial Functions

      Topics include: Function NPV, PV, IRR, IFERROR, Small, Large, Time Period

      4. Data Validation

      Topics: • Create a dropdown list for a set of cells to pick the right set of data. • For a particular set of cells, set the rules to insert values

      5. Using Scenarios and Goal Seeking

      Topics: • Create and view multiple scenarios • Summarize scenario results in a separate worksheet • Get a Desired Result Using Goal Seek • Use Goal Seek to determine a solution

      6. Using PivotTable & PivotChart

      Topics: • Analyze Data Dynamically Using PivotTables • Filter, Show, and Hide PivotTable Data • Create, Edit & Format PivotTables • Filter a PivotTable by using multiple methods • Show and hide details in a PivotTable • Create a formula that references PivotTable data • Apply a number format, PivotTable style, banded rows, and conditional formatting • Create Dynamic Charts Using PivotCharts • Create, update, modify, and filter a PivotChart

      7. Creating and Using Macros

      Topics: • Enable and Exam Macros • Create and Modify Macros • Run Macros When a Button is Clicked • Examine, step through, and run a macro • Record, edit, save and run a macro • Add macro buttons to the Quick Access Toolbar

      8. Graph, Collaboration and Customization

      Topics: Sparkline, Trendline, Forecasting, Collaboration, and Customizing Excel

      Completion

      The following certificates are awarded when the course is completed:

      Certificate of completion
      Back to top