Microsoft Excel Intermediate
The course covers a range of topics, starting with a refresher on the structure of formulas and cell references. Participants will then explore date and number formatting, advanced formula functions such as VLOOKUP, SUMIFS, and Pivot Table, and managing large tables. Advanced PivotTable features, data validation, and conditional formatting are also included, along with logical functions and worksheet protection techniques. The course concludes with creating and formatting various charts and graphs
- The Excel Function and Formula – is a comprehensive 2-day program designed to enhance the practical Excel skills of managers, analysts, and staff within the industry.
- This course focuses on the effective use of Excel formulas and functions to streamline operations, manage large datasets, and perform detailed data analysis, all tailored to the specific needs of your professionals.
This course is ideal for clerical, costing, admin, executives and managers, analysts, and staff who have a basic understanding of Excel. Participants should be comfortable with data entry and navigation but seek to advance their skills in using Excel for more complex tasks such as data management, financial forecasting, costing and pricing.
The training methodology is hands-on and interactive, involving practical exercises using sample datasets that mimic real-world scenarios. Each module includes step-by-step instructions and practical applications to ensure participants can immediately apply what they learn to their daily tasks. The course also provides a training manual and quick reference guides to support ongoing learning and application. By the end of the course, participants will have a solid understanding of how to leverage Excel’s powerful features to improve efficiency, accuracy, and decision-making in their daily operations.
Day 1
- Module 1: Refresher and Understanding Structure of Formulas
- Relative and Absolute Cell References
- Module 2: Date Format
- Entering date functions
- TODAY function
- NOW function
- EOMONTH function
- NETWORKDAYS Function
- Date formats
- Using dates in formulas
- Module 3: Number Format
- Number Format
- Round Function
- Roundup Function
- Rounddown Function
- Module 4: Using Formula and Functions
- VLOOKUP, HLOOKUP and XLOOKUP Functions
- SUMIF , SUMIFS
- COUNTIF, COUNTIFS
- INDEX and MATCH Function
- Module 5: Managing Large Tables and Data ( Database Management)
- Working with lists
- Structure of a list
- Sorting and filtering lists
- Simple sorting
- Sorting by multiple fields
- Using AutoFilter
- Advanced filtering
- Using custom filter
- Using Advanced Filter
- Adding subtotals to a list
- Module 6: Advanced PivotTable Features
- Creating a Basic PivotTable
- Creating Basic PivotChart
- Using the PivotTable Fields Pane
- Adding Calculated Fields
- Sorting Pivoted Data
- Filtering Pivoted Data
- Slicer
- Module 7: Data Validation
- Restricting type of data allowed to be keyed into a cell.
Day 2
- Module 8: Conditional Formatting
- Apply Conditional Formatting to cells
- Module 9: Logical Fuction (IF, AND , OR )
- Setting more complex formula using IF , AND, OR
- Nested IF
- Module 10: Lock Unlock , Protect Unprotect , Hidden Formula in worksheet
- Lock Unlock Cells
- Protect Unprotect Worksheet
- Hidden Formula
- Find and select cells that meet specific conditions (F5, Goto Special)
- Section 11: Linking Workbook and Updating Workbook
- Linking Workbook
- Editing Workbook Link
- Updating External Data
- Section 12: What-If Analysis
- Using What-If Analysis
- Goal Seek
- Scenario Manager
- Solver
- Section 13: Introduction to Macro
- Recording a Macro
- Introduction to VBA Editor
- Section 14: Chart & Graph
- Creating Sparklines
- Inserting Charts
- Formatting Charts
- Combo Charts
You May Also Be Interested In
Explore other training programmes that may complement your learning needs.
