Bookkeeping Institute of Australia Pty Ltd.
5/1407 Logan Road
Phone: 1300 729 844
Fax: (07) 3343 1511
Mobile: 0418 211 108
Home » Microsoft Excel Training Courses
To view details of pricing and available course dates or to make a booking please scroll down to the bottom of this page.
In addition to Visa, Mastercard or AmEx, payment options include EFT or Cheque in advance.
An e-mail Tax Invoice showing our bank account details will be generated by your booking.
Please note that all courses start at 9:15am and finish at 4pm.
BI-815 Microsoft Excel - Advanced
General Description:
This course covers aspects of Microsoft Excel beyond building basic workbooks and deals with workbook automation and productivity features. It includes areas such as absolute referencing, lookup functions, autofilters, scenarios, pivot tables, complex formulas, data analysis tools, and automating workbook operations using macros.
Learning Outcomes:
At the completion of this courseware participants will be able to:
· use names and labels · understand and use absolute referencing in a workbook · use a range of logical functions · use the various Lookup functions · nest functions to create complex formulas · use autofilter to display data in lists selectively · use advanced filters to analyse data in a list · use goal seeking to determine the values required to reach a desired result · create, use and modify data tables · create and work with scenarios and the Scenario Manager · create, modify and work with PivotTables · create recorded macros in Excel · use the macro recorder to create a variety of macros.
· use names and labels
· understand and use absolute referencing in a workbook
· use a range of logical functions
· use the various Lookup functions
· nest functions to create complex formulas
· use autofilter to display data in lists selectively
· use advanced filters to analyse data in a list
· use goal seeking to determine the values required to reach a desired result
· create, use and modify data tables
· create and work with scenarios and the Scenario Manager
· create, modify and work with PivotTables
· create recorded macros in Excel
· use the macro recorder to create a variety of macros.
Target Audience:
This course is intended for current Excel users who wish to learn how to extend their use of the software.
Prerequisites:
This course is designed for for current Excel users and assumes the participant has a basic understanding of the Windows environment and file management.
Methodology:
A self-paced hands-on approach is taken during this course. A case study is used to produce real-life scenarios so that learners experience how to use the software to solve everyday problems and undertake routine tasks. A course manual is included in the cost of this course. Course files are provided with this courseware.
Certificate:
The Bookkeeping Institute of Australia will issue a Certificate of Achievement upon completion of this course.
Table of Contents
1. Labels And Names 1. Labels And Names Explained 2. Accepting Labels In Formulas 3. Using Text Labels 4. Creating Labels 5. Creating Names Using The Name Box 6. Using Names To Select Cells 7. Using Names In Formulas 8. Creating Names Between Workbooks 9. Creating Names For Constants 10. Creating A List Of Names 11. Changing The Range For Names 12. Deleting Names 13. Labels And Names Quick Reference 2. Absolute Referencing 1. Absolute And Relative Referencing 2. Problems With Relative Formulas 3. Tracing Precedents 4. Creating Absolute References 5. Creating Mixed References 6. Circular References 7. Absolute Referencing Quick Reference 3. Logical Functions 1. Logical Function Concepts 2. Displaying Information Using IF 3. Displaying Values Using IF 4. Nesting An IF Function 5. Using The AND Function 6. Using The OR Function 7. Using The NOT Function 8. Logical Functions Quick Reference 4. Lookup Functions 1. Requirements Of Lookup Functions 2. The CHOOSE Function 3. The ISERROR Function 4. The VLOOKUP Function 5. Testing Lookup Functions 6. Using VLOOKUP For Exact Matches 7. The Index Function 8. Lookup Functions Quick Reference
5. Nesting Functions Workshop 1. Scoping A Formula 2. Developing A Nested Function 3. Creating A Nested Function 4. Editing A Formula With Nested Functions 5. Copying A Formula With Nested Functions 6. Concatenation 7. Nesting Functions Workshop Quick Reference 6. AutoFilter 1. Applying & Using AutoFilter 2. Creating Compound Filters 3. Creating Custom Filters 4. Multiple Value Criteria 5. Using Wildcards In Custom Criteria 6. AutoFilter Quick Reference 7. Advanced Filters 1. Advanced Filter Concepts 2. Using An Advanced Filter 3. Extracting Records With Advanced Filter 4. Using Formulas In Criteria 5. Using Database Functions 6. Advanced Filters Quick Reference 8. Goal Seeking 1. Goal Seek Components 2. Using Goal Seek 9. Data Tables 1. Using A Simple What-If Model 2. Creating A One-Variable Table 3. Using One-Variable Data Tables 4. Creating A Two-Variable Data Table 5. Data Tables Quick Reference
10. Scenarios 1. Creating A Default Scenario 2. Creating Scenarios 3. Using Names In Scenarios 4. Displaying Scenarios 5. Creating A Scenario Summary Report 6. Merging Scenarios 7. Scenarios Quick Reference 11. Pivot Tables 1. PivotTable Theory 2. Creating A Simple PivotTable 3. Adding Row Fields To A PivotTable 4. Using The Page Field In A PivotTable 5. Filtering Row And Column Values 6. Formatting A PivotTable 7. Counting With PivotTables 8. PivotTable Summary And Display Options 9. Show Data As Percentages In PivotTables 10. Calculated Fields In PivotTables 11. Calculated Items In PivotTables 12. Creating A PivotChart 13. Modifying A PivotChart Via The PivotTable 14. PivotTables Quick Reference 12. Recorded Macros 1. Setting Macro Security Levels 2. Recording A Simple Macro 3. Running A Recorded Macro 4. Relative Cell References 5. Running A Macro With Relative References 6. Viewing A Macro Module 7. Modifying A Recorded Macro 8. Recorded Macros Quick Reference 13. Recorder Workshop 1. Preparing Data For An Application 2. Recording A Summation Macro
« Previous | Next »
BIA Courses
Self-paced Course Overview Our Mt. Gravatt Location Bookkeeping Course Overview How Distance Learning Works 30 Week Bookkeeping Distance Timetable Introduction to Bookkeeping & Accounting Certificate of Bookkeeping using MYOB FNS30107 Cert III in Financial Services FNS40207 Certificate IV in Bookkeeping FNSBKPG404A Carry out BAS & IAS tasks Start Your Own Bookkeeping Business Start Your Own BB Workshop Recognition of Prior Learning Bookkeeping Associations BAS Agents Act
Administration
About us FAQ's Our people Testimonials Student enrolment policies Cancellation & refund policy Privacy policy Student support policies Compliments, complaints and appeals BIA Members Code of Ethics & Standards Recognition of existing qualifications Request to access personal records Administration login NTIS links BIA partner bookings
Associations & Organisations
Find A Bookkeeper Australian Bookkeepers Network Institute of Certified Bookkeepers Association of Accounting Technicians Association of Professional Bookkeepers Institute of Chartered Accountants CPA Australia National Institute of Accountants Association of Management Accountants Taxation Institute of Australia Australian Tax Office National Training Information Service Fair Work Australia Rapid Companies ASIC National Names Index
Tax Practitioners Board
Tax Practitioners Board Home Page Conditions of Registration Registration as an individual BAS agent Exposure Drafts Message from the Chair BAS agents and the regime What is a ‘BAS Service’? Code of professional conduct Events affecting continued registration Recognised BAS Agent Associations Tax Practitioners Board Members Statement of relevant experience Schedule of personal details
BAS Agent Register
