Course Objective:
This course is aimed to help Excel users to use formulas & functions Creatively, starting from Fundamental of References to the complicated Nested Functions to simplify the Daily Workflow.
Target Student:
This course is designed for students who desire to manage and prepare reports quickly. Users who want to create template, automate calculations, create dynamic reports and performed complicate calculations are encourage to join this course.
Prerequisites:
Microsoft Excel Foundation and Advanced level is essential. You should be comfortable in the Windows environment and be able to use Windows to manage information on the computer. Specifically, you should be able to launch and close programs; navigate to information stored on the computer; and manage files and folders.
Course Objectives
Upon successful completion of this course, students will be able to:
- Understand Static References versus Dynamic References thoroughly
- Prepare report using Formula
- Prepare dynamic reports
- Use nested functions to simplify the daily workflow
- Generate Dynamic Summary from Database
Duration
2 days (9AM to 5PM daily)
Program Outline
Mastering Data and References
- Explore Excel Data Types
- Using Functions & Mathematical Formulas
- Excel Formula Reference Techniques
- Advanced Referencing using Name Range
- Create, Edit & Apply Name Range
- Working with Name Range in a Formula
- Using Name as Static Value
- Using Name as Absolute Reference
- Using Name as Dynamic Reference
- Explore the differences between Cell Range and Table
- Advance Referencing from a Table
- Understand Table Structured References
- Use Structure Reference in Formula
- Create Dynamic Reference using Structured Reference
Building Basic Formulas
- Excel Formula Limitations
- Using Arithmetic Operators in Excel
- The Order of Operation
- Copying Formulas
- Formatting Numbers, Date and Time
Statistical COUNT Functions
- Count all the cell with Numbers
- Count all the cells with Data
- Count all the Empty Cells
- Count with Single Condition
- Count with Multiple Condition
Data Cleansing with text Functions
- Join Text into a single cell
- Extract some Text from a Cell
- Split cell contents into 2 different cells
- Change text to Uppercase, Lowercase and Title Case
- Remove trailing Spaces within a text
- Convert data using Date and Text Functions
Date and Time Calculations
- Static and Dynamic Date
- Find the date line of a given duration
- Find the duration of a given date line
- Calculate the number of years and months of given start and end date
- Move the given date to a specific date by month
- Calculate the duration between start and end time
Apply Logic in Formulas
- Compare data using Logical Test
- Combine multiple Logical Test using AND/OR Functions
- Using If Functions to automate decision
- Suppress error and replace error with meaningful information
Excel Superstar: VLOOKUP
- Understand the science behind the VLOOKUP
- Be aware of the constraints using VLOOKUP Function
- Approximate Match vs Exact Match
- Overcome the limitation of VLOOKUP Function
- Alternative to VLOOKUP: INDEX & MATCH
- Using VLOOKUP to find the Unmatched Data
Summarize Database using Functions
- Calculate the Total base on specific criteria(s)
- Calculate the Average base on specific criteria(s)
- Calculate the Highest Recorded Value base on specific criteria(s)
- Calculate the Lowest Recorded Value base on specific criteria(s)
- Calculate the Number of Record base on specific criteria(s)
Optional
Assessment
- Automate a Template using Formulas and Functions
- Focus Area:
Logical Functions & Lookup Functions - Others:
Name Range, Drop Down List
