Training Course Outline
CREATING AND AUDITING FORMULAS
- Constructing formulas in Excel.
- Understanding Excel’s data types.
- The four step process of auditing formula in Excel.
- Understanding Excel errors and how to correct them.
- Working with relative reference and absolute reference formulas.
CREATING FORMULA USING NAMED RANGE
- Naming a cell as an alternative to absolute reference.
- Naming a range of cells.
- Using the names in the formula.
- Editing and deleting the names.
- Using named formulas as an alternative to paste link.
FUNCTIONS
- Getting Help with Functions.
- Understanding function syntax and differentiating from required argument and optional argument.
- Reading the Microsoft documentation on help about function.
TEXT FUNCTIONS
- Functions to split data.
- Functions to join data.
- Functions to clean data with white spaces and unprintable characters.
- Functions to convert data types.
TIME & DATE FUNCTIONS
- The concept of time in Excel.
- The Date and Time data type.
- Solving problems relating to date and time.
- Calculating future and paste dates.
- Important Date and Time Functions.
LOGICAL FUNCTIONS
- How and when to use logical functions.
- The IF functions.
- Using nested IF function to solve multiple criteria problems.
- Applying the AND and OR function.
- Applying the IFS function.
STATISTICAL FUNCTIONS
- The various COUNT functions to detect data irregularity.
- The conditional statistics functions: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF and COUNTIFS.
LOOKUP FUNCTIONS
- VLOOKUP and HLOOKUP.
- Understanding how to choose approximate match and exact match.
- XLOOKUP.
FORMATTING DATA IN EXCEL
- Using the preset formatting to format data.
- Creating your custom Number Formats.
- Formatting formats for large numbers using prefixes such as “k”, “M” and “G”.
- Inserting symbols in number formats.
PIVOT TABLE
- Creating Pivot table.
- Formatting Pivot table.
- Grouping in Pivot table.
- Creating reports from Pivot table.
- Applying standard calculations.
- Applying built in formulas.
- Using slicers to filter Pivot table.
CHARTS
- Create sparklines.
- Standard charts will be covered: Column chart, Bar chart, multi column chart, pie chart and line chart.
- Creating Pivot charts.











