Course Code: ax6
We'll explore the Excel Options dialog box, set up your Quick Access Toolbar, and take a look at the status bar and its customizable features. Then we'll explore data forms and databases, and look at a few alternative database techniques. We'll delve into advanced formatting and data forms, and you'll practice using a data form to search for and view records that match specific criteria.
We'll tackle data validation techniques. You'll practice using whole number, decimal, date, time, list, and other forms of validation. After that, we'll explore custom validation. Then it's time to work with custom controls—graphical objects that help facilitate data input. You'll learn how to create standard and drop-down list boxes, check boxes, option buttons, and group boxes. You'll also master the process of creating a dynamic list box.
You'll discover how to use formula conditional formatting. We'll work through nine different practice exercises that explore row conditional formatting. You'll see how to use conditional formatting to hide errors, highlight records based on multiple criteria, find the differences between two lists, how to set up a scorecard, and more. Then we’ll learn about consolidating and outlining, including how to use automatic and manual outlining to view or hide different levels or sections.
We'll focus on predefined calculations spanning four categories: Logical, Database, Math & Trig, and Lookup & Reference. We'll take a look at the Insert Function dialog box, the IF function, nesting functions, the DSUM function, and the VLOOKUP function. Next you'll see how to use the built-in Query Editor, and do an import from an external database that combines two database tables into a single import. Finally, you'll learn how to perform a web query.
You'll learn how to use two types of data tables: a one-variable data table (which lets you substitute just one variable into the formula calculation) and the two-variable data table (which allows you to change multiple aspects of the formula). Then we'll explore three more of Excel's what-if analysis tools: Goal Seek, Scenario Manager, and Solver. We'll practice using Scenario Manager and put Excel's Solver to work.
You'll discover how to use PivotTables, and practice both methods for creating them. You'll also learn how to edit a PivotTable, how to filter the table to create individual reports, how to format a PivotTable to make it reader-friendly, how to insert a Timeline, and how to create and use calculated fields and items. Finally, we look at the functions available in the Analysis ToolPak, including the Moving Average and Sampling tools.
Chad Wambolt has become an expert user of Microsoft Excel throughout the course of his professional career in the finance field. 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. He is a graduate of Boise State University, where he obtained his bachelor's degree in accounting, and built his career working for both privately and publicly held companies with sales ranging from $500 million to $3 billion.
There are no prerequisites to take this course.
Instructional material requirements:
Our course is so thorough, it really sets itself apart from other Excel training classes. You'll see how to work with the additional analysis tools provided by Excel add-ins and become skilled in using validation to protect the integrity of your worksheets from less experienced users. Impress your coworkers by learning how to add functional and eye-catching custom controls to any worksheet and how to use scenarios and data tables to quickly perform what-if data analysis.
You'll discover ways to work with databases and advanced techniques for PivotTables, like creating Timelines, calculated fields, and calculated items. You'll learn how to use Excel's consolidation function to efficiently summarize data from multiple sources, become adept at importing external data using Excel's built-in Power Query tool, and master the art of conditional formatting to highlight duplicate entries and other common worksheet problems.
Don’t just rely on Excel training books and manuals. A hands-on education that uses engaging materials and real-life examples is the way to get the most out of your training. But you don’t need to spend your valuable time traveling to classes with strict starting times, either. Learning Excel online is a great way to master new skills, on your own terms.
The version that we use in this course, Microsoft Excel 2016, is the latest version of the program. You can rest assured you’re getting all the most up-to-date information and learning about this version’s state-of-the-art features.
I've taken many Excel classes over my 25 years of practice, but Chad Wambolt's beginner, intermediate and advanced are the best by far! I appreciate his writing style and ability to explain the "How and Why" aspects of Excel. His courses are paced such that a student can learn and incorporate new skills, rather than just memorizing information for a final. I've started incorporating what I learned in these classes into my spreadsheets and have seen increases in both effectiveness through improved presentation and efficiency during creation."
Thank you for offering this course. I found a lot of useful information in this session. Some of it was complex and will require me to work with it more, but I do feel that it will come in very handy with my current position. I felt that you were always prompt in answering our questions in the discussion area. I would definitely recommend this course to others who use Excel a lot."
The online content was great. I am able to complete projects assigned to me at work and this class allowed me to give them a more professional appearance."