Duration
2 days
Introduction
Excel is commonly used in data management & analysis, but few people can truly use it effectively. You need to know Excel’s vast array of data management & analysis tools to manage & interpret business information in a coherent & organized manner. For self-taught professionals, it is a discipline that is acquired through years of experience, often an arduous trial & error process.
This program is designed to be immediately practical and useful for the business analyst who needs to manage huge data records, analyse business information, perform forecasts and ultimately make fact-based decisions to drive businesses forward. This is an all-inclusive program to familiarise the participant in Excel’s data management features. It trains the participant to manipulate & analyse business data as a valuable resource.
Audience
This course is intended intermediate Excel users (Accounting, Finance, and Operations).
Prerequisites
Before attending this course, students should have experience:
- A basic working knowledge of Microsoft Office Excel
Module 1: Statistical and Logical Functions
This module explains how to use logical functions including CountIf, Sumif, If, IsError.
Lessons
- Perform calculation using CountIF
- Perform calculation using SumIF
- Perform calculation using AverageA
- Using IF function to prevent division by zero
- Using IsError function to avoid error display
- Creating multiple conditions using nested IF
- Using logical function OR, And
After completing this module, students will be able to:
- Perform calculation using CountIf, SumIf, AverageA
- Using If function to prevent division by zero
- Using IsError function to avoid error display
- Create multiple conditions using nested IF
- Using logical function OR, AND
Module 2: Lookup and Reference Formulas
This module explains how to apply and use lookup formulas including vlookup, hlookup, match and index.
Lessons
- Using Vlookup to find specific data
- Using Hlookup to find values in rows
- Using Match and Index to retrieve data
After completing this module, students will be able to:
- Using Vlookup to find specific data
- Using Hlookup to find values in rows
- Using Match and Index to retrieve data
Module 3: Text Formulas
This module explains how to apply Text formula to help change casing of text, append text and numerical value in excel spreadsheet.
Lessons
- Changing case of text
- Append text and numerical value
- Convert imported text format into numbers
- Break imported date field into individual columns
After completing this module, students will be able to:
- Changing case of text using Upper, Lower or Proper formula
- Append text and numerical value
- Convert imported text format into numbers
- Break imported date field into individual columns
Module 4: Date and Time Formulas
This module explains how to make use of calculate the difference of two given Date fields and to perform calculation with Time fields.
Lessons
- Perform addition to Date fields
- Calculate difference between two Dates
- Perform calculations with Time fields
After completing this module, students will be able to:
- Perform addition and calculate difference between two dates
- Perform calculations with Time fields
Module 5: Array and Database Functions
This module explains how to apply and use advance formula including Array, Frequency and Database functions.
Lessons
- Using Array Formulas
- Calculate the difference between Maximum and Minimum values
- Using Frequency function to Count responses
- Using Database functions DSum and DCount
After completing this module, students will be able to:
- Using Array Formulas
- Calculating the difference between Maximum and Minimum values in an Array
- Using Frequency function to Count responses in tabulated data
- Using Database functions DSum and DCount
Module 6: Efficiency Tips
This module discusses some useful Excel Tips including application of Data Validations and Auditing Tools.
Lessons
- Shortening worksheets names
- Protecting cells containing formulas
- Using Data Validation
- Displaying Formula syntax
- Using Auditing Tools for errors checking
- Tracing precedent and dependent
- Adding comments to worksheet
After completing this module, students will be able to:
- Understand the advantages of shortening worksheet names
- Protecting cells from amendments by others
- Using Data validation to improve data entries
- Using Auditing Tools for checking errors
- Adding useful notes by commenting worksheet
Module 7: Making Data Work For You
This module explains how to make use of Excel data and maximize it through the use of PivotTable.
Lessons
Maximize the Power of PivotTable
Ask the Questions
Get the Answer
After completing this module, students will be able to:
Maximize the Power of PivotTable
Module 8: A Quick Tour
This module explains how to use determine the source needed to create its PivotTable report.
Lessons
Determine What You Need to Know
Identify the Source
Creating the Pivot Table
Working with Pivot Table Layout
After completing this module, students will be able to:
Determine the source needed for its PivotTable
Create the PivotTable
Working with the PivotTable
Module 9: Manipulating the PivotTable
This module explains how to rearrange the layout of the PivotTable.
Lessons
Rearranging the Layout
Adding New Field
Renaming a Field
Formatting Numbers
Changing Sorting Order
Showing Top 10
Refreshing Data
After completing this module, students will be able to:
Rearrange the layout of the PivotTable
Adding, renaming New Field within the PivotTable
Formatting Number in the PivotTable reports
Changing Sorting Order
Showing Top 10 values
Refreshing Source data within the PivotTable
Module 10: Beyond the Basics
This module explains how to create a second data field and changing the summary function with the PivotTable.
Lessons
Creating Second Data Field
Changing the Summary Function
Displaying Numerical Value as a Percentage of the Total
Creating a Custom Calculation
More Function in Custom Calculation
After completing this module, students will be able to:
Create a Second Data Field within the PivotTable
Changing Summary Function
Displaying Numerical Value as a Percentage of the Total
Create custom calculation within the PivotTable
Module 11: More PivotTable Calculations
This module explains how to insert formula within the PivotTable.
Lessons
Using Formulas
Creating a Calculated Field
Creating a Calculated Item
Using GetPivotData Function
Turn off GetPivotData Function
After completing this module, students will be able to:
Use Formulas with the PivotTable
Create a calculated field and item
Using GetPivotData Function
Turn off GetPivotData Function
Module 12: Creating an Excel Database
This module explains how to create a simple database or list in Microsoft Office Excel.
Lessons
An Excel Database
Excel List Command - New in Excel 2003
Create a New Database
Freeze Panes
After completing this module, students will be able to:
Create new databases quickly in Excel.
Module 13: Using Data Validation
This module explains how to use the Data Validation feature to reduce error while keying in data in Excel databases.
Lessons
Reduce Data Entry Errors by Using Data Validation
Validate Text Length
Create a Custom Error Message
Validate Data by Using a List
Set Maximum and Minimum Values
Remove Validation Rules
After completing this module, students will be able to:
Create data validation rules to reduce data entry errors.
Customize data validation rules to help in data entry efforts.
Module 14: Sorting and Filtering Data
This module explains how to use the sorting and filtering features available in Excel.
Lessons
Use Simple Sorting
Sort Records By Multiple Fields
Use Custom Sort Orders
Create Custom Sort Orders
Use AutoFilter to Filter Data
Remove a Filter Criteria
Display Top Ten Records
Create a Custom AutoFilter
Create a Custom Filter by Using Wildcard
After completing this module, students will be able to:
Use simple one-key sort and multiple-key sort.
Create customized sort orders.
Use the AutoFilter feature to do simple filtering.
Use more complex filtering by using the AutoFilter feature
Module 15: Working with Advanced filter & Database Functions
This module explains how to use advanced filter to perform complex filtering and derive analysis results by using database functions.
Lessons
Create a Criteria Range
Use a Criteria Range
Use an Advanced AND Condition
Use an Advanced OR Condition
Copy Filtered Records
Use Database Functions
After completing this module, students will be able to:
Build a criteria table.
Create AND and OR condition criteria.
Use a criteria table as part of a database function.
Programme scope, delivery arrangements and suitability are confirmed during enquiry.