This two-day instructor-led course will increase your productivity by providing the advanced skills and knowledge to use Microsoft Excel 2003 effectively through group and individual instruction, hands-on practical exercises, visual aids, and comprehensive training manuals and materials.
Skills Gained:
Upon successful completion of this course, students will be able to automate common Excel tasks, apply advanced analysis techniques to more complex data sets, collaborate on worksheets with others, and share Excel data with other applications.
Course Modules:
Advanced Functions • What is a Function? • Text Functions • Mathematical Functions • Logical Functions • Nested If Functions
Working with External Data • About Microsoft Query • Starting a Query within Excel • Creating a Query • Editing the Data Results • Editing a Saved Query • Refreshing a Single Query • Refreshing All Queries At Once • Refreshing Queries Automatically
Using Hyperlinks in Spreadsheets • What is a Hyperlink? • Using a Hyperlink • Creating a Link to a Different Cell • Creating a Link to Other Worksheets • Creating a Link to Other Workbooks • Creating a Link to a Web Page • Formatting Hyperlink Styles • Editing a Hyperlink
Using Advanced Filtering Techniques • Advanced Filter • Using Functions to Analyse a List • Database Functions
Analysing Data • Using Goal Seek • One-Input Data Tables • Two-Input Data Tables • Editing Tables • Creating a Scenario • Viewing Scenarios • Editing a Scenario • Adding the Scenarios List to a Toolbar • Generating a Summary Report • Deleting and Merging Scenarios • Creating and Using Custom Views
Embedding and Linking Objects • Embedding Versus Linking • Embedding an Object • Linking an Object • Editing an Object • Updating Links • Changing the Link to Update Manually • Using Start Up Prompts in Excel • Hiding an Object’s Data • Deleting an Object
Protecting Workbooks • About Protection • Unlocking Cells • Protecting a Worksheet and Workbook • Unprotecting Workbooks and Worksheets
Pivottable and Pivotchart Reports • What is a Pivottable? • Creating a Pivottable • Applying Different Summary Functions • Displaying/Hiding Items in Fields • Rearranging a Pivottable • Using the Page Axis • Setting Pivottable Options • Creating a Pivottable from Multiple Sources • Creating a Pivotchart Report from a List • Creating a Pivotchart Report from a Pivottable
Array Calculations • Working with Arrays • Creating a Basic Array Calculation • Editing and Deleting an Array • Consolidating Information Using Arrays
Working with Controls • About Controls • Displaying the Forms Toolbar • Adding Controls to a Worksheet • Setting Properties for Controls • Using Linked Cells
Consolidation • Consolidating Worksheets • Creating Links to Source Data • Performing Other Summary Operations • Consolidation by Category
Working with Add - Ins • What is an Add - In? • Loading and Unloading an Add - In
Using Macros • What is a Macro? • Recording a Basic Macro • Assigning a Macro to a Toolbar • Running a Macro • Changing the Picture on a Toolbar • Deleting Macros • What is Vba? • Components of Code • Editing Vba Code • Writing Code • Adding Comments
Using the Web Tools • Converting an Excel Workbook into Html • Publishing Excel Items • Viewing Published Excel Items • Editing and Republishing Pages • Deleting Excel Web Pages
Who Should Attend:
This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building and formatting simple workbooks.
Prerequisites:
This course is designed for current Microsoft Excel users who can build basic workbooks. It is assumed that the user will know how to create and save a workbook, create formulas and use functions, and perform simple formatting of data.
