|
|
|
Advanced Microsoft Excel 2003
|
| Price: |
$99.00 |
| Business Focus: |
Computer Applications |
| Instructor: |
Chad Wambolt |
| Course Code: |
aex |
| Delivery Method: |
Instructor-led Online Course |
| Frequently Asked Questions |
|
|
| Course Overview: |
Any proficient Microsoft Excel user will agree that Excel is a very powerful analytical tool. Wouldn't it be great to learn how to effectively use every Excel tool at your disposal? In this course, you'll learn how to maximize Excel's analytical and functional capabilities.
Most organizations rely heavily on Microsoft Excel to consolidate, analyze, and report financial information. Your company is probably no exception. By learning these advanced techniques, you can become more of a value to your organization. Your ability to generate information with increased accuracy, timeliness, and usefulness will lead to better decision-making.
In this practical and information-packed course, you'll learn how to use the additional analytical tools provided by Excel add-ins. You'll become skilled in the use of validation to protect the integrity of your worksheets from other, less experienced users. Impress your coworkers by learning how to add functional and eye-catching controls to any worksheet, and find out how to use scenarios and data tables to quickly perform multiple what-if analyses. You'll discover advanced pivot table techniques, like creating calculated fields and calculated items. You'll become adept at consolidating and importing data, and you'll master the art of conditional formatting to highlight duplicate entries and other common worksheet problems. And you'll become proficient at nesting functions within other functions to accomplish just about anything Microsoft Excel has to offer. |
| Instructor Biography: |
| Chad Wambolt is a graduate of Boise State University where he obtained his bachelor's degree in Accounting. He has spent his entire professional career in the finance field, working for both privately and publicly held companies with sales ranging from $500 million to $3 billion. Wambolt has become an expert user of Microsoft Excel through the course of his career. His knowledge of Excel's capabilities has helped organizations streamline processes and save countless hours of labor. Wambolt has taught Excel to students of varying skill levels since 1997. |
| Course Syllabus: |
| Lesson 1 Syllabus |
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. |
| Lesson 2 Syllabus |
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. |
| Lesson 2 Objective |
Customize Excel to gain efficiencies. |
| Lesson 3 Syllabus |
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. |
| Lesson 3 Objective |
Use a data form to manage an Excel table. |
| Lesson 4 Syllabus |
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. |
| Lesson 4 Objective |
Effectively use data validation to control cell input. |
| Lesson 5 Syllabus |
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! |
| Lesson 5 Objective |
Create custom controls to facilitate user input. |
| Lesson 6 Syllabus |
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. |
| Lesson 6 Objective |
Use formula conditional formatting. |
| Lesson 7 Syllabus |
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. |
| Lesson 7 Objective |
Effectively use the consolidate and outline features. |
| Lesson 8 Syllabus |
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. |
| Lesson 8 Objective |
Use functions and create nested functions. |
| Lesson 9 Syllabus |
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. |
| Lesson 9 Objective |
Import data from multiple sources. |
| Lesson 10 Syllabus |
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. |
| Lesson 10 Objective |
Create one-variable and two-variable data tables. |
| Lesson 11 Syllabus |
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. |
| Lesson 11 Objective |
Learn to use Goal Seek, scenarios, and Solver. |
| Lesson 12 Syllabus |
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. |
| Lesson 12 Objective |
Master the use of Excel PivotReports and PivotCharts. |
