Creating and auditing formulas

• Constructing formulas in Excel.
• Understating 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 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 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

• Excels 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 approximate match and exact match.

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”.
• Inserting symbols in number formats.

working with many worksheets

• Creating multiple windows.
• Tilling the windows.
• Arranging the windows horizontal and vertical.
• Cascading the windows.

Working with large worksheet

• Splitting window to show various parts of a worksheet.
• Freezing top rows.
• Freezing left columns.
• Freezing rows and columns.

Paste Special Options.

• Using Paste Special to Add, Subtract, Multiply & Divide.
• Using Paste Special ‘Values’.
• Using Paste Special Transpose Option.

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