An Advanced Lesson Plan for Excel

At end of an intermediate training course in Excel, a learner should be able to use some of the more advanced features of the Excel application. This training program will equip learners to use advanced features including how to use pivot tables, database functions and automating functions using VBA. It assumes knowledge of the learning outcomes of my basic and intermediate lesson plans for Excel 2007 . That’s why we start with Learning Outcome 6.

A learner should be able to use pivot tables to analyze consolidated data stored in workbooks. This will require them to:

• create a basic Pivot Table from the Insert Pivot Table option on the Insert tabbed ribbon, or by using the Pivot Table Wizard opened by the keyboard shortcut [ALT]+D, then P.
• manipulate, refresh, filter and sort data within the pivot table and add fields to a report generated from the pivot table
• create and format a pivot chart from the Insert Pivot Chart option below the Pivot Table option on the Insert tabbed ribbon or by using the Pivot Table Wizard opened by the keyboard shortcut [ALT]+D, then P.
• connect a Pivot Table to a external data source within the Insert Pivot Table option on the Insert tabbed ribbon, or by using the Pivot Table Wizard opened by the keyboard shortcut [ALT]+D, then P.

A learner should be able to use Excel as a database. This will require them to:

• sort data within a worksheet using the Sort option on the Data tabbed ribbon;
• create a custom sort list from Excel Options on the Microsoft Office Button
• filter data using the Filter option on the Data tabbed ribbon;
• filter data using a bespoke filter using the Advanced option from the Sort and Filter pane on the Data tabbed ribbon;
• validate data at entry using the Data Validation tool from the Data tools pane on the Data tabbed ribbon;
• find unique and remove duplicate values in a data list using the Advanced option from the Sort and Filter pane on the Data tabbed ribbon; and
• share data with other applications using the Get External Data pane on the Data tabbed ribbon.

A learner should be able to automate tasks in Excel. This will require them to:

• record a macro to automate repetitive tasks by making the Developer tabbed ribbon visible from the Office button if it is not already visible, selecting the Record macro option from the Developer tabbed ribbon, assigning a macro to a template and a keyboard shortcut, recording a series of keystrokes or similar operations, and stopping recording when required;
• run a macro by recalling it via its assigned keyboard shortcut;
• debug, and modify the Visual Basic Code using the Visual basic editor accessed via the Visual Basic option on the Developer tabbed ribbon;
• define their own custom functions using the Visual basic editor accessed via the Visual Basic option on the Developer tabbed ribbon;
• understand and use R1C1 style referencing in functions;
• trigger events within Visual Basic Code, including workbook events, chartsheet events, application level events using the Visual basic editor accessed via the Visual Basic option on the Developer tabbed ribbon; and
• develop forms to allow users to enter data directly and validate data at entry using the toolbar in the Visual basic editor accessed via the Visual Basic option on the Developer tabbed ribbon.

For lots more information about Microsoft Excel 2007, check out the other items in Bright Hub's Where Can I Find Free Excel 2007 Training and Tutorials.