Advanced Diploma in Microsoft Excel

How to import data from external sources

How to import data from external sources, Databases, Websites as well as other Workbooks into Excel.
Topics covered in this module include:

• Importing Data into Excel
• Importing from an external data source
• Using a database query
• Importing data from a Web page
• Exercise / Solutions
• Summary

Data management

A more complex look at data management by using Tables in Excel. Topics covered in this module include:
• Advanced Working with Tables
• Creating an modifying a table
• Using data forms to manage tables
• Using advanced filters
• Using subtotals
• Grouping and outlining data
• Using data validation
• Circling invalid data
• Exercise / Solutions
• Summary

Data validation

Using automated tools for data and validation. Topics covered in this module include:

• Analysing Data with Automated Tools
• Using the Analysis ToolPak
• Using Goal Seek
• Using a data table
• Using the Solver
• Exercise / Solutions
• Summary

Setting Scenarios

Setting Scenarios to ascertain a variety of 'What if?' results. Topics covered in this module include:
• Analysing Data with Scenarios
• Creating and showing scenarios
• Merging scenarios
• Creating a scenario report
• Exercise / Solutions
• Summary

Pivot Tables and Pivot Charts

How to use Pivot Tables and generating Pivot Charts. Topics covered in this module include:
• Using PivotTables and PivotCharts
• Creating a PivotTable report
• Specifying the data to analyse
• Modifying a PivotTable report
• Updating a PivotTable
• Grouping dates in a PivotTable
• Using the Report Filter area
• Drilling down to the detail
• Changing the type of calculation
• Filtering in a PivotTable
• Grouping data in a PivotTable
• Sorting PivotTables
• Formatting a PivotTable
• Charting a PivotTable
• Exercise / Solutions
• Summary

Module 6

Using the wide range of Excel functions to analyse data. Topics covered in this module include:
• Analysing Data with Functions
• Using the Lookup functions
• Using the Conditional Sum Wizard
• Using Database functions
• Exercise / Solutions
• Summary

Security

Setting security and passwords to protect data stored on Worksheets as well as hiding functions and files. Topics covered in this module include: • Protecting Cells, Sheets, and Books
• Protecting cells
• Protecting worksheets
• Protecting workbooks
• Attaching and viewing digital signatures
• Adjusting macro settings
• Exercise / Solutions
• Summary

How to share data

How to share data and Workbooks with others in a workgroup as well as other collaborators online.Topics covered in this module include: • Collaborating with Others
• Creating a shared workbook
• Viewing and removing users
• Resolving conflicting changes
• Viewing the change history
• Comparing and merging workbooks
• Tracking changes to a workbook
• Accepting and rejecting changes
• Removing workbook sharing
• Exercise / Solutions
• Summary

Correctly managing Data and Workbooks for effective and efficient Saving and Storage. Topics covered in this module include:
• Managing Data and Workbooks
• Publishing workbooks as Web pages
• Creating a workbook template
• Editing a workbook template
• Consolidating worksheets
• Hiding columns, rows, sheets, and books
• Saving a custom view
• Viewing and changing workbook properties
• Exercise / Solutions
• Summary