Microsoft Excel Level 2: Mastering Data Analysis and Presentation
(EXCPRO, 2 days)


Dates and Pricing


2 People for Delivery $1,150/person

Outline

Microsoft Excel Essential Skills Revised
Identifying and Referencing Cells
Working with Cell Contents
Formatting Cells
Working with Formulas
Business Graphics
Exercise: Setting up the Business Data
Working with Data
Characteristics of a Database Layout
Using Cell Names to Prepare the Data
Using Filters to Sort and Filter the Data
Creating Custom Filters to Find the Data you need
Using Search and Find Functions to Locate the Data you need
Using Subtotals to Summarize the Data
Removing Duplicates
Using Custom Functions to Clean the Data
Exercise: Summarizing the Data with Excel Functions
Advanced Functions: Conditional Logic
Using Conditional Formatting
Planning for Conditional Logic
Using a Single If Function to Implement Conditional Logic
Using Multiple If Functions to Implement Complex Conditional Logic
Using the AND and OR Functions to Implement Complex Criteria
Using Negation and DeMogan’s Theorem to Simplify Logic
Exercise: Implementing Complex Logic in Support of Data Cleanup
Advanced Functions: Statistics and Finance
Review of Fundamentals: Working with Excel Functions
Dealing with Special Situations: Blank Cells
Working with Statistical Functions: Count, Median and Average
Working with Statistical Functions: Variance and Standard Error
Working with Statistical Functions: Frequency and Ranking
Working with Statistical Functions: Linear Regression and Correlation
Working with Statistical Functions: Probability Distributions
Performing Variance and Standard Error Calculations
Working with Financial Functions: Payments, Interest and more
Working with Financial Functions: NPV, IRR and More
Performing Financial Analysis: Probability and Earned Value
Exercise: Creating Statistical Summary Sheets for Corporate Data
Exercise: Analyzing NPV and IRR for Project Allocation
Pivot Tables
Setting up the Data
Creating a Pivot Table
Changing Row, Column and Data Associations
Formatting the Pivot Table Headers
Formatting the Pivot Table Aggregate Values
Setting Pivot Table Options
Adding and Working with Splitters
Exercise: Using Pivot Tables to Analyze and Present Data