Some power Excel techniques¶
Intro and Objectives¶
There are numerous power Excel techniques that we’ll learn in this class that come in really handy for doing business analysis and modeling. In this first installment we’ll take a look at:
Data validation
Worksheet protection
Worksheet controls
Array functions and array formulas (these are magical)
Readings¶
We will see how powerful the Excel INDEX function can be in this module. A great reference for it is:
Downloads¶
Screencasts and other activities¶
We’ll start our introduction to power Excel techniques with this video. It introduces the general topic and reviews data validation and worksheet protection. We have actually touched on both of these already in the class.
Next, get an introduction to creating and using worksheet controls to start to build a simple GUI interface on your spreadsheets.
Let’s get our first real look at the mysterious world of Excel array functions and formulas.
Now that you know the basics of array formulas, let’s start to see how to use them to do some pretty magical stuff in Excel. Within the Powerpoint slides for this module (found in the Downloads zip file), you’ll find reference to a few more power techniques. Check out the slide on dynamic range names. We’ll revisit this soon when we talk about the general topic of “named array formulas” (NAF). I’ve included a slide and associated file on NAFs for those curious types who want to start exploring them right away.
I realized that Excel’s Evaluate Formula tool provides a nice way of visualizing how array functions work. So, here’s a very short vid that demonstrates the idea.
Explore¶
Somewhat recently, MS released some pretty big upgrades to array formulas. Unfortunately, they decided to release them in O365 only and not in Excel 2016 or Excel 2019. So, I’ll only be covering traditional array formulas in this class. The following blog post does a terrific job of describing the improvements to array functions that are now available in O365. For example, one big change is that you no longer have to use CTRL-SHIFT-ENTER to enter an array formula in O365.
