Intermediate Microsoft Excel

1 Day, 9:00-4:00

Course Overview

This Instructor-led course, through lecture and hands-on exploration, will teach students the skills needed to work with multiple worksheets within the same workbook. Students will also learn how to use statistical, logical, time, and text functions like COUNTIF, IFS, CONCAT, TRIM, LEFT, MID, RIGHT, UPPER, LOWER, PROPER, MONTH and NETWORKDAYS. The course also covers how to graph information to create pie and bar charts.

Course Objectives

Create formulas to summarize data across worksheets and multiple files
Save multiple files as a workspace to create a shortcut for easy access
Use a variety of statistical, date and text functions
Turn numeric data into a pie, bar, and line charts

Course Outline

Advanced Worksheet Features

Reviewing Excel Introduction Topics
Copying Worksheets
Writing 3-D References
Formatting Multiple Worksheets Simultaneously
Grouping Multiple Worksheets
Ungrouping Worksheets

Writing Formulas that Link Different Excel Workbooks

Opening Multiple Workbooks Simultaneously
Copying Worksheets to New Workbook
Creating Links Between Workbooks
Viewing Linked Information
Watching Formulas
Editing Source Files
Inserting Rows and Columns in the Source File

Working with Statistical and Logical Functions

Understanding Absolute vs. Relative Cell References
Using the IF Function to Perform an Action Based on a Condition
Writing IF Statements
Using the Function for More Than Two Outcomes
Using the SWITCH Function to Display Results
Using the COUNT Function to Count a List of Numbers
Using the COUNTA Function to Count Non-Empty Cells
Using the COUNTBLANK Function to Count Blank Cells
Using the COUNTIF Function to Count Based on a Criterion
Using the COUNTIFS Function to Count Based on Multiple Criteria

Using Text Functions and Features

Using the Text to Columns Feature
Inserting Columns
Splitting Text over Multiple Columns
Understanding Text Functions
Using the CONCAT Function to Join Cells
Using the TRIM Function to Remove Extra Spaces
Using the UPPER, LOWER, and PROPER Functions to Change Case
Using the LEFT, MID, and RIGHT Functions to Extract Characters

Working with Date and Time Functions

Working with Time and Date Functions
Working with Date and Time Functions
Using the Month Function
Using the Day Function
Using the Year Function
Using the TEXT Function
Using the NETWORKDAYS Function

Charting Data

Creating Pie Charts
Modifying and Resizing Charts
Adding Chart Titles and Labels
Formatting Chart Objects
Creating Column or Bar Charts
Changing the Numbering Scale
Changing the Data Series to Rows or Columns
Working with Trendlines
Updating the Source Data

Cost and Delivery Format Options

We offer the above listed class as a private group class in the following delivery formats:

Live Virtual
1-4 students	$700
5-8 students	$850
9-12 students*	$950
*$50 per additional student

At Your Location
1-4 students	$950
5-8 students	$1100
9-12 students*	$1200
*$50 per additional student

Private Group Training

Volume discounts of 5% to 20% are available when five or more training days are scheduled. For classes conducted at your location, you are responsible for the classroom setup which should include:

A computer for each student (with the appropriate software installed), and
An instructor workstation connected to a visual display (e.g., projector, smartboard, etc.).

Customization

Softek can easily customize a training course that covers the specific topics you require and integrate examples relevant to your work environment. Course customization is billed at $150 per hour and covers time needed to meet, discuss, prepare, and incorporate any specific examples into the training program. Once we have a better idea of what you have in mind, we will be glad to provide a specific price quote for the customization.