  Microsoft Excel Training

Introduction

The training covers the intermediate level of MS 2013, 2016, 365, 2019.

Learning Outcome

At the completion of the training, candidates will be able to:

• Use Functions and Formulas
• Use Time & Date Functions
• Use Mathematical Functions
• Use Logical Functions
• Use Statistical Functions
• Use Text Functions
• Use Lookup Functions
• Work With Named Ranges
• Use Paste Special Options within Microsoft Excel

Foundation level

Two days

Training Course Outline

MODULE 1: Microsoft Excel Functions and Formulas

• Getting help with functions
• Nested functions
• Mixed references within formulas

MODULE 2: Auditing Techniques in Microsoft Excel

• Tracing precedent cells
• Tracing dependent cells
• Identifying cells with missing dependents
• Showing all formulas in a worksheet, rather than the resulting values

MODULE 3: Microsoft Excel Named Ranges

• Naming cell ranges
• Removing a named range
• Named cell ranges and functions

MODULE 4: Microsoft Excel time & date functions

• Inserting the current time and date
• Today function
• Now function
• Day function
• Month function
• Year function

MODULE 5: Microsoft Excel Logical Functions

• If function
• And function
• Or function

MODULE 6: Microsoft Excel Statistical Functions

• Count function
• Counta function
• Countif function
• Countblank function
• Rank function

MODULE 7: Microsoft Excel Text Functions

• Left function
• Right function
• Mid function
• Trim function
• Concatenate function

MODULE 8: Microsoft Excel Lookup Functions

• Vlookup function
• Hlookup function

MODULE 9: Paste Special Options Within Microsoft Excel

• Using paste special to add ranges
• Using paste special to subtract ranges
• Using paste special to multiply ranges
• Using paste special to divide ranges
• Using paste special ‘values’
• Using paste special transpose option

Introduction

The training covers the advanced level of MS 2013, 2016, 365, 2019.

Learning Outcome

At the completion of the training, candidates will be able to

• Use Microsoft Excel Pivot Tables
• Create Array Formulas
• Perform Auditing Techniques in Microsoft Excel
• Import Text Files into Microsoft Excel
• Perform Sorting, Filtering & Totaling Data Within Microsoft Excel
• Perform Validating Within Microsoft Excel
• Create Microsoft Excel Passwords & Understand Security Issues

Pre-Requisite

Intermediate Level

Two days

Training Course Outline

MODULE 10: Microsoft Excel Functions and Formulas (Advanced)

• Getting help with functions

• Nested functions

• Mixed references within formulas

• Excel errors

• Concept of Time and Date

• Logical functions

• Useful functions

MODULE 11: Auditing Techniques in Microsoft Excel

• Tracing precedent cells

• Tracing dependent cells

• Identifying cells with missing dependents

• Showing all formulas in a worksheet, rather than the resulting values

MODULE 12: Array Formula

• What are arrays in Excel?
• Multi-cell arrays
• Single-cell array
• Editing arrays

MODULE 13: Microsoft Excel Tables and Pivot Tables

• Creating Table from a range
• Manipulating Table features
• Filter buttons in a table
• Creating and using a pivot table
• Filtering and sorting data within a pivot table
• Automatically grouping data in a pivot table and renaming groups
• Manually grouping data in a pivot table and renaming groups

MODULE 14: Importing Text Files into Microsoft Excel

• What is a delimited text file?
• Importing a delimited text file

MODULE 15: Sorting, Filtering & Totaling Data Within Microsoft Excel

• Sorting data by multiple columns at the same time
• Applying a preinstalled custom sort
• Creating a customized list and performing a custom sort
• Deleting an Excel customized list
• Using AutoFilter
• Using AutoFilter to perform multiple queries
• Top 10 AutoFilter
• Removing all AutoFilters from a worksheet
• Subtotalling
• Removing subtotals
• Expanding and collapsing outline detail levels

MODULE 16: Validating Within Microsoft Excel

• Data validation – a whole number
• Data validation – decimal number
• Data validation – list
• Data validation – date
• Data validation – time
• Data validation – text length
• Customizing a validation input message and error alert
• Removing data validation

MODULE 17: Microsoft Excel Passwords & Security Issues

• Removing an ‘open’ password from a workbook
• Removing a ‘modify’ password from a workbook
• Password protecting cells and worksheets
• Hiding formulas
• Un-hiding formulas

Introduction

This training is intended for anyone with an intermediate knowledge of Excel who wants to analyze and visualize data in order to get results. It focuses on understanding the underlying structure of big data so that the most appropriate tools can be used to analyze it. The training is targeted at an average person who wants to leverage his or her Excel skills to analyze large datasets.

MS Excel Version

2013 / 2016 / 365

Learning Outcome

This training will enable candidates to:-

• Understand the use of Excel as Database and Data Aggregator
• Create and manipulate Pivot Tables and Pivot Charts
• Build Data Model
• Use SQL in Excel
• Design Reports with Power View
• Create calculation with Data Analysis Expressions (DAX)
• Import Data using Power Query
• Create maps with Power Map
• Perform Statistical Calculations

2 days

Prerequisite

Participants must be well versed with Excel Formulas and functions.

Training Course Outline

MODULE 18: MODULE 1: Excel As Database and Data Aggregator

• Interpreting File Extensions
• Using Excel As a Database
• Importing from Other Formats
• Using the Data Tab to Import Data
• Data Wrangling and Data Scrubbing
• Input Validation
• Working with Data Forms
• Selecting Records

MODULE 19: MODULE 2: Pivot Tables and Pivot Charts

• Recommended Pivot Tables in Excel 2013
• Creating a Pivot Chart
• Adjusting Subtotals and Grand Totals
• Analyzing Sales by Day of Week
• Creating a Pivot Chart of Sales by Day of Week
• Using Slicers
• Importing Pivot Table Data from the Azure Marketplace

MODULE 20: MODULE 3: Building a Data Model

• Enabling PowerPivot
• Relational Databases
• Database Terminology
• Creating a Data Model from Excel Tables
• Creating a Pivot Table from Two Tables
• Creating a Pivot Table from Multiple Tables
• Adding Calculated Fields to the Data Model

MODULE 21: MODULE 4: Using SQL in Excel

• SQL Syntax
• SQL Aggregate Functions
• Subtotals
• Joining Tables
• Importing an External Database
• Specifying a JOIN Condition and Selected Fields
• Using SQL to Extract Summary Statistics
• Generating a Report of Total Order Value by Employee

MODULE 22: MODULE 5: Designing Reports with Power View

• Elements of the Power View Design Screen
• Considerations When Using Power View
• Types of Fields
• Understanding How Data Is Summarized
• A Single Table Example
• Viewing the Data in Different Ways
• Creating a Bar Chart for a Single Year
• Column Chart
• Displaying Multiple Years
• Using Tiles
• Relational Example
• Customer and City Example
• Showing Orders by Employee
• Aggregating Orders by Product

MODULE 23: MODULE 6: Calculating with Data Analysis Expressions (DAX)

• Understanding Data Analysis Expressions
• Updating Formula Results
• Using the SUMX Function
• Using the CALCULATE Function
• Calculating the Store Sales for 2009
• Creating a KPI for Profitability
• Creating a Pivot Table Showing Profitability by Product Line
• Summary

MODULE 24: MODULE 7: Power Query

• Installing Power Query
• Working with the Query Editor
• Key Options on the Query Editor Home Ribbon
• A Simple Population
• Performance of S&P 500 Stock Index
• Importing CSV Files from a Folder
• Importing JSON

MODULE 25: MODULE 8: Power Map

• Installing Power Map
• Plotting a Map
• Key Power Map Ribbon Options
• Troubleshooting

MODULE 26: MODULE 9: Statistical Calculations

• Recommended Analytical Tools in 2013
• Customizing the Status Bar
• Inferential Statistics
• Review of Descriptive Statistics
• Charting Data
• Excel Analysis ToolPak
• Using a Pivot Table to Create a Histogram
• Scatter Chart