Microsoft Excel 2007 Expert
Courseware Training Materials
Part of the Computer Expert Series

Intermediate / Advanced level computer training materials for delivering training to your students, staff or clients.  A courseware site licence allows you to train an unlimited number of people, at your training center using these teaching resources.

Training Course Outlines 

FUNCTIONS AND FORMULAS.

• Getting Help with Functions.
• Inserting the current time and date.
• Nested functions.
• Consolidating data using a 3-D reference sum function.
• Mixed references within formulas.
• Time & Date functions - TODAY.
• Time & Date functions - NOW.
• Time & Date functions - DAY.
• Time & Date functions - MONTH.
• Time & Date functions - YEAR.
• Mathematical functions - ROUND.
• Mathematical functions - ROUNDDOWN.
• Mathematical functions - ROUNDUP.
• Logical functions - IF.
• Logical functions - AND.
• Logical functions - OR.
• Mathematical functions - SUMIF.
• Statistical functions - COUNT.
• Statistical functions - COUNTA.
• Statistical functions - COUNTIF.
• Statistical functions - COUNTBLANK.
• Statistical functions - RANK.
• Text functions - LEFT.
• Text functions - RIGHT.
• Text functions - MID.
• Text functions - TRIM.
• Text functions - CONCATENATE.
• Financial functions - FV.
• Financial functions - PV.
• Financial functions - NPV.
• Financial functions - RATE.
• Financial functions - PMT.
• Lookup functions - VLOOKUP.
• Lookup functions - HLOOKUP.
• Database functions - DSUM.
• Database functions - DMIN.
• Database functions - DMAX.
• Database functions - DCOUNT.
• Database functions - DAVERAGE

NAMED RANGES.

• Naming cell ranges.
• Removing a named range.
• Named cell ranges and functions.

CELL FORMATTING.

• Applying styles to a range.
• Conditional formatting.
• Custom number formats.

MANIPULATING WORKSHEETS.

• Copying or moving worksheets between workbooks
• Splitting a Window.
• Hiding rows.
• Hiding columns.
• Hiding worksheets.
• Un-hiding rows.
• Un-hiding columns.
• Un-hiding worksheets.

TEMPLATES.

• Using templates.
• Creating Excel templates.
• Displaying hidden template folders on a Windows Vista computer.
• Editing Excel templates on a Windows Vista computer.
• Displaying hidden template folders on a Windows XP computer.
• Editing Excel templates on a Windows XP computer.

PASTE SPECIAL OPTIONS.

• Using Paste Special to add, subtract, multiply & divide.
• Using Paste Special ‘values’.
• Using Paste Special transpose option.

PIVOT TABLES.

• Creating and using a pivot table.
• Filtering and sorting data within a pivot table.
• Automatically grouping data in a pivot table and renaming groups.
• Manually grouping data in a pivot table and renaming groups.

INPUT TABLES

• One-input data tables.
• Two-input data tables.

CHARTS.

• Creating a combined line and column chart.
• Adding a secondary axis to a chart.
• Changing the chart type for a particular data series.
• Adding a data series to a chart.
• Removing a data series from a chart.
• Re-positioning chart title.
• Re-positioning the chart legend.
• Moving and formatting chart data labels.
• Modifying chart axis scales.
• Formatting an axis to display using commas.
• Inserting images into chart columns.
• Inserting images to chart bars.
• Formatting the chart plot area using a picture.
• Formatting the chart area using a picture.

HYPERLINKS

• Inserting Hyperlinks.
• Editing Hyperlinks
• Removing Hyperlinks

LINKING & EMBEDDING

• What is embedding and linking?
• Linking data within a worksheet.
• Linking cells between worksheets within a workbook.
• Linking data between workbooks.
• Linking data from Excel to a Word document.
• Linking an Excel chart to a Word document.
• Updating, locking and breaking links.

IMPORTING TEXT FILES

• What is a delimited text file?
• Importing a delimited text file.

SORTING AND FILTERING DATA.

• Sorting data by multiple columns at the same time.
• Applying a pre-installed custom sort.
• Creating a customized list and performing a custom sort.
• Removing a customized list.
• Using AutoFilter.
• Using AutoFilter to perform multiple queries.
• Top 10 AutoFilter.
• Removing all AutoFilters from a worksheet.
• Advanced Filter Criteria.
• Sub-totaling.
• Removing subtotals.
• Expanding and collapsing outline detail levels.

TRACKING AND REVIEWING CHANGES.

• Enabling or disabling the 'track changes' feature.
• Sharing, comparing and merging worksheets.

SCENARIOS.

• Scenario Manager.
• Scenario summary reports.

VALIDATING.

• Data validation - Whole number.
• Data validation - Decimal number.
• Data validation - List.
• Data validation - Date.
• Data validation - Time.
• Data validation - Text Length.
• Customizing a validation input message and error alert.
• Removing data validation.

AUDITING.

• Tracing precedent cells.
• Tracing dependent cells.
• Identifying cells with missing dependents.
• Showing all formulas in a worksheet, rather than the resulting values.
• Inserting and viewing comments.
• Editing and deleting comments.
• Showing and hiding comments.

MACROS.

• Macro to change the page set-up.
• Macro to apply a custom number format.
• Macro to format a cell range.
• Macro to insert fields into the header or footer.
• Assigning a macro to a button on the Quick Access toolbar.
• Deleting macros.

SECURITY

• Adding 'open' password protection to a workbook.
• Adding 'modify' password protection to a workbook.
• Removing an 'open' password from a workbook.
• Removing a 'modify' password from a workbook.
• Password protecting cells and worksheets.
• Hiding formulas.
• Un-hiding formulas.