Microsoft Excel 365 Intermediate Training - 4-Hour Tutorial

Embark on a comprehensive 4-hour Excel tutorial designed to elevate your spreadsheet skills to an intermediate level. Master essential techniques for better spreadsheet design, improved readability, and efficient data input control. Learn to create summary sheets, implement logical functions, and utilize advanced lookup methods like VLOOKUP, INDEX and MATCH, and XLOOKUP. Explore data manipulation techniques including sorting, filtering, and handling duplicates. Dive into statistical analysis with functions such as MEAN, MEDIAN, and MODE. Enhance your Excel proficiency with custom formatting, advanced conditional formatting, and practical exercises throughout the course. Perfect for those looking to refine their Excel skills and boost productivity in spreadsheet management.

Syllabus

- Course Introduction
- Exercise 01
- Design Better Spreadsheet
- Improve Readability with Cell Styles
- Control Data Input: Data Validation
- Add Navigation to a Workbook
- Create a Summary Sheet
- Use Forms to Input Data Quickly
- Exercise 02
- Logical Functions: Basic Recap
- Logical Functions: AND and OR
- Nested IF Statements
- The IFS Function
- Conditional IF: COUNTIFS and SUMIFS
- Error Handling with IFERROR and IFNA
- Use Logical Functions with Conditional Formatting
- Exercise 03
- VLOOKUP: Exact vs Approx Match
- HLOOKUP
- Flexible Lookups: INDEX and MATCH
- Modern Lookups: XLOOKUP and XMATCH
- Lookups with Duplicates
- Lookups with the CHOOSE Function
- The SWITCH Function
- Exercise 04
- Sort on Multiple Columns
- Sort with a Custom List
- The SORT and SORTBY Functions
- The FILTER Function
- Extract and Sort Unique Values
- Exercise 05
- Find Duplicates with Conditional Formatting
- Find Duplicates with Formulas
- Exercise 06
- Use Mean, Median and Mode
- The LARGE and SMALL Functions
- RANK and RANK Average
- The SUBTOTAL and AGGREGATE Functions
- Exercise 07
- ROUND Values
- Special Rounding
- Exercise 08
- Custom Formatting Explained
- Practical Uses of Custom Formatting
- Advanced Conditional Formatting
- Exercise 09
- Conclusion