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.