- Roll On Through: Create an Iterative List in Excel Without Any Real Coding
- Dynamic Tension! Creating and Using Dynamic Named Ranges in Excel
- Active-X Gonna Give it to Ya
- Your Invitation to the Excel Pivot Party
- Slicing Excel: The Way of The Knife
- Yes...If(And)): Excel’s Mighty “IF” Statement
- Getting Iffy With it: Conditional Statements in Excel
- Taking Out the Trash: Dirty Data in Excel
- Dealing with Dirty Data in Excel
- Doing the Excel Data Dance
- Adventures in Excel: Power to the Pivot
- The Art of Excel Pivot Tables
- The One Excel Formula to Rule Them All
- Excel Showdown: Index Match vs. The Lookups
- How I Stopped Worrying and Learned to Love the Formula
- Excel Data Manipulation: a Guide For Everyone
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 the utilization of 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 is 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:
- There are only maybe a handful of formulae worth using, and
- Its way quicker (as well as more impressive to the less informed) to write them in yourself.
Each formula is comprised of the following parts:
- The Command which is the phrase immediately after the "=" which is what tells Excel to do that thing.
- An open parenthesis immediately after the command
- One or more variables which is what 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).
- 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 really 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 both calculated with or on by almost anything else, you just 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 really weird.
Heres 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's 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:
- Highlight the first cell,
- 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...
- And yes, this totally works in any direction by using the other arrow keys. You'd be very surprised how few people know of this trick that can literally save HOURS of time.
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 individual variable in order to save time, and cover large areas with formula magic.
In summary, we've went 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 amounts of time over the rest of your long career.
Next time, we'll finally use this knowledge to begin bending Excel to our whim in order to manipulate, transform, and make sense of data with the one formula resume booster