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.

To purchase this course, click the Enroll Now button below:

Recommended Courses:

Students who enrolled in Advanced Microsoft Excel 2003 were also interested in the following courses:

Syllabus:

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 the lesson is released, but you must complete each lesson within two weeks of its release.

A new section of each course starts on the second or third Wednesday of each month. 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.

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.

To purchase this course, click the Enroll Now button below:

This course includes a knowledgeable and caring instructor who will guide you through your lessons, facilitate discussions, and answer your questions. The instructor for this course will be Chad Wambolt.

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. Through the course of his career, Chad has become an expert user of Microsoft Excel. His knowledge of Excel's capabilities has helped organizations streamline processes and save countless hours of labor. Chad has taught Excel to students of varying skill levels since 1997.

To purchase this course, click the Enroll Now button below:

Requirements:

Completion of Craig Power's online Intermediate Microsoft Excel 2003 course (or equivalent experience), Microsoft Windows Vista, XP or 2000, Microsoft Excel 2003 or Microsoft Office 2003 (Student and Teacher Edition, Standard Edition, Small Business Edition, or Professional Edition are all acceptable - please be sure to install this software on your computer before the course begins), Internet access, e-mail, the Microsoft Internet Explorer or Mozilla Firefox Web browser, and the Adobe Flash and PDF plug-ins (two free and simple downloads you obtain at http://www.adobe.com/downloads by clicking Get Adobe Flash Player and Get Adobe Reader). This course is not suitable for Macintosh users.

Microsoft Excel 2000 and 2002 users will be supported in the discussion area.

To purchase this course, click the Enroll Now button below:

Student Reviews:

• "Best class I have taken so far. I really liked the Excel Workbook. It had the answer on one side and a work area on the other. It also allowed us to focus on the advanced features and not on data input."
• "Chad has been a great instructor in this course. His instructions were clear and concise. His dedication and efforts during the course were above and beyond what one could expect. His contributions in the discussions areas were what made this course exceptional. Thanks again, Chad, for an excellent course."
• "Chad is very helpful. I've learned lots of advance features which I had never ever discovered without this course. Great Materials! Great instructor! Thank you."
• "Chad, Just wanted to thank you for an excellent course! Many thanks for all your time and effort in creating the workbook and examples that we used throughout the class. I will be referring back to it many times as I try and incorporate many of the advanced features of Excel 2003 in my job."
• "Chad Wambolt is a great instructor. The Advanced Excel class was just what I expected, both tough and very informative. I am glad I printed out all lessons because I think I will "take the class again" on my own time. This class will help out for my business needs at the office and I thank Chad and everyone else for the opportunity to learn more about Microsoft Excel."
• "Enjoyed this course very very much! Found it to be well thought out, easy to follow, and extremely useful. I learned a lot of brand NEW things I had never done and was able to add to what I already know! BRAVO. I would recommend this course to others. Thanks."
• "Good course! Obviously a lot of thought and effort in the topics and presentation of them (as well as a lot of "work" with EXCEL). I was making use of the information from the very first lesson, and wishing I had known about these features several years ago when I started this job."
• "Great course! I liked the fact that there was a workbook download with all of the Lesson's data. Lots of screen shots really helped."
• "Great teaching approach for me - overview, example with practical application and tips."
• "I am very impressed with Chad Wambolt's knowledge and ability to articulate and provide prepared classroom spreadsheets. I am going to seek further ed2go courses where he is the instructor."
• "I cannot tell you how much I enjoyed your class! I am such a novice at computers, but you brought the class studies down to the level that even I could understand it! I would recommend you to anyone. Thanks!"
• "I consider myself an Excel power user - and learned quite a bit from this course. The worksheet provided by the insructor was very helpful. It was set up so you could see the before and after of each lesson - AND didn't waste a lot of the user's time entering data for an exercise. THANKS for giving us some great learning tools."
• "I found this course extremely helpful; the best on-line course I have taken to date."
• "I have enjoyed this class a lot and could not believe all of the information that I had learned. Did not realize how powerful Excel really was until this taking this course. I’ve taken this class it to expand my knowledge of the pivot tables for my development at work but took in much more than pivot tables. I would recommend this class to anyone that uses Excel. Excellent instructor – he did an excellent job on having the materials ready for use (workbook) so that you could jump right into the exercises. Hats off to you Chad Wambolt."
• "I have taken several "abbreviated" advanced Excel classes, but the content of this one was exceptional. I learned a great deal and look forward to applying some of these new tools. Thank you!"
• "I have taken several course through the internet. I have always found them to be very useful and have been able to incorporate them into my work. This course was especially good because the instructor supplied a Workbook file as an additional learning tool. That was an excellent visual and time saving learning tool. Also his exams were set up to make me go back and research which helped to reinforce the lesson. Thanks for this learning media, it works very well into my work schedule."
• "I have taken several on line classes and this was by far among the best of all. The Instructor provides a clear, understandable and interesting format to progress through some sophisticated items related to Excel. I would recommend this class to anyone who needs an in-depth knowledge of Excel and wants to be taught by the best."
• "I learned a lot, and this will help save me time at work!!!"
• "I really enjoyed the course. I have a much greater respect for excel. I definitely have a better understanding of what I see when I am using programs at work that have been designed by the "professionals". I like taking the lessons at home as I could go at my own pace and not be embarrased when I had difficulty with an item. Of course my cat and dog were a bit curious when I would spit and sputter at the computer at times! I love the online classes and look forward to taking more. Thanks to the instructor for a job well down!"
• "I really learned so much more information than in other Excel courses. Thank you for this experience."
• "I was able to pick up quite a number of new functions that I can use within my job. I will use the notes and lessons as a reference when I need to use some of the other capabilites of Excel. Good class materials."
• "Thanks very much! My schedule got very crazy but I was able to complete the course. I found that the questions asked by others and the responses from the Instructor and other students helped me greatly!"
• "Thank you for all your help & support. I have received a lot of useful information and will be able to use it on my job as well as at home. I will be doing a lot of practicing to keep up with it."
• "Thank you so much! I learned a great deal and feel your teaching style will help me retain what I've learned. If you ever decide to teach an Advanced, *Advanced* Excel course, I'm in."
• "The chapters were short and sweet and to the point. I loved the step-by-step examples. And I enjoyed Chad's conversational writing style. I also liked that he didn't assume his students knew everything there was to know up to this class."
• "The instructor's writing style and step-by-step instruction was supportive and engaging. I liked the fact that the examples used were practical and relatively universal. I learned a lot, and will continue to use the lessons as resources to refer back to because they are full of great time-saving tips and ways to maximize the use of the program."
• "The Supplementary Material section was very helpful. The assignments where top notch. I have not used Excel to the depths that this class was taught but I could follow the lesson with no problem. A very worthwhile class."
• "This class was excellent! I had no idea that Excel could do so much. Hopefully this new knowledge will help me advance my career. I would be very interested in taking an Excel Formulas and Functions class from you. You are a very effective instructor."
• "This class was excellent. It really pushed me past what I thought that I was capable of doing! I only have one regret--the class is over. Thanks!"
• "This course really allowed me to explore parts of Excel that I knew had to be there, I just didn't know how to access them. Chad is a good instructor and exceeded my expectation for an on-line teacher. I will look for more of his courses and will refer others to him"
• "This course was very helpful to my use of Excel. I'm already incorporating many of the time-saving tips in my work. The course has shown me many powerful tools of Excel that I was not using. Excellent job, Chad!"
• "This has definitely been a great learning experience and a great refresher course!"
• "This was a great course, and it has already improved my Excel skills tremendously."
• "This was an excellent class and Chad's teaching style was very clear and informative. He responded to everyone's questions promptly and thoroughly - the material was presented with examples relevant to today's business environment. The workbook that we downloaded and used throughout the entire session was a valuable tool that reinforced the subject matter and allowed us to actually practice what we were taught."
• "You have a number of fine instructors. This one is the best!"