This one-day instructor-led coursee will provide a thorough understanding of advanced concepts and skills that will enable the participant to effectively use Microsoft Excel 2007 through group and individual instruction, hands-on practical exercises, visual aids, and comprehensive training manuals and materials.
Upon successful completion of this course, students will be able to:
• Increase productivity and improve efficiency by streamlining your workflow • Collaborate with others using workbooks • Audit worksheets • Analyze data • Work with multiple workbooks • Import and export data • Use Excel with the web • Creating PivotCharts • Create, edit and customise Macros
Security and Protection • Password Protection • Attaching a Password • Modification Password
Saving with a Backup Cell Protection • Protecting Cells
Functions • Function Overview • Functions Revision • Math & Trig Functions • The SUMPRODUCT Function • Rounding Functions • Financial Functions • The PMT Function • Logical Functions • The IF Function • Nested IF Functions • The AND Function • The OR Function • More Functions • Lookup Functions • The VLOOKUP Function • The HLOOKUP Function • Concatenating Functions • Index & Match Functions • Naming Ranges • The MATCH Function • The INDEX Function • Using the Function Library
Goal Seek • Using Goal Seek
Solver • Target Cell & Value • Saving a Scenario • Solver Reports
Scenarios • Adding a Scenario • Showing Scenarios
Linking • Worksheet Linking • Workbook Linking
Data Consolidation • Using Statistical Functions
Importing Data • Importing CSV Files • Text Import Wizard
Exporting Data • Choosing a Specific Format • Exporting to Microsoft Word • Pasting Data with a Link • Copying a Graph as a Picture
PivotTable Reports • Rearranging Data • Filtering a Report • Adding Fields • Field Settings • Refreshing Data • Formatting a PivotTable • Expanding and Collapsing Detail
PivotChart Reports • Creating a PivotChart • PivotChart Reports from scratch • Adding Fields to a PivotChart
Macros • Recording a Macro • Editing a Macro • Relative/Absolute Recording • Stepping through a Macro
This course is designed for students desiring to gain the skills necessary to create macros, collaborate with others, audit and analyze worksheet data, incorporate multiple data sources, and import and export data. In addition, the course is also for students desiring to prepare for the Microsoft Certified Application Specialist exam in Microsoft Office Excel 2007, and who already have knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data.
Before attending this course it is recommended participants have completed the Excel 2007 Level 1 and Level 2 training or have the equivalent working experience.
