Introduction

This two (2) day, 4 hours each day webinar is been designed to equip participants with powerful report generation capabilities, auto-consolidation techniques, search and find capabilities using LOOKUP, VLOOKUP, INDEX-MATCH, CHOOSE functions (to name a few), and apply built-in date/time/text/financial tools, as well as text manipulation, with data validation and built-in user forms for easier data capture. Most notable feature of the workshop is the introduction of a simplified formula creation, with the capability to “fetch named” ranges from anywhere in the workbook, providing for faster, more elegant, and English-like formula creation, which in turn enables users to debug formulas of whatever complexity, and at the same time, enabling the tracing of formula precedents and dependencies

Learning Objectives

At the completion of this training course, participants are expected to be:

• capable of creating basic data tables, and creating calculated columns, with simplified sorting capabilities, and callable data table elements.
• use of the enhanced
  • SUMIF’s, SUMPRODUCT, COUNTIF’s,
  • standard VLOOKUP function,
  • standard INDEX/MATCH function,
  • creation of PivotTables/PivotCharts,
  • Text Functions (RIGHT, LEFT, &, LEN, MID),
  • Concatenate,
  • Remove Duplicates,
  • Text to Column,
  • Customize graphs in an effective way,
  • DATE/TIME mathematics,
  • Date Grouping, Cell Range Naming, Page Layout and Themes, and standard Text,
  • Financial, Date/Time, Statistical functions

• MDI or Multiple Document Image (MDI), which enables users to view various and simultaneous perspectives of the same workbook. With these features, participants should also be able to create better charts, using the “right chart for the right purpose” guide provided by the facilitator.

Prerequisites for attending this webinar

• All delegates must be willing to learn the basic features of MS Excel and have been using the software for the past 3 months.
• Strong internet connection (this is an online, live webinar)
• Laptop with a licensed MS Excel software

Program Duration

2 days, 3 hours each day

Program Outline/Course Overview

Section 1

• Foundation for Intermediate Techniques
• About the Intermediate MS Excel Cheat Sheet
• Understanding the MS Excel Date and Time Feature
• Creating custom Date and Time Formats

Section 2

• Introducing Data Table
• Understanding Context in a Data table
• Improved Sorting Techniques
• Understanding Data Grouping
  • By week
  • By Month
  • By Quarter
  • By Year
• Applying when to “turn-off” selected grouping values, and when “not to turn-off” selected grouping values.

Section 3

• Impact of Data Tables on your formulas (from Greek to English formulas)
• Named Ranges – Quick Access to Excel Memorized Cells
• Using Structured formulas
• Discovering advanced techniques using LOOKUP, VLOOKUP, INDEX-MATCH, CHOOSE, SUMIFS, TEXT, COUNT, COUNTA, and COUNTIF functions
• Using Copy with Transpose capabilities

Section 4

• Automating and Simplifying Report Updates the Easy Way
• Simplified appends, edits and deletions
• Creating Excel Dashboards and Data Visualization

Section 5

• Using Named ranges in cells and images
• Creating Dynamic Pivot Tables
• Creating bar, column, line, pie charts, 2-axes column charts, and pareto charts

Section 6

• A guide to the using the right chart for the right purpose
• Using Named Ranges for workbook navigation

Section 7

• Data Validation Concept
• Data Validation using various techniques
• Test for empty cells
• Test for range values
• Test for valid lists

Section 8

• Formula Audit and Tracing
• Tracing precedent cells
• Tracing the dependents of a cell
• Displaying all formulas within a worksheet

Revalida

For a complete course outline, email trainings@gcssinc.com.