Data-driven decision-making starts with efficient reporting. This course equips participants with skills to clean, manipulate, and analyse data using advanced Excel functions and tools. They will build structured reports, apply conditional logic, and summarise key metrics with PivotTables to enhance reporting accuracy and insight.
Learning Outcomes:
Understand how to use logical and statistical functions for reporting
Apply conditional formatting to highlight key trends
Clean and structure raw data for reporting use
Implement advanced filters for data extraction
Create and interpret PivotTables for financial reports
Key Topics:
IF, VLOOKUP, IFERROR, and date functions
Conditional formatting with custom rules
Data cleaning techniques and text functions
Advanced filtering using criteria ranges
General ledger reporting with PivotTables
Module 1. Using Advanced Functions
- Using the IF Function
- Using Nested IF Functions
- Using an AND Condition with IF
- Using an OR Condition with IF
- Using the VLOOKUP Function
- Using the IFERROR Function
- Using the ROUND Function
- Using Date Functions
- Day Of Week (DOW)
- Using DATEDIFF Function
Module 2. Using Conditional Formatting
- Applying conditional formatting
- Setting custom criteria in conditional formatting
Module 3. Data cleaning
- Convert text to table
- Remove duplicate
- Fill up blanks cell quickly
- Use find and replace to clean data
- Using TEXT Function (LEFT, RIGHT, CONCATENATE, TRIM, UPPER, LOWER, PROPER)
Module 4. Advanced Filter
- Creating a Criteria Range
- Using a Criteria Range
- Showing All Records
- Using an Advanced AND Condition
- Using an Advanced OR Condition
- Copying Filtered Records
Module 5. Creating Pivot Tables for a General Ledger
- Concepts of PivotTable
- Check your source data for Pivot (remove merge cell, repeat row labels, remove extra columns, remove extra rows)
- Create your PivotTable
- Filter your PivotTable
- PivotTable Classic View
- Present your PivotTable
.png?width=50&name=Artificial%20Intelligence%20(AI).png)
-1.png?width=360&name=Server%20(4)-1.png)
/AI%20(6)-1-1.png?width=360&name=AI%20(6)-1-1.png)
/AI%20(8)-1.png?width=360&name=AI%20(8)-1.png)
-1.png?width=360&name=Automation%20RPA%20(2)-1.png)
/AI%20(5)-1.png?width=360&name=AI%20(5)-1.png)
