Data Management, Manipulation and Analysis using Excel Course

Objective:

By the end of this course, participants will be able to:

• Analyse relationships across information and data using MS Excel.
• Generate data forecasts using MS Excel.
• Organise your company’s data in a more structured manner.
• Analyse your data effectively using various MS Excel techniques.
• Select the appropriate chart for your data.

Contents:

An Introduction to the MS Excel Environment

• Cell referencing, cell formatting and entering formula
• Workbooks versus Worksheets
• Copy and pasting
• Left click versus right click
• Paste Special
• Introductory charts

Using MS Excel Functions for Fundamental Data Analysis

• Use of text functions: FIND, LEN, LEFT, RIGHT, &
• Use of count functions: COUNTA, COUNTIF, COUNTIFS, SUMIF
• Basic statistical functions: Max and Average
• Filtering, sorting and use of conditional formatting
• Scatter diagrams

Intermediate MS Excel Functions

• Use of VLOOKUP and HLOOKUP
• Date functions: YEAR, MONTH, DAY, YEARFRAC
• Selecting appropriate charts
• Introduction to Pivot tables

Carrying out Statistical Analysis using MS Excel

• Using MS Excel to calculate mean, mode and median
• The difference between the various standard deviation and variance functions in MS Excel
• Using MS Excel to examine inter-dependency
• Drawing histograms in MS Excel
• Introduction to Data Analysis functions

What-if and Scenario Analysis Using MS Excel

• Naming cells in MS Excel
• Linking cells together to undertake scenario analysis
• Introduction to solver
• Advanced charting
• Sharing MS Excel output with other office formats