Hi there dear reader, we've now reached a crossroads in your ability to use Excel as a means of data analysis. If you've been following along, you've learned how to use every tool that an Excel analyst would need to have in front of their mind. For those just joining us, these tools are as follows:

• Pivot Tables (both the report layout and the tabular layout)
• Lookup Functions
• IF + AND + OR statements
• And the combination of these three tools (and if you're particularly efficient, you may have dabbled in some VBA here and there)

However, if you have functioning vision (and once you've generated 10,000 hours worth of pivot tables and lookups...you may not need to see anymore), you may notice that Excel has about 20,000 more buttons that you've never interacted with! Any reasonable individual would assume that you must be missing something...but we're not reasonable...we're ANALYSTS.

To solve this puzzle, I ask you to think back to our discussion on databases, and how they quickly grow unwieldy: deleting code will often cause significantly more problems than just leaving the code in there and hoping no one wants to use it (have you ever glitched through a wall while playing a video-game only to see a half designed level...that's something that the dev team didn't have time to finish!)

One thing to remember is that the proto-version of Excel was one of the very first programs available on the personal computer (that would be VisiCalc for the Apple II if you're the type of person who's into esoteric computer trivia). It revolutionized the world...of accounting. After Windows overtook Apple in the PC department, Excel soon followed. Because it was the only game in town, it needed to do any calculation-related task that the user base (which at this time didn't include everyone) came up with.

If you've ever taken a computer programming class, this may sound familiar, in that it's the same design methodology as the programming language Java. Java was designed so that you could code ANYTHING on it and it would run on ANY platform. However, the downside of this is that except a small set of things that Java does pretty well, coding in Java quickly gets miserable, it's slow as molasses, and in case you haven't noticed, your Java applet needs to update whenever anyone in the world thinks of a new idea (seriously, check the lower right-hand corner of your screen, an update is pending right now!)

So now that this entertaining history lesson is over, let's leverage a few of those tools that our ancestors left us to make our (or more importantly, end-users) lives easier.

The first of these functions I care to talk about is the "naming of ranges". Back in the day, Excel wouldn't let you open up two workbooks at the same time, making it difficult and annoying to lookup between worksheets (granted, I'd argue that the best practice is that if you're planning on leaving those formulas on your worksheet, that you paste the source data into a new worksheet in your workbook or risk the breaking of your links). To make both inter and intra-worksheet lookups and formulas easier, Excel offers the ability to "name" a particular range (or "collection of cells"...the concept of a range becomes much more important if you're coding VBA). For example, here I have a list of random names:

Now, if I were to write a formula that would involve these names (such as finding out where DMX is hiding) I would probably use the location of the range (A:A, or $A$2:$A$8 if you're an absolute kinda person). However, I can also bless this column with a name.  

To do this, highlight the row or column, click formulas up top, and then go to "Define Name"

The interface automatically takes the first cell and assumes that you want it to be the name of the range (NO SPACES!) but I like to keep things to a single word, so let's call this "Names", and hit OK.

What you've now done is transformed anything in column A on sheet 1 of this workbook into a monolith known as "Names". While this sounds unimpressive on its own (what is a name?) it comes in handy in two ways.

1) If you're ever writing a formula that refers to column A, you can now type "Names" instead, no matter what sheet the formula starts in, as long as it's in the workbook (which saves you a ton of time, and makes it so that you don't need to click back to sheet 1 to select column A or to type 'Sheet 1!'A:A which you'll almost certainly screw up (In fact, that's probably wrong). For example, let's create a bunch of extra sheets to complicate things, and then try and find DMX.

The utility there is pretty evident, however, there's an even more useful reason for naming a range, and it involves another one of Excel's functions...the active-X controls. Plopping these controls into a workbook allows someone who calls tech support when their computer crashes to read your data...and as such, represent an important albeit un-enjoyable part of the job.

To drop one of these into your worksheet, first go to developer (which, as a person who's made it this far, I assume you've enabled), then insert (the cool insert, not the one for pleebs), and then active-X control (I generally use the combo box...and no, I don't know what it's a combination of). Check it out:

You'll then be asked to "draw" the box, generally, you want it to be roughly the size of two or three cells. Once that's done, it'll give you a seemingly normal drop-down box, but now, let's click properties to see where the magic happens.

Now, if you're interested, you can look up what all this stuff means, but for most people, the only things that matter are: List Fill Range, and maybe linked cell.

The list fill range should be populated with what you want the drop-down to say, and because you already named a range...all you gotta do is type that in, and it'll gobble up anything you type into the range you defined.

The linked cell (above list fill range) is a cell that you want to export the data from this cell into so that it can be interacted with by regular Excel stuff (like formulae), the cool kids will put that underneath the combo box so you can't see it, but for the sake of clarity, I'll show you how this works (the linked cell is G2):

First X out of the properties window, then un-click "design mode"

As a bonus, you can type in the first few letters of one of those names, and it'll auto-populate.

The coup-de-grace is that you can now name cell G2. You've essentially created a dynamic variable that someone can choose that can be shoved into formulae...and I hope you can see how this might be useful if you were building say...a report that checks the values associated with a particular name or ID (and you want to gently nudge someone reading your data into selecting only the values you have data for).

Congrats, you're now a step closer to sharing the fruit of your knowledge with the populace...and by using these tools, you don't need to share that knowledge with the very same populace (they won't be paying attention anyways), and that my friend, is how the world goes round.

-Snactive-X