This one-day course is designed to help a delegate develop spreadsheets using more advanced functions and formulas.
English
Course is facilitated by a competent & experienced subject matter trainer, who utilises the following techniques to ensure that the session is practical and experiential:Â Questions; Discussion; Exercises & Assessments.
Any delegate wanting to develop their skills in using Microsoft Excel to design spreadsheet solutions and use tools for creating and formatting more complex spreadsheets, that solve more difficult problems.
1 Day
Module 1 | Working with Excel Tables Structured References Create PivotTables View Source Detail Information Summary Functions PivotTable Default Summary Function Adding a Zero Value to Empty Cells Grouping Data Removing and Redisplaying Subtotals Removing and Redisplaying Subtotals Report Layouts Formatting PivotTables Updating and Refreshing Data Create PivotCharts Using Filter Slicers Using Timeline Filters |
Module 2 | The VLOOKUP Function Understanding the VLOOKUP FALSE Understanding the VLOOKUP TRUE The HLOOKUP Function Logical Functions The IF Function Nesting IF Functions Using the VLOOKUP with an IF Function Financial Functions The PMT Function The FV Function |
Module 3 | What-If Analysis Tools Creating a One or Two Variable Data Table Using the Goal Seek Installing the Solver Add-In Using Solver Using the Scenario Manager to Create Multiple Analysis of the same set of Data Creating Names from Selection |
Module 4 | Understanding Macros Working with VBA Code Editing VBA Code Assigning Macros to the Quick Access Toolbar Assigning Macros to Shapes Saving an Excel Workbook that contains Macros Macro Security Settings |
Module 5 | Import and Export Data Microsoft Query Starting the Microsoft Query from Excel Using the Query Wizard Working with Columns Sorting and Filtering Records Finishing the Query Updating Query Results Importing and Exporting Text Data Save all or part of a Worksheet as a Static Web Page Publishing a Worksheet to the Web Auditing Workbooks and Worksheets using Excel’s Auditing Tools Tracing the flow of Data and Formulas Using the Error Checking Using the Watch Window to watch Formula Results Sharing Workbooks |
Module 6 | Creating & Saving Templates Using Templates Editing Templates Conditional Formatting Customize Excel Creating & Using Styles |