Course Code: exceldata
Duration: 14 hours
Prerequisites:
  • A basic understanding of computers

Audience

  • Data Analysts
Overview:

Excel is a spreadsheet application for formatting and analyzing data. Using Excel, users can perform descriptive analysis.

This instructor-led, live training (online or onsite) is aimed at data analysts who wish to use Excel to efficiently perform a variety of calculations in a transparent way to organize and present data, use multiple mechanisms to facilitate and accelerate the creation of spreadsheets.

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

  • Format and analyze data in spreadsheets.
  • Dynamically handle data to accelerate the creation of spreadsheets.
  • Use VBA, create pivot charts, and apply what if analysis with Goal Seek.
  • Create data visualizations with Google Sheets and PowerPoint.

Format of the Course

  • Interactive lecture and discussion.
  • Lots of exercises and practice.
  • Hands-on implementation in a live-lab environment.

Course Customization Options

  • To request a customized training for this course, please contact us to arrange.
Course Outline:

Introduction

Theoretical Science of Data Analysis

  • The principles of data analysis
  • Objectives of data analysis
  • Approaches for data analysis

Preparing the Data Analysis Environment

  • Installing and configuring Microsoft Office
  • Setting up Google Sheets

Formatting data

  • Determining how to display different types of data and calculation results (eg, dates, amounts, percentages or fractions)
  • Determining the edge and the background of the cells
  • Formatting row and column titles
  • Using Styles

Processing 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

Dynamic Data Handling

  • Aggregation of cell contents using the AutoSum
  • Adding in the dialog window
  • Insert Function
  • Editing formulas
  • Evaluate Formula
  • Using Solver tool to obtain specific values of the arguments
  • Create a conditional formula
  • Using the data table to solve the formulas
  • Tracking error performance formulas
  • Look up values in a particular row or column
  • Determine the value location
  • Returning values from a cell in a specific location in the data list
  • Positioning of the data list
  • Create a table of credit
  • Using Execel Web Query

PivotCharts

  • Performing the pivot of information from external database
  • Change the layout of a PivotChart
  • Changing a PivotChart
  • Filtering fields PivotChart
  • Grouping elements PivotChart field
  • Add a table of data to the PivotChart

Visual Basic

  • VBA and Sheet Data Exchange (Cells, Range Methods)
  • Getting and Printing Data from Users (InputBox, MsgBox)
  • Variable Declaration
  • Scope and LifeTime of Variables and Objects
  • Operators and Their Priorities
  • Modules Options
  • Creating Worksheet Functions
  • Objects, Classes, Methods and Properties
  • Locking Project for Viewing

What if Analysis

  • Using Goal Seek
  • Automating with macros
  • Running macros

Data Visualization

  • Visualizing data with Google Sheets
  • Visualizing data with PowerPoint

Data Analysis with SQL

  • What is SQL?
  • Syntax structure
  • Queries and databases

Summary and Conclusion