OVERVIEW

Students will build upon the foundational Microsoft Office Excel 2010/2013/2016/ 2019 / Office 365 knowledge and skills already acquired and learn to create advanced workbooks and worksheets, including advanced formulas, tables and data analysing tools.

LEARNING OUTCOMES

Upon completion of this program, participants will be able to:

• Demonstrate enhanced self-assurance and authority in leadership roles.
• Apply advanced frameworks for strategic and agile decision-making in high-pressure situations.
• Improve their ability to influence, motivate, and manage diverse teams effectively.
• Cultivate an agile mindset to adapt to change and drive results efficiently.
• Function as powerful internal and external brand ambassadors, reinforcing organizational trust and reputation.

COURSE OUTLINE

Lesson 1: Customizing the Excel Environment

• Configure Excel Options
• Customize the Ribbon and the Quick Access Toolbar
• Enable Excel Add-Ins

Lesson 2: Organizing and Analysing Datasets and Tables

• Managing Raw Data
• Data Cleaning and Housekeeping
• Manual Tables VERSUS Smart Tables
• Create and Modify Tables
• Sorting Data
• Filtering Data
• Use SUBTOTAL and Database Functions
• Enabling / Disabling Smart Tables

Lesson 3: Creating Basic to Advanced Formulas

• Working with Cell References
• Working with Absolute Referencing and Relative Referencing
• Working with Array Formulas
• Working with Name Range

a) Descriptive Names for Cell References

• Creating and Using a Named Range
• Creating Slightly Smarter Named Ranges
• Naming Formulas and Constants
• Managing Named Ranges
• Automatically Creating Named Ranges
• Applying Names to Existing Formulas

b) Controlling Recalculation

c) Solving Formula Errors

• Step-by-Step Evaluation
• Tracing Precedents and Dependents
• Error Checking

d) Working with Formulas and Functions

o Creating a Basic Formula

• Excel’s Order of Operations (BODMAS)
• Cell References
• How Excel Formats Cells That Contain Cell References

o Functions

• Using a Function in a Formula
• Using Cell References with a Function
• Using Cell Ranges with a Function
• Excel Compatibility Functions

o   Formula Errors

o   Logical Operators

o   Formula Shortcuts

• Point-and-Click Formula Creation
• Point-and-Click Formula Editing
• The Formulas Tab.
• Using the Insert Function Button

o Copying Formulas

• Absolute Cell References
• Partially Fixed References
• Referring to Other Worksheets
• Referring to Other Workbooks

Lesson 4: Working with Statistical and Database Functions

a) Working with Simple to Advanced Queries With: 

• Calculating Sum, Sumif, Sumifs functions
• Working with Average, Averageif, Averageifs functions
• Counting with Count, Countif, Countifs Functions
• Analyzing Max, min, Large, Small, Rank Values

b) Working with Complex Queries With:

• DSUM
• DAverage
• DCount
• DMax
• DMin

Lesson 5: Manipulating Dates, Times, and Text

a) Working with Text Functions

• CONCATENATE(): Joining Strings of Text Together
• LEFT(), MID(), and RIGHT(): Copying Portions of a Text String ▪ UPPER(), LOWER(), and PROPER(): Changing Capitalization

b) Working with Date and Time Functions

• TODAY() and NOW(): Inserting the Current Date and Time
• DAY(), MONTH(), and YEAR(): More Date Calculations
• WEEKDAY(): Determining the Day of the Week
• DATEDIF(): Calculating the Difference Between Dates
• DAYS360(): Finding Out the Number of Days Between Two Dates
• YEARFRAC(): Calculating the Percentage of a Year Between Two Dates
• WEEKNUM(): Figuring Out in Which Week a Date Falls

Lesson 6: Managing Workbooks

• Arranging Multiple Workbooks
• Adding Hyperlinks
• Working with Comments
• Saving Workbooks in Different Formats
• Saving a Workbook as a Web Page
• Creating Folders
• E-Mailing a Workbook

Lesson 7: Grouping and Outlining Data

• Basic Data Grouping
• Creating a Group
• Nesting Groups Within Groups
• Summarizing Your Data
• Combining Data from Multiple Tables
• Grouping Timesavers
• Auto Outline
• Automatic Subtotalling

Lesson 8:   Templates

• Understanding Templates
• Creating a New Workbook from a Template
• Downloading Templates
• Creating Templates
• Understanding Custom Templates
• Building a Custom Template
• Sharing Templates with Others

Lesson 9: Reporting Data with pivot Table Tables

• Preparing Summary
• Formatting and Perfecting Pivot Tables
• Working with Other Sources
• Creating Pivot-Charts