Course Description
This 2-day hands on course will provide you with the knowledge of more specialized and advanced capabilities of Excel by automating some common tasks, applying advanced analysis techniques to more complex data sets, collaborating on worksheets with others,
and sharing Excel data with other applications.
Prerequisites
Participants must have basic Excel knowledge and must bring their own laptop to apply the lessons and work on exercises.
Learning Objectives
By the end of this course, the participants shall be able to:
Identify & define the essential components of Excel formulas
Perform advanced formulas
Identify how to format financial statements & other Excel-based reports effectively
Perform data security & validation
Create Excel-based charts and dashboards to aid in communicating financial data information
Manage big data in Excel
Use data analysis tools & pivot features
Get introduced to macro functions & features
Outline
- Essential Components of Excel Formulas
- Relative Value & Absolute Value
- Calculate Data Across Worksheets
- Performing Advanced Formulas
- Simplify Number Functions
- Create Solutions Using Text Methods
- Streamline Aging Report
- Using IF Condition & Logical Functions: SUMIF, COUNTIF, AVERAGEIF
- Lookup Functions
- How to Format Financial Statements & Other Excel-based Reports
- Advanced Conditional Formatting & Filtering
- Custom Data Formats, Styles & Templates
- Perform Data Security & Validation
- Using Input & Drop-down Tool for Validation
- Read-only Protected Sheet & Workbook
- File Protection Method
- Excel-based Charts & Dashboards for Communicating Financial Data Info
- Application of Subtotal per Data Category
- Managing Big Data in Excel
- Advanced Filter with Name Manager and Logical Symbols
- Data Analysis Tools Using Pivot Features
- Introduction to Visual Basic & Macro Settings
