Excel 2007 Advanced - Online Course

The Excel 2007 Advanced series teaches you to apply filters, use formulas and analyze data. You will be able to apply your knowledge to create complex business applications and analyses in Microsoft Excel.

This series is for anyone interested in learning about advanced data analysis features of Excel 2007. This online course contains 6 lessons and should take approximately 12 hours to complete.

Lesson 1: Filtering and Sorting Data (2.0) hours

Filtering and Sorting Data teaches you how to remove duplicate records from an Excel worksheet, apply filters to an Excel worksheet, and apply one-click, multiple-level, and custom sorts to your data. This course provides information on how to filter and sort your worksheets in Excel 2007. Topics include Removing duplicate records, Using filters, Setting filter options, Sorting data, and One-click and custom sorting.

Lesson 2: Using Pivot Tables (2.0) hours

Using Pivot Tables teaches you how to create pivot tables, modify pivot tables, use pivot tables to quickly and easily analyze complex data sets, and apply pivot tables for common uses. This course explains how to create and use pivot tables in Excel 2007. Topics include Creating Pivot Tables, Viewing Pivot Tables, Formatting Pivot Tables, Grouping and Filtering Data in Pivot Tables, and Exploring Pivot Tables.

Lesson 3: Working with Functions (3.0) hours

Working with Functions teaches you how to calculate results with math functions, use date and time functions, adjust text with functions, work with logic and information functions, use lookup functions to locate data, determine payments with financial functions, and use statistical functions to determine relationships. This course introduces the use of Excel 2007 functions. Topics include Using functions in Excel, Calculating with math functions, Using date and time functions, Using functions to adjust text, Making decisions with logical functions, Testing with information functions, Finding data with lookup functions, Calculating payments with financial functions, and Finding relationships with statistical functions.

Lesson 4: Using Data Analysis Tools (1.0) hour

Using Data Analysis Tools teaches you how to use What-If analysis in Excel 2007, create analyses with Scenario Manager, analyze data using Goal Seek, and install Solver. This course provides an introduction to the commands used to analyze data in Excel 2007. Topics include Using What-If Analysis, Creating Analyses with Scenario Manager, Using Goal Seek, and Exploring Solver.

Lesson 5: Automating with VBA Macros (2.0) hours

Automating with VBA Macros teaches you how to enable the Developer tab on the Ribbon, modify macro security settings, record and run macros, record a macro that uses an advanced filter, customize macros for everyday use, and combine elements of written code with recorded code. This course introduces the use of macros in Excel 2007. Topics include Setting VBA security, Recording and Running Macros, Using the End Key, Filtering with Macros, Understanding VBA Code, Writing Macros, and Controlling and Combining Macros.

Lesson 6: Adding Connections and Importing Data (2.0) hours

Adding Connections and Importing Data teaches you how to connect two worksheets in separate workbooks, use the Links tab on the Trust Center, set up a connection to a Web page, import an XML file, and connect to an Access database using Microsoft Query. This course provides detail about connecting worksheets and workbooks for automatic sharing and updating. It also provides detail about importing information from the Web, text files, XML data, Access documents, and other files from external data sources. Topics include Connecting Two Worksheets, Connecting Two Workbooks, Connecting to Data on a Web Page, Connecting to a Text File, and Connecting to a Database.