Verhoef Training USA

Training Courses and On-Line Classes Preferred By IT Pros

Visit us at:   http://www.verhoef-training.com

103 Hillside Ave., PO Box 1515
West Caldwell, NJ 07006
Main Office:  800.631.0410
Sales Office:  800.533.3893

ADVANCED DATA ANALYSIS

Course:   ADA
Duration:   2 Days
Level:   II
On our website at:   http://www.verhoef-training.com/courses/ADA.html
 
Course Summary

Most organizations rely heavily on advanced features of Microsoft Excel 2016 to consolidate, analyze, and report financial information. If you have chosen to take this workshop, you use Excel a lot in your business role. By now, you're familiar with Excel 2016, its functions and formulas, a lot of its features and functionality, and its powerful data analysis tools. You are likely called upon to analyze and report on data frequently, work in collaboration with others to deliver actionable organizational intelligence, and keep and maintain workbooks for all manner of purposes.

We?ll start off with a quick review of what was covered in Introduction to Data Analysis, the prerequisite for this class. As the workshop progresses you?ll see how to work with the additional data analysis tools provided by Excel 2016 add-ins and become skilled in using validation to protect the integrity of your worksheets from less experienced users.

You?ll learn how to add functional and eye-catching custom controls to any worksheet and how to use scenarios and data tables to quickly perform what-if data analyses. You'll discover advanced techniques for PivotTables, like creating Timelines, calculated fields, and calculated items. This training class is designed for students to gain the skills necessary to audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros and use the Visual Basic for Applications (VBA) language.

Attendees will look at working with scenarios, using Solver, the Analysis ToolPak, PivotTable features and developing macros. Other features such as conditional formatting, slicers, the Inquire Add-In, and tracking changes are also covered as well as a lesson on Goal Seek, Solver, PowerPivot and PowerView.

You'll learn how to use Excel's consolidation function to efficiently summarize data from multiple sources, become adept at importing external data, and master the art of conditional formatting to highlight duplicate entries and other common worksheet problems. As you become proficient, you'll be able to accomplish just about anything Microsoft Excel 2016 has to offer!

Most of the time when you run statistics, you want to use statistical software. These tools are built to do calculations like t-tests, chi-square tests, correlations, and so on. Excel is not really meant for data analysis. But that doesn?t mean you can?t do it.

Unfortunately, Excel?s statistical functions aren?t always intuitive. And they usually give you esoteric results. So instead of using stats functions, we?re going to use the Excel statistics add-in: the Data Analysis ToolPak. 2

Whether it is daily life thing or any day at work, we are always comparing to see what?s good and what is not so good. This differential analysis has a popular name, ?ANOVA.? Analysis of Variance (ANOVA) is a statistical method used to test differences between two or more means. We will learn how to use various multiple comparison tests within Excel.

Another statistical technique we will use is Regression Analysis, an important tool for modelling and analyzing data. Multiple regression is an extension of simple linear regression. It is used when we want to predict the value of a variable based on the value of two or more other variables.

We?ll look at the difference between parametric statistics and non-parametric methods and get some experience using various non-parametric tests in Excel. Nonparametric methods are useful when the normality assumption does not hold and your sample size is small.

Topics Covered In This Course

Introduction to Data Analysis: Review

  • Review: Introduction to Data Analysis
  • The Role of Statistics and the Data Analysis Process
  • Customizing the Excel Environment
  • Creating Advanced Functions and Formulas
  • Analyzing Data with Functions and Conditional Formatting
  • Lookups and Data Tables
  • Organizing and Analyzing Datasets and Tables
  • Hypothesis Testing
  • Visualizing Data with Basic Charts
  • Advanced Charting
  • Preparing Data and Creating PivotTables
  • Examining Data Using PivotTables
  • Analyzing Data with PivotTables, Slicers, and PivotCharts
  • Working with PivotCharts

Advanced Functions and Formulas

  • Logical functions 4
  • Conditional functions
  • Financial functions
  • Text functions
  • Date functions
  • Array formulas

Lookups and Data Tables

  • Using lookup functions
  • Using MATCH and INDEX
  • Creating data tables

Advanced Data Management

  • Validating cell entries
  • Advanced filtering
  • Validating and Updating
  • Data Validation
  • Learn how to use Data Validation to ensure that users enter valid data in input cells.
  • Data Validation Lists
  • Use to restrict users ability to enter invalid data in cells by providing them with a drop down list of valid options.

Advanced Charting

  • Chart formatting options
  • Combination charts
  • Graphical objects

Advanced PivotTables and PivotCharts

  • Working with PivotTables
  • Rearranging PivotTables
  • Formatting PivotTables
  • Using the PivotTable Fields Pane
  • Formatting PivotTables
  • Learn to control formatting and other options in PivotTables.
  • Adding Calculated Fields in PivotTables
  • Learn to create and modify basic calculated fields for PivotTables.
  • Sorting Pivoted Data
  • Filtering Pivoted Data
  • PivotCharts

Exporting and Importing Data

  • Exporting and importing text files
  • Getting external data 5

Analytical Tools

  • Goal Seek
  • Scenarios

Working with Scenarios

  • Scenario Concepts
  • Creating Scenarios
  • Loading Scenarios
  • Merging Scenarios
  • Editing Scenarios
  • Creating a Scenario Summary Report
  • Deleting Scenarios
  • Managing Scenarios

Using Goal Seek, Solver and VLookup

  • Setting up the Worksheet
  • Using Goal Seek
  • Running Solver
  • Solver Concepts
  • Defining the Problem
  • Solving the Problem
  • Solver Reports
  • Setting Solver Options
  • Generating Reports and Scenarios with Solver
  • What-If Analysis
  • Learn to perform what-if analysis in Excel using the Scenario Manager and Goal-Seek.
  • Modifying Constraints
  • Use VLookup

Macros and Visual Basic

  • Using Macros
  • Recording a macro
  • Running a macro
  • Editing a Macro
  • Working with VBA code
  • Writing a Macro using the Visual Basic Editor

Analyzing Data

  • Enabling the Analysis ToolPak
  • Using Data Analysis Tools
  • Using Data Tables

Managing Data

  • Consolidating Information 6
  • Removing Duplicates
  • Configuring Data Validation
  • Transposing Data
  • Converting Text to Columns

Working with Slicers

  • Inserting and Using a Slicer
  • Renaming the Slicer
  • Changing Slicer Settings
  • Formatting a Slicer
  • Clearing the Slicer

Using the Inquire Add-In

  • Installing Inquire
  • Generating a Workbook Analysis Report
  • Viewing Workbook and Worksheet Relationships
  • Viewing Cell Relationships
  • Comparing Two Workbooks

Tracking Changes

  • Tracking Changes
  • Reviewing Changes
  • Setting Options for Tracking Changes
  • Stopping Tracking Changes

Publishing an Excel Workbook

  • When to Use Excel Services

Auditing Workbooks

  • Auditing Concepts
  • Tracing a Worksheet
  • Evaluating Formulas
  • Locating and Correcting Errors in Formulas
  • Tracking Cells in a Workbook

External Data

  • External Data Concepts
  • Importing Query Data from Access
  • Importing Query Data from Web Pages
  • Importing Query Data from Text Files

Excel Data Analysis Tools

  • Most of the time when you run statistics, you want to use statistical software. These tools are built to do calculations like t-tests, chi-square 7 tests, correlations, and so on. Excel isn?t meant for data analysis. But that doesn?t mean you can?t do it.
  • Unfortunately, Excel?s statistical functions aren?t always intuitive. And they usually give you esoteric results. So instead of using stats functions, we?re going to use the go-to Excel statistics add-in: the Data Analysis ToolPak.
  • You can now select any one of the following options useful for statistical analysis:
  • ANOVA: Single Factor
  • ANOVA: Two-Factor with Repetition
  • ANOVA: Two-Factor without Repetition
  • Correlation
  • Covariance
  • Descriptive Statistics
  • Exponential Smoothing
  • F-Test: Two Sample for Variances
  • Histogram
  • Random Number Generation
  • Rank and Percentile
  • Regression
  • Sampling
  • t-Test: Paired Two Sample for Means
  • t-Test: Two-Sample Assuming Equal Variance
  • t-Test: Two-Sample Assuming Unequal Variance
  • z-Test: Two-Sample for Means
    • Each of these options represents a data analysis tool

Regression Analysis: How to Use the Regression Data Analysis Tool in Excel

  • You can move beyond the visual regression analysis that the scatter plot technique provides. You can use Excel?s Regression tool provided by the Data Analysis add-in.
  • Excel is a great option for running multiple regressions when a user doesn't have access to advanced statistical software. The process is fast and easy to learn.

Non-parametric Tests

  • Most of the tests that we study are based on some distribution. These are called parametric tests. Parametric tests require that certain assumptions are satisfied. We now look at some tests that are not linked to a particular distribution. These non-parametric tests are usually easier to apply since fewer assumptions need to be satisfied.
  • Introduction to Non-parametric Tests 8

ANOVA Test: Definition, Types, Examples

  • Analysis of variance (ANOVA) is a collection of statistical models and their associated estimation procedures (such as the "variation" among and between groups) used to analyze the differences among group means in a sample.
  • An ANOVA test is a way to find out if survey or experiment results are significant. In other words, they help you to figure out if you need to reject the null hypothesis or accept the alternate hypothesis. Basically, you?re testing groups to see if there?s a difference between them.

Multiple comparison procedures

  • ?One-way? ANOVA & multiple comparisons: Learn these concepts through an Excel exercise
Who Should Take This Course

Students who have taken this vendor?s Introduction to Data Analysis workshop, or those who have intermediate skills with Microsoft Excel 2016 and who want to learn more advanced statistical and data analysis skills.

Recommended Prerequisites

Students should have taken an Introduction to Data Analysis workshop and have an intermediate to advanced Excel 2016 skills level, knowledge of pivot tables and other advanced techniques.

Training Style

Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

Related Courses
Code Course Title Duration Level
DAI
INTRODUCTION TO DATA ANALYSIS
2 Days
I
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.