Excel Training

Excel Classes

We offer classes for beginning, intermediate and advanced 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.

Course Objectives

Understand how Excel is organized; how it relates to other Office products; how it relates to other databases

A

Use groups of data rather than individual cells when the opportunity presents itself

A

Organize data within a workbook, worksheet, table and range

A

Develop macros used to define functions not currently part of Excel

A

Discover answers to tasks to be accomplished

A

Connect with outside data sources

A

Understand the pros and cons of tables

A

Use the most common Excel functions, learning how to search for all other

A

Use the most common Excel functions, learning how to search for all other functions, creating functions from scratch

Lesson Overviews

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, grid-lines
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

Course Evaluation

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.