Optimizing America's Workforce - One Spreadsheet at a Time!
Excel Training, Development and Automation using macros, formulas and functions. Also specialize in other Microsoft Office products.
Microsoft Office Specialist - Master
We have 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
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.
Lesson I : Understanding Excel Objects
Cells, Columns, Rows
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
Insert/delete columns, cells, ranges
Paste Special, Transpose
Lesson V : Most Important Excel Functions, Functionality
Vlookup vs Match/Index
Creating test data
Text to Columns
Lesson VI : Accessibility
Workbook, Worksheet, Cell, VBA Code protection
Hide worksheets, ‘Very Hide’ worksheets
Lesson VII : Importing/Exporting information from other sources
Txt, CSV, XML (various delimiters)
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
Lesson XI : Printing
Using print layout, setting pages, repeating rows, establishing headers/footers
Lesson XII : Automation
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.