Overview

In this instructor-led, online training course, students will be provided with a foundation for Excel knowledge and skills, which you can build upon to eventually become an expert in data manipulation.

Course Instructor: Ashley Hunt

Course Outline

01. What’s New Excel?

  • Video 1: Excel overview for newer users
  • Video 2: Flash Fill and Quick Analysis
  • Video 3: New Features
  • Customize the ribbon
  • TextJoin
  • Concat
  • Insights
  • 3D images

02. ABC’s of Sort & Filter

  • Apply A Simple Filter To a Range
  • Apply A Top 10 And Custom Filter To a Range
  • Apply an Advanced Filter With Multiple OR Criteria
  • Apply an Advanced Filter With Complex Criteria
  • Apply an Advanced Filter With Function-Driven Criteria
  • Convert A Range into A Table and Add a Total Row
  • Format A Table Using Table Styles
  • Create A Custom Table Style
  • Conditional formatting and help

03. Clean Your Data!

  • Text to Columns
  • Split Delimited Data Using
  • Split Fixed Width Data Using Text to Columns
  • Consolidate Data from Multiple Data Ranges
  • Use Data Consolidation to Generate Quick Subtotals from Tables
  • Remove Duplicate Values from A Table

04. Get inFORMed!

  • Setting Up a Form
  • Adding A Dropdown List
  • Conditional Formatting
  • Add Group Box and Option Button Controls to A Worksheet Form
  • Add A Combo Box Control to A Worksheet Form
  • Add forms to quick access toolbar

05. Mathematically challenged?

  • Named ranges
  • SUM
  • MAX AND MIN
  • IF
  • TRIM
  • SUMIF
  • COUNTIF / AND
  • LEFT/ RIGHT
  • TEXTJOIN
  • PROPER, UPPER, LOWER
  • Move or copy formula sheets
  • CONCATINATE
  • VLOOKUP
  • Use the Watch Window to Monitor Cell Values

06. Hands Off My Work!

  • Prevent Unauthorized Users from Opening or Modifying Workbooks/password protect
  • Control The Changes Users Can Make to Workbooks
  • Restrict The Cells Users Are Allowed to Change
  • Allow Different Levels of Access to A Worksheet with Multiple Passwords
  • Digital Signatures
  • Accept and Reject Changes to Shared Workbooks
  • Totally Hide Cell Content from Prying Eyes

07. Let Macros do it:

  • Record A Macro
  • Record A Macro with Absolute/Relative References
  • Use Shapes to Run Macros
  • Run A Macro from A Button Control /Intro to VBA

08. Chart, Spark and Pivot:

  • Intro to charts and sparklines
  • Use Pictograms and Blow Their Mind!
  • Create A Pivot Table Because Your Boss Loves Them!
  • Filter A Pivot Table Visually Using Slicers
  • Add A Timeline Control to A Pivot Table
  • Use Slicers to Create a Custom Timeline
  • Format A Pivot Table Using Pivot Table Styles
  • Create A Custom Pivot Table Style
  • Understand Pivot Table Report Layouts
  • Add/Remove Subtotals and Apply Cell Styles to Pivot Table Fields
  • Display Multiple Summations Within a Single Pivot Table
  • Add A Calculated Field to A Pivot Table
  • Pivot charts

Skills Learned

After completing this online training course, students will be able to:

  • Sort a range
  • Use text to columns
  • Applying advanced filters
  • Set up a form
  • Use conditional formatting
  • Utilize Excel functions
  • Secure their workbook
  • Utilize macros
  • Create and modify charts, graphs, and pivot tables