This Excel course aims to teach users everything from basic to advanced Excel skills. By the end of the course, learners will be able to use logical IF functions, Excel lookup functions, formatting, statistical functions, and more. The course covers launching Excel, exploring the interface, using ribbons, tabs, and menus, and utilizing the Quick Access Toolbar. It also includes topics such as cell styles, data input, navigation buttons, dynamic arrays, two-way lookups, median and mode, rounding values, and more. The teaching method includes downloadable exercise files and practical examples. This course is intended for beginners looking to enhance their Excel skills, as well as intermediate users seeking to deepen their knowledge.
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
