Advance Excel
Course Overview
This course aims to provide students with advanced skills in Microsoft Excel 2019, focusing on formulas and functions, data management and analysis, and error-free spreadsheet creation. Students will engage in practical exercises, hands-on activities, and real-world applications to enhance their proficiency in Excel.
Course Outcomes
On completion of this course, the students will be able to:
- Use advanced formulas and functions in Excel for various applications.
- Perform complex data management tasks.
- Analyze data using PivotTables and other Excel tools.
- Conduct financial and statistical analysis using Excel.
- Implement data validation and protection techniques.
- Utilize what-if analysis, Goal Seek, and Solver for decision making.
- Create error-free spreadsheets and ensure data accuracy.
Course Objectives
- Master advanced Excel formulas and functions.
- Perform complex data manipulation and analysis.
- Utilize Excel for financial and statistical analysis.
- Import, clean, and validate data effectively.
- Create and analyze PivotTables.
- Conduct what-if analysis and use Goal Seek and Solver.
- Protect and secure Excel workbooks.
Course Pedagogy
- Lectures: Theoretical foundations and key concepts will be taught through interactive lectures.
- Hands-on Activities: Practical activities to apply learning in real-world scenarios.
- Case Studies: Real-world examples to illustrate the application of advanced Excel concepts.
- Group Discussions: Collaborative discussions to enhance understanding and encourage diverse perspectives.
- Simulations: Use of Excel simulations to practice data analysis and problem-solving.
Session Plan
Advance Excel Syllabus T29
| Session No. | Topics | Activities |
|---|---|---|
| 1 | Introducing Formulas and Functions | Lecture and Exercise |
| 2 | Using Formulas for Common Mathematical Operations | Practical Activity |
| 3 | Using Formulas to Manipulate Text | Case Study |
| 4 | Using Formulas with Dates and Times | Hands-on Activity |
| 5 | Using Formulas for Conditional Analysis | Group Discussion |
| 6 | Using Formulas for Matching and Lookups | In-Class Activity |
| 7 | Using Formulas for Financial Analysis | Case Study |
| 8 | Using Formulas for Statistical Analysis | Practical Exercise |
| 9 | Using Formulas with Tables and Conditional Formatting | Hands-on Activity |
| 10 | Understanding and Using Array Formulas | Group Exercise |
| 11 | Making Your Formulas Error-Free | Lecture and Practical Activity |
| 12 | Importing and Cleaning Data | Practical Exercise |
| 13 | Using Data Validation | Hands-on Activity |
| 14 | Creating and Using Worksheet Outlines | Case Study |
| 15 | Linking and Consolidating Worksheets | Group Discussion |
| 16 | Introducing PivotTables | Lecture and Practical Exercise |
| 17 | Analyzing Data with PivotTables | Hands-on Activity |
| 18 | Performing Spreadsheet What-If Analysis | Group Exercise |
| 19 | Analyzing Data Using Goal Seeking and Solver | Case Study |
| 20 | Analyzing Data with the Analysis ToolPak | Practical Activity |
| 21 | Protecting Your Work | Lecture and Hands-on Activity |
Textbooks:
- Walkenbach, J. (2018). Excel 2019 Bible: The Comprehensive Tutorial Resource. Wiley.
- Alexander, M., & Kusleika, D. (2018). Excel 2019 for Dummies. Wiley.
References:
- Muir, D. (2018). Microsoft Excel 2019: Step by Step. Microsoft Press.
- Winston, W. L. (2019). Microsoft Excel Data Analysis and Business Modeling (6th Edition). Microsoft Press.
Useful Hyperlinks:
- Excel Functions and Formulas - https://support.microsoft.com/en-us/excel
Softeare for Excel Practice:
- Microsoft Excel 2019
- Solver Add-In for Excel
- Analysis ToolPak for Excel