Excel Advanced

After you’ve mastered formatting tables of data, creating formulas between cells, using built-in functions, and creating charts, there is a standard set of more powerful features you’ll eventually need. Whether you’re looking at someone else’s spreadsheet and wondering “how did they do that?” or simply lacking enough firepower to get your own spreadsheets to do what you want, this class will probably solve that problem.

Database Features. You may be thinking “I use spreadsheets, not databases!” — but there are a few indispensable Excel features that mirror the most useful of features from the world of databases: lookups and aggregate functions. A “lookup” in Excel is when your formula looks up values in a secondary worksheet (just like a database!) and puts them in the primary worksheet: for example, looking up in worksheet B the inventory amount adjacent to a cell containing a particular SKU, and putting it in worksheet A, adjacent to the cell containing that same SKU. Likewise, the SUMIF(), COUNTIF(), and similar Excel functions are counterparts of “aggregate data” calculations in databases – useful, for example, if you wanted to sum up the order values shipped to a particular zip code.

Errors. You’ve probably encountered your fair share of errors that Excel has vociferously pointed out with: “#N/A” and “#NUM!” It turns out there’s an easy way to deal with these errors, either by intercepting them and providing a backup value, or by auditing and fixing them once and for all. We’ll show you the two surefire methods of handling these errors.

Finding the Right Value(s). Sometimes you need to measure variations in a single cell value, like product price, to optimize the value of another cell, like profits. Other times, you need to optimize multipleinterconnectedcells, like price, volume, and inventory – to find the optimal value for profits. These two what-if scenarios are handled by the “Goal Seek” and “Solver” functions in Excel. Get ready to impress your boss.

Macros. These are recorded steps that can be memorized for later playback. For example, if you find yourself splitting text into columns along a delimiter and then conditionally formatting certain rows, you might want to record those steps and then instantly reapply them to a new table. These steps are saved in Excel's internal programming language, VBA (Visual Basic for Applications). Once you've recorded your steps, you can dive into the behind-the-scenes VBA code that activates those steps and customize them even further.

Pro Techniques. On your way to becoming an Excel pro, you’ll need a toolkit of useful standard tips, tricks, and techniques. Among these, you’ll probably find that functions such as LEFT(), MID(), and Excel’s Text-To-Columns and Consolidating Data features are the ones you’ll use the most often. We’ll show you why those are so popular and how to use them.