Excel 2002 Advanced

This course will give students the ability to create advanced spreadsheets using the full range of Microsoft Excel 2002's tools and features. Students will learn to apply custom formatting and use templates; to import and export data in different formats; to use LOOKUP functions; and to create customized charts. Students will also learn how to customize and automate Excel using macros and toolbars.

Target audience

Users with a basic understanding of Excel 2002 who want to consolidate and expand on their knowledge of the basic features in a short space of time. Students should have a good working knowledge of Excel and must be able to create, format and print workbooks that incorporate basic mathematical, financial and logical functions, multiple worksheets, charts and data lists.

Course content

Custom Number and Conditional Formatting Apply number formats (accounting, currency, number) • Create a custom number or date format • Use conditional formatting

Using Named Ranges Add and delete a named range • Use a named range in a formula

Importing and Exporting Data in Excel Use Paste Special to import and manipulate data • Export worksheet data to other applications • Import data from a text file • Import a table from an HTML web page • Round trip HTML data in Excel

Workbook Templates and Add-Ins Create a template file for frequently used worksheet layouts • Edit and apply a template file • Use workgroup templates • Load an Add-in program

Working with Multiple Workbooks Save different views of a workbook • Use a workspace file • Link workbooks • Use Consolidate to perform arithmetic on multiple ranges automatically

Printing Workbooks and Reports Print and preview multiple worksheets • Print multiple workbooks • Use the Report Manager

Lookup and Reference Functions Use VLOOKUP and HLOOKUP to get values from multi-column tables • Sort values in rows or from left-to-right • Use the LOOKUP function • Prevent lookup errors

Customising Excel Toolbars Hide and display a toolbar • Dock and float toolbars • Customise a toolbar • Create a new toolbar

Using Macros in Excel Record a macro • Run a macro from the menu or the keyboard • Edit a macro • Assign a macro to a toolbar button or a menu • Implement macro virus file protection

Customising Excel Charts Reposition chart elements (titles, legend and labels) • Format chart axes (number format, text and line width) • Format data series (shading, spacing and width) • Explode segments of a pie chart • Modify the chart type for a data series • Delete a data series in a chart • Add a trendline to a chart • Create a combination chart • Insert an image into a chart • Store a custom chart as a chart template

Glossary

Index

About Courseware Company:: Instructor-led training courseware :: Self-study solutions :: Buy online today