Training Classes

---

We offer classes for beginning, intermediate and advances Excel users.

The class described below is the most common and ends up with an introduction to macros and writing code

COURSE DESCRIPTION

The objective of this course is to optimize an employee’s average eight-hour day. An employee with less monotonous tasking is not only a more productive employee but a more challenged employee than one who is consistently performing the same motions each day. The computer and its applications are a resource to task, another entity to delegate. The employee and senior leadership alike can then focus on more meaningful contributions to a company. Analyzing data and predicting trends provides better information for decision makers to propel a company.

The course provides a level of knowledge that allows an individual or company employee to store information and report on it without having to lookup definitions or to discover the best way to accomplish a task. The goal is for the student to review a task, ask the right questions and provide a basic solution. From here, the student will be able to discover simple solutions to complex challenges.

If the students have a project from their work or personal business, they are encouraged to bring it to class and the instruction can be built around their project. If not, the students will build out a rudimentary inventory system from scratch that will utilize most of the common functionality of Excel. Learning the common functionality will provide the student with the ability to create any other type of system centered around information important to any business.

Although there are times during the class in which theory or background is explained, most of the class involves practical application by the student.  Projects will be accomplished while the instructor is teaching. Students will have Excel open from “minute one” building an inventory system (or personal project) from a blank Workbook to a final product. All student work is done near simultaneously with the instructor. The course does not progress to the next lesson until students comprehend the previous lesson.

The course is given over a 24-hour period with the most common arrangements being either three consecutive eight-hour days or four consecutive six-hour days. We can also accommodate other customer schedules in breaking up the 24-hour period. Breaks within the day are issued between topics or as agreed upon by the class.

• Understand how Excel is organized; how it relates to other Office products; how it relates to other databases
• Use groups of data rather than individual cells when the opportunity presents itself
• Organize data within a workbook, worksheet, table and range
• Understand the pros and cons of tables
• Connect with outside data sources
• Use the most common Excel functions, learning how to search for all other functions, creating functions from scratch
• Discover answers to tasks to be accomplished
• Develop macros used to define functions not currently part of Excel

Lesson I : Understanding Excel Objects
  Cells, Columns, Rows
  Ranges
  Data Tables, Pivot Tables, Charts
  Functions, Formulas. Menu Items
Lesson II : Understanding Data and how to organize it
  Create Data Tables
  Link Data tables – use Vlookups/Match-Index
  Create Setup Tables (dropdowns) – use precise lists making for accurate reports
  Clean/Scrub Data – remove or replace data inconsistent with the main body
Lesson III : Ensuring User inputs valid information
  Limiting input to numbers, date/time ranges
Lesson IV : Shortcuts and Maneuvering
  Fill series
  Ctrl-Z, Ctrl-Y
  Insert/delete columns, cells, ranges
  Paste Special, Transpose
  Sort/Filter
Lesson V : Most Important Excel Functions, Functionality
  Vlookup vs Match/Index
  Creating test data
  Highlighting duplicates
  CountIfs, SumIfs
  MaxIf/MinIfs
  Counta, Counta
  If,Ifs,Switch, Choose
  Mid, Find
  Iferror

  Substitute
  Text to Columns
  Networkdays, Round
Lesson VI : Accessibility
  Workbook, Worksheet, Cell, VBA Code protection
  Encryption
  Hide worksheets, ‘Very Hide’ worksheets
Lesson VII : Importing/Exporting information from other sources
  Txt, CSV, XML (various delimiters)
  Access
  SQL server
  Export to SharePoint list
Lesson VIII : Connecting to information outside current workbook
  Connecting to other worksheets

  Connecting to other workbooks
  Connecting to Access
  Connecting to SQL server
Lesson IX : Database Management
  Retrieving information from other file types (txt, xml, sq. database, access)
Lesson X : Appearance
  Creating similar appearances in color and structure between worksheets
  Removing rows, columns, gridlines
  Using borders
  Conditional Formatting

Lesson XI : Printing
  Using print layout, setting pages, repeating rows, establishing headers/footers
Lesson XII : Automation
  Automatic emails
  Automatic pivot tables/charts refresh
  Select from listbox
  Create Buttons to run code

During the course, the student will be periodically tested before moving on to the next lesson to ensure the student understands what has been taught.  A quiz of what was taught in the lesson will be given and graded on the spot.