Skip to content
Microsoft Excel

Excel Advance Features & Functions


Course

Build the skills to analyze data sets, use PivotTable & Pivot Chart, develop Macros to automate repetitive tasks, collaborate with others, integrate with Office software and secure your data. Students who complete this course can be on their way to preparing for a Microsoft Office Specialist (MOS) Certification exam.

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 get 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

      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

      9. Customization

      Topics: Customizing Excel

      10. Extra Problems

      11. Microsoft Office Specialist (MOS) Certification

      Completion

      The following certificates are awarded when the course is completed:

      Certificate of completion
      Back to top