Skip to content
Data Analyst

Microsoft Excel 2019 Advance


Course
Purchase for $49

Learn to use Performance Calculations;  Work with Advanced Functions; Work with Lists; Analyze Data; Visualize Data with Charts; Use PivotTables and PivotCharts; Working with Multiple Worksheets and Workbooks; Use Lookup Functions and Formula Auditing; Share and Protecte Workbooks; Automate Workbook Functionality; Create Sparklines, Map Data, and identify trend.

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 data set.
  • For a particular set of cells, set the rules to insert values
  • Create and view multiple scenarios
  • Summarize the 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 formulas and functions in Excel
    • The student has at least one year of experience using Microsoft Excel or attended an Excel Intermediate class.

      Who should take this Course?

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

      What do you need?

      • At least one year of experience using Microsoft Excel or attended Excel Intermediate class.
      • Excel 2019 is 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 an NRCLC online subscriber, the course will instantly be 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. What-if-Analysis

      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