Ardito Corporate Training

Duration:2 days | Book this course
Cost: $560.00 + GST

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 2007 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