Microsoft Excel Automation Course

Welcome to VTC's presentation of Excel Automation. My name is Lauri Matson and I will be your instructor. Please note that this is intended to be an advanced version for Excel. It is recommended that you take our Introduction to Excel first. Although I will introduce some of the basic features, the true aspect of this course it to delve into the powerful tools of Excel. Our objectives are to increase your productivity. In doing so, I will reduce your error ratio and I will simplify your workflow. To do this I will concentrate on automating your tasks, avoiding duplication and using the tools of Excel to get our work done twice as fast. This will help us eliminate frustration and increase the time that we have available for other projects. In order to achieve our objectives, we will be covering a variety of tasks, tools and tips. These include working with formulas, reviewing functions and delving into conditional formats. In our introduction of formulas, we will work with the lookups, arrays, conditional statements and other advanced components. Although I will have a very brief introduction to the philosophy of formulas, this is not intended as an introduction course. After working with formulas, I'll delve into the secrets behind macros and Visual Basic. I'll show you how you can learn Visual Basic starting with a macro. We'll use lookups and validation rules to protect the data that we have within our spreadsheets and we'll use these lookups to reduce errors. We will continue with chats about advanced charts, graphs and pivot tables. Now again, it'll be a brief introduction to the subject but we'll delve into the more specialty features such as single-point charts, dynamic graphs and even combination charts. In pivot tables we'll talk in terms of adding calculations, putting on conditional formats and even quickly adding all our pages to a quick view. Throughout we'll talk in terms of using named references. I'll show you how much time that can be saved by naming a reference rather than using a cell range. Also we'll take advantage of three-dimensional formulas, external links and using features such as Paste Special to eliminate a lot of the mundane tasks that we get caught on in a day-to-day aspect. I do recommend a specific course of action for learning these concepts. In order to ensure success, I'd really like to focus on the retention of the material. As a result, I recommend that first you just watch and listen. After going through the videos, then go back and practice the components. When practicing, review the workload that you have so that you can actually apply it to your actual spreadsheets. I have included Work Files with these movies so that you can print them and go through the step by steps of these concepts. For those of you using previous versions; I would like you to note that all of these labs are applicable. Keep in mind that it is only the shell that is different between 2007 and the prior Legacy versions. When we use the term shell, we talk in terms of what you see on the screen. The menu systems have indeed changed; however, once you get past those menu systems, the dialog boxes are the same. You will see that it's just the screen shot that's different. I have, though, included the screen shots from all the prior versions so that you can see exactly where the change takes place. You will find that the labs work wonderfully, the features are the same. It is the shell that is different. So whether you are using 2007, 2003, 2000 or 97, the movies and the labs are applicable for all.  

Download WorkFiles