A new section of each course starts monthly. If enrolling in a series of two or
more courses, please be sure to space the start date for each course at least two
months apart.
All courses run for six weeks, with a two-week grace period at the end. Two lessons
are released each week for the six-week duration of the course. You do not have
to be present when lessons are released. You will have access to all lessons until
the course ends. However, the interactive discussion area that accompanies each
lesson will automatically close two weeks after the lesson is released. As such,
we strongly recommend that you complete each lesson within two weeks of its release.
The final exam will be released on the same day as the last lesson. Once the final
exam has been released, you will have two weeks to complete all of your course work,
including the final exam.
Week One
Wednesday - Lesson 01
In our first lesson, you'll learn how to change some of the more obscure Excel options to help you use your time more efficiently. For instance, you'll learn how to set the default file location so that you can open and save files with little effort, and we'll talk about how to change the standard font, font size, and number of worksheet tabs in a new workbook. We'll go over how to create custom toolbars and drop-down menus so you can quickly access the tasks you use the most. Don't like the position of a command on an existing menu? Today, you'll find out how to move it up or down on the list. By the time you finish this lesson, you'll be extremely comfortable with Excel's behind-the-scenes set-up options and all the customizations that are available to you.
Friday - Lesson 02
Excel is a wonderful tool that you can use to analyze fairly large amounts of data. This information is normally stored in Excel as a table. In today's lesson, you'll learn how to use AutoFilter, Sorting, and data forms to effectively manage these tables. A data form is a commonly overlooked tool that can help eliminate the redundancy of data entry. You'll learn how to use a data form to perform such table management tasks as editing, deleting, restoring, and searching for table records. You'll even learn how to use multiple search criteria to create a truly focused search.
Week Two
Wednesday - Lesson 03
In an office environment, workbooks are often shared and updated by multiple users. This sharing can lead to integrity issues with the captured data. In today's lesson, we'll explore how to use data validation to minimize the risk of receiving incorrect information. You can apply data validation to almost any situation to validate numbers, dates, text length, and values from a list. We'll even take it one step further and by creating custom validation rules. You'll also learn how to create valuable input and error messages to further aid the user.
Friday - Lesson 04
In today's lesson, you'll learn how create functional and eye-catching custom controls. What are custom controls? They're drop-down boxes, check boxes, or option buttons that you can add to any workbook. Wouldn't it be useful to control the values in one list box based on the value selected in the first list box? You'll find out just how easy it is when I show you how to create dynamic list boxes. After this lesson, your command of creating custom controls will surely impress your coworkers.
Week Three
Wednesday - Lesson 05
There's more to conditional formatting than just formatting based on cell values, and we'll explore all of Excel's conditional formatting options in this lesson. Formula conditional formatting is a powerful tool that you can use to make your life easier. Today, we'll discuss how to use formula conditional formatting to identify all the cells in your workbook that contain formulas. Then you'll discover how to use formula conditional formatting to hide error values in cells, highlight duplicate values in a list, compare values in two lists, and create a very useful scorecard to identify problem areas. The potential uses are nearly limitless!
Friday - Lesson 06
Today's lesson is all about data consolidation. First we'll discuss how to consolidate information from within the same workbook using category labels to filter the data just the way you want it. Then you'll discover how to consolidate data from multiple workbooks without even opening the external workbooks. We'll explore using wildcards in the consolidation process to further control the data you're consolidating. Then you'll get the chance to try out Excel's automatic outlining feature to display different summarization levels of information. If you're responsible for gathering information using Excel, you won't want to miss this lesson.
Week Four
Wednesday - Lesson 07
In this lesson, you'll discover how useful Excel's built-in functions can be. I'll show you how to take a couple basic functions and "nest" them together to form a very powerful combined function. You'll learn how to use the various database functions and how they differ from normal functions. We'll then explore using the conditional sum wizard add-in to perform a calculation similar to a DSUM or SUMIF function. Last, you'll discover practical uses for the VLOOKUP function and wonder how you ever lived without it.
Friday - Lesson 08
There are many ways to bring data into your Excel workbooks. Today, you'll learn how to use the data import feature to quickly and efficiently import data from most any outside source. We'll start by bringing in an entire workbook tab from another workbook. Then, you'll discover how easy it is to import data from an external database like Microsoft Access. The best part about these imports is that you can set them up to refresh the data at regular intervals so you always have the latest and greatest information from the source. We'll end the lesson with some practice using one of Excel 2003's new features, called a Web query.
Week Five
Wednesday - Lesson 09
Data tables are a powerful but often overlooked "what-if" analysis tool. This is probably because most people don't understand how simple and useful data tables can be. Imagine being able to see all the possible results of a calculation by changing the inputs to the calculation? In today's lesson, I'll show you how to use a one-variable and two-variable data table to accomplish this. And after this lesson, you'll be extremely comfortable adding this "what-if" analysis tool to your arsenal.
Friday - Lesson 10
This lesson is packed with useful information on how to use three of Excel's powerful "what-if" analysis tools. We'll discuss how to use goal seek to calculate one of the input cells when you know the desired result of the calculation. You'll learn how to use scenarios to quickly calculate and compare information. Then you'll discover how to use the solver tool to find the optimal solution to a problem by using different constraint parameters. If you're responsible for analyzing data, this lesson was designed with you in mind.
Week Six
Wednesday - Lesson 11
I can't think of a more powerful analytical tool than a pivot table. A pivot table allows you to see different views of information instantaneously by simply adding, removing, or changing an element within the table. We'll go beyond just creating a pivot table in this lesson. You'll also learn how to create a calculated field and calculated item within the table. Being able to create your own calculations within the table is where Excel's pivot table becomes an exceptional tool. I'll also teach you how to take the results of your pivot table and create an interactive pivot chart.
Friday - Lesson 12
In our final lesson, you'll discover some great tools that you can access just by installing the Analysis ToolPak Add-in. With this add-in, you can easily calculate a moving average or use its sampling techniques. We'll explore using the advanced filter for those situations when the regular auto filter isn't sophisticated enough. You'll finish the course by learning about some of the new features available in Excel 2003, including advancements in list management, comparing workbooks side by side, and using a watch window to see how calculations elsewhere change when you change an input cell.