Excel Intermediate Training

Introduction

The Excel Intermediate training is for participants who have basic Excel skills and are looking to enhance their knowledge of composing formulas and applying them to manipulate data.

Participants will learn how to audit and correct their formulas. They will also learn to work with Excel functions such as date and time functions, text functions, statistics functions, and lookup functions. The training will also train them to work with many worksheets and large worksheets and finally print the worksheets.

Learning Outcome

At the completion of the training, candidates will be able to:

• Create formulas

• Audit formulas 

• Correct errors

• Apply Excel Functions

• Work with many worksheets

• Work with large worksheets

• Create charts

Module 1: Creating and Auditing Formulas

• Constructing formulas in Excel

• Understating Excel’s data types

• The four-step process of auditing formulas in Excel

• Understanding Excel errors and how to correct them

• Working with relative reference and absolute reference formulas

Module 2: Creating Formula Using Named Range

• Naming a cell as an alternative to an 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 pasting links

Module 3: Functions

• Getting Help with Functions

• Understanding function syntax and differentiating between required argument and optional argument

• Reading the Microsoft documentation on help with function

Module 4: 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.

Module 5: Time and Date Functions

• The concept of date and DATESERIAL in Excel

• The concept of time in Excel

• The Date and Time data type

• Solving problems relating to date and time

• Calculating future and pasting dates

• Important Date and Time Functions

Module 6: 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 (only for Excel version 2019 and above)

Module 7: Statistical Functions

• The various COUNT functions to detect data irregularity

• The conditional statistics functions are: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF and COUNTIFS

• Functions to calculate central tendencies.

Module 8: Lookup Functions

• Excel lookup functions

• LOOKUP as an alternative to the IF logic

• LOOKUP array form and vector form

• Left lookup using LOOKUP

• VLOOKUP and HLOOKUP

• Understanding how to choose an approximate match and an exact match.

Module 9: Formatting Data In Excel 

• Using the preset formatting to format data

• Creating your custom Number Formats

• Creating formats for large numbers using prefixes such as “k”, "M,” and “G”

• Insert symbols in number formats

Module 10: Working With Many Worksheets

• Creating multiple windows

• Tilling the windows

• Arranging the windows horizontally and vertically

• Cascading the windows.

Module 11: Working With Large Worksheets

• Splitting window to show various parts of a worksheet

• Freezing top rows

• Freezing left columns

• Freezing rows and columns.

Module 12: Paste Special Options

• Using Paste Special to Add, Subtract, Multiply and Divide

• Using Paste Special ‘Values’

• Using the Paste Special Transpose Option.

Module 13: Charts

• Create sparklines

• Create charts and work with the various chart options

• Standard charts will be covered: Column chart, Bar chart, multi-column chart, pie chart and line chart