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