Introduction (15 minutes)
• Brief recap of basic Excel functions and concepts
• Importance of Excel in external audit
• Objectives of the course

Data Import and Cleaning (30 minutes)
• 2.1. Importing Data: CSV, TXT, and from databases
• 2.2. Handling errors: #N/A, #VALUE!, and other common issues
• 2.3. Using Text-to-Columns and Flash Fill
• 2.4. Finding and removing duplicates

Advanced Data Manipulation (45 minutes)
• 3.1. Advanced use of VLOOKUP, HLOOKUP, and introduction to INDEX-
MATCH
• 3.2. Using IF combined with other functions: IFERROR, IFS, etc.
• 3.3. Date and time functions: EDATE, EOMONTH, DATEDIF, etc.
• 3.4. Array formulas and how they can be leveraged in auditing

PivotTables and PivotCharts (45 minutes)
• 4.1. Building and customizing PivotTables
• 4.2. Grouping data and creating calculated fields/items
• 4.3. Using slicers and timelines for interactive reporting
• 4.4. Introduction to PivotCharts for visual analysis

Data Analysis Techniques (45 minutes)
• 5.1. Conditional formatting for audit analysis
• 5.2. Data bars, color scales, and icon sets
• 5.3. Using Data Tables and Scenario Manager for sensitivity analysis
• 5.4. Introduction to Solver for optimization problems

Security and Tracking Changes (30 minutes)
• 6.1. Protecting worksheets and workbooks
• 6.2. Setting password and encryption options
• 6.3. Using the Track Changes feature for collaborative auditing
• 6.4. Documenting and reviewing comments in Excel

Automation with Simple Macros (30 minutes)
• 7.1. Introduction to the concept of macros
• 7.2. Recording and running simple macros for repetitive tasks
• 7.3. Assigning macros to buttons for ease of use
• 7.4. Security considerations with macros

Q&A and Hands-On Exercise (45 minutes)
• Participants can ask specific questions related to their work or the topics
covered
• A real-world audit scenario will be provided, and participants will be
tasked to handle and analyze the data using the skills learned

Conclusion and Feedback (5 minutes)
• Recap of what was learned
• Feedback collection for continuous improvement of the course
• Providing additional resources for self-study

Those who have mastered the basics of Excel and want to take it a step further.

Auditors
Independent reviewers
Accountants