Mircrosoft Excel 2007 Level 3
Format
Workshop
Accredited
No
Email
Course Description
Microsoft excel 2007 level 3 focuses on the more advanced features of excel 2007. Advanced anaylsis tools, especially pivot tables, solver, outlining and summarising are covered, as are some key automation features such as macros.
CONTENT
Lookup functions: Understanding data lookup functions; Using CHOOSE; Using VLOOKUP; Using VLOOKUP for Exact Matches; Using HLOOKUP; Using INDEX; Using MATCH; Understanding reference functions; Using ROW and ROWS; Using COLUMN and COLUMNS; Using ADDRESS; Using INDIRECT; Using OFFSET
Setting Excel options: Understanding excel options; Personalising excel; Setting the default font; Setting formula options; Understanding save options; Setting save options; Setting the default file location; Setting advanced options
Chart object formatting: Understanding chart object; Formatting; Selecting chart elements; Using shape styles to format; Objects; Changing column colour; Changing pie slice colour; Changing bar colours; Changing chart line colours; Using shape effects; Filling the chart area and the plot area; Filling the background; The format dialog box; Using the format dialog box; Using themes
Labels and names: Understanding labels and names; Creating names using text labels; Using names in new formulas; Applying names to existing formulas; Creating names using the names box; Using names to select ranges; Pasting names into formulas; Creating names for constants; Creating names from a selection; Scoping names to the worksheet; Using the name manager; Documenting range names
Protecting data: Understanding data protection; Providing total access to cells; Protecting a worksheet; Working with a protected worksheet; Disabling worksheet protection; Providing restricted access to cells; Password protecting a workbook; Opening a password protected workbook; Removing a password from a workbook
Summarising and subtotalling: Creating subtotals; Using a subtotalled worksheet; Creating nested subtotals; Copying subtotals; Using subtotals with autofilter; Installing the conditional sum wizard; Using the conditional sum wizard; Creating relative names for subtotals; Using relative names for subtotals
Data linking: Understanding data linking; Linking between worksheets; Linking between workbooks; Updating links between workbooks
Data consolidation: Understanding data consolidation; Consolidating with identical layouts; Creating an outlined consolidation; Consolidating with different layouts
PivotTables: Understanding pivot tables; Creating a pivot table shell; Dropping fields into a pivot table; Filtering a pivot table; Clearing a report filter; Switching pivot table labels; Formatting a pivot table
PivotTable techniques: Using compound fields; Counting in a pivot table; Formatting pivot table values; Working with pivot table grand totals; Working with pivot table subtotals; Finding the percentage of total; Finding the difference from; Grouping in pivot tables; Creating running totals; Creating calculated fields; Providing custom names; Creating calculated items; Pivot table options; Sorting in a pivot table
Pivot charts: Creating a pivot chart shell; Dragging fields for the pivot chart; Changing the pivot chart type; Using the pivot chart filter pane; Moving pivot charts to chart sheets;
Goal seeking: Goal seek components; Using goal seek
Grouping and outlining: Understanding grouping and outlining; Creating an automatic outline; Working with an outline; Creating a manual group; Grouping by columns
Solver: Understanding how solver works; Setting solver parameters; Adding solver constraints; Performing the solver operation; Running solver reports; Refining solver answers
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; Assigning a keyboard shortcut to a macro; Deleting a macro; Copying a macro; Tips for developing macros
Recorder workshop: Preparing data for an application; Recording a summation macro; Recording consolidations; Recording divisional macros; Testing macros; Creating objects to run macros; Assigning a macro to an object
OUTCOMES
At the completion of Microsoft Excel 2007 Level 3 you should be able to:
• use a range of lookup and reference functions
• modify Excel options
• create and use labels and names in a workbook
• protect data in worksheets and workbooks
• summarise data using subtotals and relative range naming
• use data linking to create more efficient workbooks
• use the data consolidation feature to combine data from several workbooks into one
• understand and create simple Pivot tables
• construct and operate Pivot tables using some of the more advanced techniques
• use goal seeking to determine the values required to reach a desired result
• group cells and use outlines to manipulate the worksheet
• use Solver to solve more complex and intricate problems
• create recorded macros in Excel
• use the macro recorder to create a variety of macros
CONTENT
Lookup functions: Understanding data lookup functions; Using CHOOSE; Using VLOOKUP; Using VLOOKUP for Exact Matches; Using HLOOKUP; Using INDEX; Using MATCH; Understanding reference functions; Using ROW and ROWS; Using COLUMN and COLUMNS; Using ADDRESS; Using INDIRECT; Using OFFSET
Setting Excel options: Understanding excel options; Personalising excel; Setting the default font; Setting formula options; Understanding save options; Setting save options; Setting the default file location; Setting advanced options
Chart object formatting: Understanding chart object; Formatting; Selecting chart elements; Using shape styles to format; Objects; Changing column colour; Changing pie slice colour; Changing bar colours; Changing chart line colours; Using shape effects; Filling the chart area and the plot area; Filling the background; The format dialog box; Using the format dialog box; Using themes
Labels and names: Understanding labels and names; Creating names using text labels; Using names in new formulas; Applying names to existing formulas; Creating names using the names box; Using names to select ranges; Pasting names into formulas; Creating names for constants; Creating names from a selection; Scoping names to the worksheet; Using the name manager; Documenting range names
Protecting data: Understanding data protection; Providing total access to cells; Protecting a worksheet; Working with a protected worksheet; Disabling worksheet protection; Providing restricted access to cells; Password protecting a workbook; Opening a password protected workbook; Removing a password from a workbook
Summarising and subtotalling: Creating subtotals; Using a subtotalled worksheet; Creating nested subtotals; Copying subtotals; Using subtotals with autofilter; Installing the conditional sum wizard; Using the conditional sum wizard; Creating relative names for subtotals; Using relative names for subtotals
Data linking: Understanding data linking; Linking between worksheets; Linking between workbooks; Updating links between workbooks
Data consolidation: Understanding data consolidation; Consolidating with identical layouts; Creating an outlined consolidation; Consolidating with different layouts
PivotTables: Understanding pivot tables; Creating a pivot table shell; Dropping fields into a pivot table; Filtering a pivot table; Clearing a report filter; Switching pivot table labels; Formatting a pivot table
PivotTable techniques: Using compound fields; Counting in a pivot table; Formatting pivot table values; Working with pivot table grand totals; Working with pivot table subtotals; Finding the percentage of total; Finding the difference from; Grouping in pivot tables; Creating running totals; Creating calculated fields; Providing custom names; Creating calculated items; Pivot table options; Sorting in a pivot table
Pivot charts: Creating a pivot chart shell; Dragging fields for the pivot chart; Changing the pivot chart type; Using the pivot chart filter pane; Moving pivot charts to chart sheets;
Goal seeking: Goal seek components; Using goal seek
Grouping and outlining: Understanding grouping and outlining; Creating an automatic outline; Working with an outline; Creating a manual group; Grouping by columns
Solver: Understanding how solver works; Setting solver parameters; Adding solver constraints; Performing the solver operation; Running solver reports; Refining solver answers
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; Assigning a keyboard shortcut to a macro; Deleting a macro; Copying a macro; Tips for developing macros
Recorder workshop: Preparing data for an application; Recording a summation macro; Recording consolidations; Recording divisional macros; Testing macros; Creating objects to run macros; Assigning a macro to an object
OUTCOMES
At the completion of Microsoft Excel 2007 Level 3 you should be able to:
• use a range of lookup and reference functions
• modify Excel options
• create and use labels and names in a workbook
• protect data in worksheets and workbooks
• summarise data using subtotals and relative range naming
• use data linking to create more efficient workbooks
• use the data consolidation feature to combine data from several workbooks into one
• understand and create simple Pivot tables
• construct and operate Pivot tables using some of the more advanced techniques
• use goal seeking to determine the values required to reach a desired result
• group cells and use outlines to manipulate the worksheet
• use Solver to solve more complex and intricate problems
• create recorded macros in Excel
• use the macro recorder to create a variety of macros
Presenter
Paul Neale
Paul Neale has had vast experience teaching all MS Office applications from basic to advanced levels. His qualifications include Microsoft Certified Professional, Cert IV Workplace Training and Assessment, and Cert IV Small Business Management. He is an experienced Project Management and Team Leader. He is also an IT trouble-shooter, experienced in hardware assembly, network design, back-office apps and helpdesk support.
Who Should Attend
Microsoft Excel 2007 Level 3 is designed for users who are already familiar with basic
Excel features and operations and who are now ready to explore more of the advanced analysis and automation tools in Excel
Before the course...
Microsoft Excel 2007 Level 3 assumes a good general understanding software and experience in the construction and modification of workbooks. The student should be able to copy within Excel, create charts, and create basic formulas. It would be beneficial to have a general understanding of personal computers and the Windows operating system environment.
Excel features and operations and who are now ready to explore more of the advanced analysis and automation tools in Excel
Before the course...
Microsoft Excel 2007 Level 3 assumes a good general understanding software and experience in the construction and modification of workbooks. The student should be able to copy within Excel, create charts, and create basic formulas. It would be beneficial to have a general understanding of personal computers and the Windows operating system environment.
Start Date
Thursday, 18/03/2010
Time
10 AM - 4 PM
Course Duration
1 day
Venue
See http://www.cce.usyd.edu.au/
Cost
$218
Contact Details
Centre for Continuing Education - The University of Sydney
(02) 9036 4789
cce.info@usyd.edu.au
(02) 9036 4789
cce.info@usyd.edu.au