Two day class.   Class is held at the Knowledge Development Center. 
KDC is located 8 miles north of downtown Atlanta, in the beautiful Perimeter area, and minutes from the Buckhead district.

16 hours of A&A credit

What you will learn:

·         Formulas (over 12 are covered on day 1)

·         3 dimensional worksheets and cross worksheet formulas

·         Absolute & relative references

·         Auto Filtering

·         Charting techniques

·         Conditional Formatting (new features in Excel 2007)

·         Custom Formatting

·         Data validation

·         Menard’s Quick Tips

·         Auditing toolbar

·         Pulling data from other sources

·         Formatting charts and reports

·         Using Tables

·         Linking from other excel files

·         Macros

·         Printing methods, techniques and tips

·         Subtotaling

·         Worksheet protection

·         Removing duplicates

·         Goal Seek

·         Outlining

·         Advanced Filtering

 

Using What-If Analysis

1.       Defining a What-If Analysis

2.       Tracking a What-If Analysis with Scenario Manager

3.       Generating a Scenario Summary

4.       Projecting Figures Using a Data Table

5.       Creating a Two-Input Data Table

6.       Setting Up a Complex What-If Analysis with Solver

7.       Running Solver and Generating an Answer Report

Summarizing Data with Pivot Tables

8.       Planning and Designing a PivotTable

9.       Creating a PivotTable

10.   Changing the Summary Function of a PivotTable

11.   Analyzing Three-Dimensional Data

12.   Updating a PivotTable

13.   Changing the Structure and Format of a PivotTable

14.   Creating a PivotChart Report

Exchanging Data with Other Programs

15.   Importing a Database Table

16.   Inserting a Graphic File in a Worksheet

17.   Embedding a Worksheet

18.   Linking a Worksheet to Another Program

19.   Converting a List to an Access Table

Gaining Control over Your Work

20.   Finding Files

21.   Outlining a Worksheet

22.   Controlling Worksheet Calculations

23.   Creating Custom AutoFill Lists

24.   Adding a Comment to a Cell

25.   Saving a Workbook as a Template

Programming with Excel

26.   Viewing Existing VBA Code

27.   Analyzing Existing VBA Code

28.   Writing VBA Code

29.   Adding a Conditional Statement

30.   Prompting the User for Data

31.   Debugging a Macro Procedure

32.   Creating a Main Procedure

33.   Running a Main Procedure

 

Covered Topics (lists are not all-inclusive)

1. Getting started in Excel
   1. Opening and saving workbooks
   2. Entering values and text
   3. Closing a workbook
2. Building and Editing a Worksheet
   1. Editing cell entries and working ranges
   2. Entering formulas
   3. Copying and moving cells
   4. Understanding relative and absolute cell reference
   5. Moving and naming a worksheet
3. Formatting a worksheet
   1. Formatting values
   2. Using fonts and sizes
   3. Adjusting Column width
   4. Inserting and deleting rows and columns
   5. Applying colors and borders
   6. Spell checking
4. Working with charts
   1. Creating a chart
   2. Editing a chart
   3. Formatting a chart
   4. Enhancing a chart
   5. Previewing and printing a chart
5. Working with Formulas and Functions
   1. Creating a formula with several operators
   2. Using names in formulas
   3. Generating multiple totals with AutoSum
   4. Using dates in calculations
   5. Building a conditional formula with the IF function
   6. Calculating payments with the PMT function

This two hour course teaches how to create customized Excel Charts including a Waterfall Chart.  A Waterfall chart includes a spreadsheet that automatically makes the columns change to alleviate manual chart creation and use of drawing objects.  In this course, you will learn IF statements, AND Statements, Nested IF statements, the Max function, the Min function and Chart formatting. 

Covered Topics (lists are not all-inclusive)

1. Working with charts
   1. Creating a chart
   2. Editing a chart
   3. Formatting a chart
   4. Enhancing a chart
   5. Previewing and printing a chart
2. Working with Formulas and Functions
   1. Creating a formula with several operators
   2. Using names in formulas
   3. Generating multiple totals with AutoSum
   4. Using dates in calculations
   5. Building a conditional formula with the IF function
   6. Calculating payments with the PMT function
3. Working in Excel
   1. Opening and saving workbooks
   2. Entering values and text
   3. Closing a workbook
4. Building and Editing a Worksheet
   1. Editing cell entries and working ranges
   2. Entering formulas
   3. Copying and moving cells
   4. Understanding relative and absolute cell reference
   5. Moving and naming a worksheet
5. Formatting a worksheet
   1. Formatting values
   2. Using fonts and sizes
   3. Adjusting Column width
   4. Inserting and deleting rows and columns
   5. Applying colors and borders
   6. Spell checking

Excel has over 300 functions.  This course covers over 60 functions needed for business people.  Function help analyze data, speed up editing task, and perform otherwise impossible calculations.  Class counts for 8 hours of A&A credit.

Instructor: Chris Menard

List below is not all inclusive:

INTRODUCING WORKSHEET FUNCTIONS

What is a Function? 

What do Functions do? 

Simplify formulas 

Perform impossible calculations

Speed up editing task 

Function Argument Types

Entering functions manually

DATE / TIME FUNCTIONS Overview

Formatting Dates and Times

Formatting dates 

Date Functions 

Display the current date 

DATE FUNCTION  

DATEVALUE FUNCTION

YEAR FUNCTION  

Month Function 

Day Function 

Example: year, Month & day

WEEKDAY FUNCTION  

Example of Weekday Function

Converting a non-date string to a date

Calculating the number of days between two dates

NETWORKING Function

TIME FUNCTIONS 

Displaying any time 

Summing times that exceed 24 hours

TIME SHEETS 

Converting Decimal hours, minutes, or seconds to a time

COUNTING TECHNIQUES

Counting the total number of cells

Counting Blank Cells

Counting Nonblank Cells

Counting Numeric Cells

Counting Text Cells

Counting cells using the Countif Function

IF FUNCTION  

Using IF to copy formulas

Lookups 

Lookup Function

VLookup Function

HLookup Function

Financial Functions

PMT Function 

PPMT Function 

IPMT Function 

RATE Function 

NPER Function 

TEXT Functions 

Proper Function 

Upper Function 

Lower Function 

Left Function 

Mid Function 

Right Function 

Concatenate Function

Rounding Functions

Round Function 

RoundUP Function

RoundDown Function

Database Functions

Daverage 

DMAX  

DMIN  

DCount

Depreciation Functions

SLN – Straight Line

SYD  

DB  

 

Covered Topics (lists are not all-inclusive)

1. Automating worksheet tasks
   1. Planning a macro
   2. Recording a macro
   3. Running a macro
   4. Using shortcut keys with Macros
   5. Creating a macro toolbar
2. Using Lists
   1. Creating a list
   2. Adding records with a data form
   3. Finding records
   4. Deleting records
   5. Sorting a list by one field
   6. Sorting a list by multiple fields
   7. Printing a list
3. Analyzing List Data
   1. Creating a custom filter
   2. Extracting List data
   3. Creating subtotals
   4. Looking up values
4. Enhanced chart and worksheet functions
   1. Creating a custom chart
   2. Formatting a chart axis
   3. Adding data table to a chart
   4. Rotating a chart
   5. Rotating text
   6. Mapping data
5. Managing workbooks
   1. Inserting and deleting worksheets
   2. Freezing rows and columns
   3. Hiding and protecting worksheet areas
   4. Controlling page break
   5. Creating hyperlinks between excel files

This course teaches VBA programming using Excel and covers the following topics: using the Visual Basic editor; programming basics; controlling program flow; working with custom dialog boxes; and debugging and error-handling.