In my last entry, I took you on a journey to unlock the secrets of Excel; essentially making you a spreadsheet wizard. However, one important thing to remember at work is that a company can't function if it's comprised of only wizards (tell me to my face that Hogwarts was a functioning school!)

That begs the question, if I'm not doing wizardry, how can I still increase my Excel productivity? (that and the fact that macros can be scary at first!) The answer lies in using wild combinations of Formulae to achieve results that the program was never designed to give.

For the extremely green among you, an Excel formula is essentially text that enters a cell that asks Excel to do some form of calculation. To engage in this most sacred of duties, one simply needs to click an empty cell and hit the = sign, that's how all formulae start.

You'll notice that a BIG list pops up with different formulae that you can choose from, but the truth is twofold:

1. There are only maybe a handful of formulae worth using, and
2. It's way quicker (and more impressive to the less informed) to write them in yourself.

Each formula is comprised of the following parts:

1. The Command is the phrase immediately after the "=" which tells Excel to do that thing.
2. An open parenthesis immediately after the command
3. One or more variables which you're telling Excel to calculate with (one hot tip is that you can always tell what kind of variables you need as well as how many by simply typing in the command you're looking to use).
4. A closed parenthesis (which is always how formulae should end).

Reading this, you may say "so what, isn't who whole point of this blog to stop using Excel as an overpriced (although still cheaper than a TI-89+) calculator?" While this is a very valid question, please allow me to blow your mind and remind you that anything you see on a computer is just a combination of 1s and 0s, and you can change those 1s and 0s into whatever you damn well please...so long as you know how to ask. In other words, essentially anything you see in Excel can be calculated with or on by almost anything else, you need to get creative.

Now that this primer is done, let's try your newly acquired skills using the most rudimentary Excel functions: =SUM. Don't fret about this small piece of homework as once you have a basic grasp of how to write a formula, then we can start to get weird.

Here's a small set of Data to paste into Excel:

Now try clicking on an empty cell to the right of any of the numbers and typing in =SUM(. You'll notice that Excel is asking for a variety of variables(in this case, numbers) to find the sum of, and you'll notice that these are followed by commas (variables are almost always separated by a comma in Excel, you'll also notice that by clicking on any of the numbers, it adds the cell's Column Number and Row Number to the variables because even though it's a location, it's pointing to a number, and that's all Excel sees.
To take this a step further, instead of painstakingly writing commas and clicking on cells over and over again, you can instead click the first cell, and either hold the mouse button and drag down to the bottom (the newbie way), you can highlight the first cell, then hold shift, and click the last cell (which highlights all the cells in between your clicks), or, you can use this move:

1. Highlight the first cell,
2. Hold down shift and control at the same time, and press the down arrow key this will highlight all the cells in a column until you hit a blank one...
3. And yes, this works in any direction using the other arrow keys. You'd be very surprised how few people know of this trick that can save HOURS.

After using that super time-saving hotkey, you'll notice that instead of the location of the first cell and a comma separating each of the other cell locations, you'll find the address of the first cell, a colon, and the address of the last cell. This is shorthand for "include all the cells between these two locations and in our parlance, it's called an Array and you can often use an array instead of each variable in order to save time, and cover large areas with formula magic.

In summary, we've gone over the following:

• The definition of a formula in Excel
• How to write a formula
• The definition of a variable in a formula
• How to identify the amount of and type of variables that a particular formula requires
• A hotkey for highlighting a range of cells that will save you incalculable time over the rest of your career.

Next time, we'll finally use this knowledge to begin bending Excel to our whim to manipulate, transform, and make sense of data with the one-formula resume booster

Respectfully,

-Snacks