Microsoft Excel: Advanced

Course details

This one-day course is designed to help a delegate develop spreadsheets using more advanced functions and creating more complex formulas. A delegate will learn to use LOOKUP functions to extract data from a table, distribute data in different formats and publish a spreadsheet on the web.

Course Outline

Module 1

• PivotTable Terms
• Create PivotTables
• Modify PivotTables
• Adding Data Fields to a PivotTable
• Grouping Items in a Field
• Hiding, Showing & Removing Data Fields
• Create Page Fields
• Create Separate Pages
• Format a PivotTable (Numbers / Tables)
• Changing the Data Source Range
• Refreshing Data
• Showing Underlying Details
• Chart a PivotTable
• Formatting Pivot Charts

Module 2

• Advanced Formula Construction
• Using VLOOKUP Function
• Using HLOOKUP Function
• Using VLOOKUP with an IF Function
• The LOOKUP Wizard
• Using Logical Functions
• IF Function
• Nesting If Functions
• AND/OR Function
• Financial Function — PMT Function
• Financial Function — FV Function

Module 3

• Create a One-Input Table
• Create a Two-Input Table
• Goal Seek
• Solver
• Scenario Manager

Module 4

• Automating with Visual Basic (Macros)
• Recording a Macro
• Running a Macro by Command
• Running a Macro by Shortcut Key
• Examining the Procedure
• Editing VBA Code
• Assign a Procedure to a Button
• Assign a Macro to a Toolbar
• Changing the Button Image
• Name the Toolbar Marco Button
• Edit the Macro Button

Module 5

• MicroSoft Query
• Using the Query Wizard
• Working with Columns
• Filtering Data
• Sorting Records
• Finishing the Query
• Editing & Updating Query Results
• Importing Text Data
• Exporting Data
• Saving a Worksheet as a Web Page
• Publish a Worksheet to the Web
• Auditing Workbooks & Worksheets
• Using Excel's Auditing Tools
• Tracing Arrows
• The Auditing Toolbar
• Sharing Workbooks

Module 6

• Templates
• Conditional Formatting
• Customizing Microsoft Excel
• Creating & Using Styles