Master Microsoft Excel 2021 from beginner to advanced level in this comprehensive 12-hour course. Learn essential skills including launching Excel, exploring the interface, using ribbons and menus, and utilizing the Quick Access Toolbar. Progress through intermediate topics such as cell styles, data input, and navigation buttons. Dive into advanced concepts like dynamic arrays, two-way lookups, and specialized functions. Practice with included exercise files, covering a wide range of topics from basic formulas to complex data analysis techniques. Gain proficiency in logical functions, lookup functions, formatting, statistical analysis, and error handling. Follow along with timestamps to navigate different sections and tailor your learning experience to your skill level.
Overview
Syllabus
- Introduction
- Course Intro
- Excel 2021 vs Excel 365
- Launching Excel
- The Start Screen
- Exploring the Interface
- Ribbons, Tabs and Menus
- Backstage Area
- Quick Access Toolbar
- Useful Keyboard Shortcuts
- Getting Help
- Exercise 01
- Working with Templates
- Workbooks and Worksheets
- Saving Workbooks
- Entering and Editing Data
- Navigating and Selecting Cells, Rows and Columns
- Exercise 02
- Formulas and Functions Explained
- SUM Function
- COUNT Function
- AVERAGE Function
- MIN and MAX Function
- Handling Errors in Calculations
- Absolute vs Relative Referencing
- Autosum and Autofill Options
- Flash Fill
- Exercise 03
- What are Named Ranges
- Managing Named Ranges
- Using Named Ranges
- Exercise 04
- Applying Number Formats
- Applying Date and Time Formats
- Formatting Cells, Rows and Columns
- Format Painter
- Exercise 05
- Intermediate Course Introduction
- Improve Readability with Cell Styles
- Controlling Data Input
- Adding Navigation Buttons
- Logical Functions
- The IF Function
- Nested IFs
- The IFs Function
- Conditional IFs
- Multiple Criteria
- Error Handling
- Exercise 01
- VLookup Exact Match
- VLookup Approximate Match
- HLookup
- INDEX and MATCH
- XLookup and XMatch
- OFFSET Function
- INDIRECT Function
- Exercise 02
- Sorting on Multiple Columns
- Soring Using a Custom List
- SORT and SORTBY Functions
- Advanced Filter
- UNIQUE Function
- FILTER Function
- Exercise 03
- How Date and Times are Stored
- Custom Data and Time Formats
- Time and Date Functions
- WORKDAY and WORKDAY.INTL Functions
- NETWORKDAYS and NETWORKDAYS.INTL Functions
- DATEDIF
- EDATE and EOMONTH
- Advanced Course Introduction
- Exercise 01
- Dynamic Arrays
- Spill and Arrays
- Extract and Count Unique Entries
- Unique vs. Distinct
- Unique with Multiple Criteria
- Unique Values by Column
- SORT Function
- Horizontal Sort
- SORTBY Function
- Horizontal SORTBY
- Sequence and Unstacking Records
- Filter Function
- Using Filter with Logic Plus Operator
- Using Filter with Logic Asterisk Operator
- Using Filter with Logic Equals Operator
- Using Filter with Logic Minus Operator
- RANDARRAY and RANDBETWEEN
- XLookUp
- XMatch
- Exercise 02
- Two-way Lookups
- CHOOSE Function
- SWITCH Function
- Exercise 03
- Median and Mode
- Using LARGE and SMALL
- Ranking Data
- COUNTBLANK Function
- Exercise 04
- Rounding Values
- Specialized Rounding
- Aggregate Function
- Exercise 05
Taught by
Simon Sez IT
