Ardito Computer Training

Course Description

This in-depth two-day course introduces Microsoft Excel users to the advanced features of the software application. Participants will learn how to sort and manage data in lists; filter and query data; use outlines on worksheets, lookup and database functions, and PivotTables. In-class exercises will involve use of data tables and scenarios to make projections about data and use Goal Seek, Solver, and the Analysis ToolPak to analyze data. Students will also learn how to audit worksheets, protect worksheets and workbooks, create and use macros and templates, and import and export information and documents. Additional topics include use of workgroup features; working with custom views and Report Manager; and creating and modifying data maps. After completing Ardito's Excel Introduction, Intermediate, and Advanced courses, students would have covered all the topics that map to the Microsoft Office User Specialist Expert certification exam.

Prerequisites
Participants should have completed the Excel 2003 Intermediate level or have equivalent experience. If you are unsure, you may phone our Booking Administrator on (07) 857.0770 or (09) 630.1220 to request for a Training Needs Assessment form.

Who Should Take This Course
Microsoft Excel users who are familiar with the intermediate level functions and want to learn how to use the software's high end-user features.

Course Objectives

Upon completing this course, you will have learned how to:

1. Sort and manage data in lists.
2. Filter and query data.
3. Use outlines on worksheets.
4. Use lookup and database functions.
5. Use PivotTables to display and chart data.
6. Use data tables and scenarios to make projections about data.
7. Use Goal Seek, Solver, and the Analysis ToolPak to analyze data.
8. Audit worksheets.
9. Protect worksheets and workbooks.
10. Create and use macros.
11. Use and create templates.
12. Import and export information and documents.
13. Use workgroup features.
14. Work with custom views and Report Manager.
    
    

Course Outline

Working with More Functions

• Applying Nested IFs to test multiple conditions
• Combining the IF function with other functions such as AND, OR, ISBLANK, and ISERROR
• Examining the VLOOKUP function
• Joining several text strings into one text string with CONCATENATE function
• Using the NETWORKDAYS function to calculate workdays between two dates

Working with Data in Lists

• Understanding Data Lists
• Sorting Data
• Creating and Removing Subtotals
• Working with Data Forms
• Accessing the Data Form
• Entering Data into the Data Form
• Moving Through Records in the Data Form
• Editing and Querying Data in the Data Form
• Using Data Validation
• Restricting Cells to Numbers, Dates, or Times
• Restricting Cells to a List of Values
• Displaying an Input Message
• Displaying an Error Alert

Filtering and Querying Data

• Filtering Data
• Using AutoFilter and Removing AutoFilter
• Working with Filtered Data
• Using AutoFilter with Top 10
• Creating a Custom AutoFilter
• Filtering with a Single Comparison Criterion
• Filtering with Two Comparison Criteria
• Working with the Advanced Filter
• Setting Up the Criteria Range
• Examples of Criteria Ranges
• Filtering Data with the Advanced Filter
• Displaying All Data
• Performing Web Queries

Using Outlines

• Creating an Outline
• Hiding and Displaying Outline Levels
• Hiding and Displaying Outline Symbols

Using Lookup and Database Functions

• Looking Up Values in a Table
• Working with Database Functions
• Counting the Occurrences of a Value
• Finding the Sum of Specific Records
• Finding the Average of Specific Records

Working with PivotTables

• Creating PivotTables
• Editing PivotTables
• Updating Information in a PivotTable
• Adding Fields to a PivotTable
• Removing Fields from a PivotTable
• Changing the Layout of a PivotTable
• Selecting PivotTable Data
• Modifying PivotTables
• Grouping PivotTable Data
• Hiding and Showing Source Details
• Understanding Calculated Fields
• Adding Page Fields
• Displaying a Specific Page
• Displaying Page Fields on Separate Sheets
• Creating PivotCharts
• Creating Interactive PivotTables for the Web

Making Projections About Data

• Solving What-If Problems
• Using Data Tables
• Working with One-Input Data Tables
• Working with Two-Input Data Tables
• Creating and Managing Scenarios
• Creating Scenarios
• Displaying Scenarios
• Editing and Deleting a Scenario
• Creating a Summary of Scenario Results

Analyzing Data

• Working with the Goal Seek Feature
• Working with Solver
• Using the Analysis ToolPak

Auditing Worksheets

• Finding Referenced Cells
• Finding Formulas that Refer to a Cell

Protecting Worksheets and Workbooks

• Protecting Workbook Access and Contents

Working with Macros

• Working with Macros
• Recording, Running, and Assigning Macros
• Managing Macros
• Editing, Renaming, and Dleting Macros

Working with Templates

• Using, Creating, and Editing Templates

Importing and Exporting Information

• Importing and Exporting Documents
• Importing Documents from Other Applications
• Importing Text Files
• Importing a Table from an HTML File
• Exporting Workbooks
• Pasting Information from Other Programs
• Inserting Objects
• Inserting New Objects
• Inserting Existing Objects
• Editing Objects
• Inserting Pictures

Using Excel in a Workgroup

• Sending Documents to Other Users
• Sending a Document to a Mail Recipient as an Attachment
• Sending a Document in the Body of a Message
• Working with Cell Comments
• Adding Comments to Cells
• Reading and Editing Comments
• Formatting Comments
• Sharing Workbooks
• Distributing Shared Workbooks
• Merging Multiple Copies of a Shared Workbook
• Tracking Changes to Workbooks
• Accepting or Rejecting Changes
• Turning Off Workbook Sharing