Course Code: exdsabespoke
Duration: 15 hours
Prerequisites:
  • Participants should be familiar with the basics of Excel and Statistics.
Overview:

Course Objectives

This course will help improve your familiarity with Excel data and statistical analysis, and as a result increase the effectiveness and efficiency of your work or research. The course begins with a practical orientation towards basic functions to organise and manipulate data in Excel, and how to use the Analysis ToolPack in Microsoft Excel. Delegates will then be introduced to statistical functions and how to perform basic statistical procedures in Excel. This is a practical course that will expose delegates to lots of practical exercises.

Course Outline:

Basic Excel Functions

• Excel Data Types

• Look up values in a spreadsheet

• Naming the range, create a range of labels

• Modifying named ranges

• Create custom number formats

• Use AutoFormat options in a spreadsheet

• Create a named style

• Creating a user template

• Protection sheets

Organizing data in a worksheet

• Create a list of data

• Adding to the list of series

• Creating a custom sort lists

• Consolidate data

Processing of data in a spreadsheet

• Apply conditional formatting

• Summarizing data with subtotals

• Filter the list of data

• Create a custom filter

• Create an advanced filter

• Create scenarios

• Data Validation

The processing of information from external data sources

• Combining data from other Windows programs

• Importing a delimited text file

• Dividing a column of data into multiple columns

• Create a Web Query

• Importing database table

• Using queries to selectively retrieve information from external databases

• Calculate the average of the values stored in the database

Produce data charts

• Changing the chart type

• Creating a custom chart types

• Adding a trendline to a chart

• Adding new values and change data for the chart

• Create a combination chart

Aggregating Data in Excel

• Statistical Functions

• Outlines

• Subtotals

• Excel Pivot Tables in Detail (Pivot Table Reports and Pivot Charts)

Data Relation Analysis

• Normal Distribution

• Descriptive Statistics

• Linear Correlation

• Regression Analysis

• Covariance

Analysing Data in Time

• Trends/Regression line

• Linear, Logarithmic, Polynomial, Power, Exponential, Moving Average Smoothing

• Seasonal fluctuations analysis