Microsoft Excel 2007 Advanced
Dates:TBC
Time: 9am – 4pm
Venue:AP Courses Training Room
Cost: £295
Included in the price: Learning manual for the course; lunch, refreshments and post-course support
Course Description
Microsoft Excel 2007 Advanced is designed for users who are keen to extend their understanding and knowledge of the software. The skills and knowledge acquired in Microsoft Excel 2007 Advanced are sufficient to be able to use and operate the software at an efficient level.
Course Design
The course is a full-day tutor-led course that combines tutoring and practical work.
Target Audience
Microsoft Excel 2007 Advanced assumes a general understanding of personal computers and it would be beneficial for users to be comfortable in the Windows operating system environment. The course is intended for those in employment that involves using Microsoft Excel 2007 to create spreadsheets, insert PivotTables and use these to analyse data, use formulas and functions to provide financial and statistical information, import and export data and create basic macros.
Learning Objectives
At the completion of Microsoft Excel 2007 Advanced you should be able to:
·
Analyse your data and visually enhance your findings using conditional formatting techniques
·
Use advanced filters to view or extract matching records from a list of data
·
Summarise and present information in a PivotTable report
·
Create summary worksheets based on consolidated data from other individual worksheets
·
Use a variety of financial functions
·
Use the statistical function
·
Import data into, and export data from the program
·
Create recorded macros
Course Content
Conditional Formatting
Understanding Conditional Formatting
Highlighting Cells Containing Values
Highlighting Cells Containing Text
Highlighting Duplicate Values
Using Top and Bottom Rules
Using Data Bars
Using Colour Scales
Using Icon Sets
Creating Custom Rules
The Conditional Formatting Rules Manager
Managing Rules
Clearing Rules
Conditional Formatting - Examples and Guidelines
Advanced Filtering
Understanding Advanced Filtering
Using an Advanced Filter
Extracting Records Using Advanced Filters
Using Formulas in Criteria
Understanding Database Functions
Using Database Functions
Using DSUM
Using DMIN
Using DMAX
Using DCOUNT
PivotTables
Understanding PivotTables
PivotTable Basics
Working with the PivotTable Field List
Creating a PivotTable
Adding Fields to a PivotTable
Value Field Settings
Applying a Filter to a PivotTable
Changing the PivotTable Report Layout
Manipulating PivotTable Detail
Creating a PivotTable Report
Summarising Data
Summarising Data Features
Subtotalling a List
Using a Subtotalled Worksheet
Creating Nested Subtotals
Copying Subtotals
Data Consolidation
Understanding Data Consolidation
Consolidating Data with Identical Layouts
Consolidating Data with Different Layouts
Consolidating Data using 3D Sum Functions
Financial Functions
Understanding Financial Functions
Using PMT
Using FV
Using NPV
Using PV
Using RATE
Statistical Functions
Understanding Statistical Functions
Status Bar Statistics
Using COUNT and COUNTA
Using COUNTBLANK
Using COUNTIF
Using MODE
Using MEDIAN
Using LARGE and SMALL
Using STDEV
Using VAR
Importing and Exporting
Understanding Data Importing
Importing from an Earlier Version
Understanding Text File Formats
Importing Tab Delimited Text
Importing Comma Delimited Text
Importing Space Delimited Text
Importing Access Data
Working with Connected Data
Unlinking Connections
Exporting to Microsoft Word
Exporting Data as Text
Recorded Macros
Understanding Excel Macros
Setting Macro Security
Saving a Document as Macro Enabled
Recording a Simple Macro
Running a Recorded Macro
Relative Cell References
Running a Macro with Relative References
Viewing a Macro
Editing a Macro
Assigning a Macro to the Toolbar
Running a Macro from the Toolbar