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.
Overview
COURSE DIFFICULTY
COURSE DURATION
9h 30m
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
Any current or future user of Excel 2019 who is looking to better understand and utilize the application.
None
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
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
WHO SHOULD ATTEND
Any current or future user of Excel 2019 who is looking to better understand and utilize the application.
PREREQUISITES
None
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
