About This Training Course

Advanced Microsoft Excel Training Course - The Excel Expert

Please Note: this Microsoft Excel Expert Version 2007/2010 Training Course is well suited to Microsoft Excel users who already have intermediate skills and experience in MS Excel and would like to take their knowledge and ability in Excel to the next level. If you would like to automate many of your daily repetitive tasks in microsoft excel, you will also benefit from this Microsoft Excel Expert Version 2007/2010 Training Course!

Subjects Covered

This Microsoft Excel Expert Version 2007/2010 Training Course will cover the following subjects:

Session 1: Foreign Data & Managing Excel Workbooks
1.1 Importing and Exporting Data into Micirosoft Excel
• Importing data created in other versions of MS Excel
• Importing data from other applications
• Using the text import wizard
1.2 Importing a Web Page
1.3 Exporting Data
1.4 Publishing Worksheets and Workbooks
• Publish worksheets and workbooks on the web
1.5 Using the Spreadsheet Component
• Publishing a page with the chart component
1.6 Managing Workbooks
• Applying a template
• Editing a template
• Creating a template
1.7 Working with Workspaces
• AutoFilter
• Custom Filters
1.8 Consolidating Data
• Use data consolidation

Session 2: Managing Workbooks
2.1 Auditing Formulas
• Auditing formulas
2.2 Locating and Resolving Errors
• Using the formula checker to resolve errors
2.3 Finding Precedents

Session 3: Making Excel even Easier
3.1 Naming Ranges
• Creating names in bulk
• Using names in formulas
3.2 Lookup & Reference Functions
• Using VLOOKUP
• Using LOOKUP
• Using INDEX and MATCH

Session 4: Automating and Customizing Excel
4.1 Advanced Number Formatting
• Creating custom formats
• Spacing codes
• Codes for dates and times
• Codes for text
4.2 Use Conditional Formatting
• Using formulas as conditions
4.3 Creating Macros in Excel
• Recording a macro
• Assigning shortcut keys
• Absolute and Relative cell references
• Editing macros
• Running macros
• Opening files that contain macros
4.4 Customizing Toolbars and Menus
• Adding commands to toolbars and menus
• Creating a custom toolbar
• Adding macros to toolbars and menus

Session 5: Subtotals, Lists & Ranges
5.1 Creating Subtotals
5.2 Defining & Applying Filters
• Define & Apply advanced filters
5.3 Using Grouping & Outlining
• Add group & criteria to ranges
• Automatically outlining a worksheet
• Manually outlining a worksheet
5.4 Using Data Validation
• Adding & creating validation rules
5.5 External Data & Creating Queries
• Retrieving external data
• Creating database queries
• Creating web queries
• Creating XML queries

Session 6: Analysing Data
6.1 PivotTables & PivotCharts
• Creating PivotTables with the wizard
• Adding items to the PivotTable
• Modifying the table layout
• Filtering PivotTable items
• Grouping PivotTable items
• Summary & format for data fields
• Applying an AutoFormat
• Creating PivotCharts
6.2 Using Business Analysis Tools
• Using the what-if-tools
• Creating data tables
6.3 Formatting & Modelling
• Using goal seek
• Using solver
• Working with scenarios

Session 7: Sharing Excel Data
7.1 Passwords, Protections and Properties
• Setting Workbook passwords
• Protecting a workbook
• Protecting worksheets and ranges
• Unlocking ranges
• Allowing users to edit ranges
• Setting workbook properties
7.2 Collaborating with Others
• Creating a shared workbook
• Tracking changes
• Updating changes
• Conflicting changes between users
• Include in personal view
• Highlight, accept & reject changes
• Merging workbooks

Prerequisites

What is assumed of the students?
• Students have reasonable keyboard skills.
• Students have reasonable mouse skills.
• Students have reasonable experience in Excel.
• Students have a basic knowledge of Windows.

Other Information

• A high quality manual
• Exercise files and templates
• Free online support
• A certificate of attendance - on demand

If you need further information contact Startnextweek.com.au on 1300 728 102