The Maddening Problem
If you work with Microsoft Excel at your job (and who doesn’t?) without a firm understanding of Excel Formulas, you are likely wasting your time day in and day out repeating the same mind numbing procedures. You make a list by importing data, break it into categories, sum various columns, and file a report. Then you do this or some other routine over and over and over.
You’ve probably mastered the SUM function and perhaps even toyed with the IF function. Those are great Microsoft Excel functions, but they are just the tip of the iceberg, so to speak. I’m sure you feel that if you just knew the secret to Excel your work could be so much easier… and certainly more interesting!
Excel Formulas Are the Answer
First off, SUM is not a formula, it’s a function. Excel 2010 has about 250 built-in worksheet functions in its library. You can use any of these in any cell. When you use one (or more) of these native functions in a cell, you create an Excel formula. So while a Microsoft Excel function is not a formula by itself, when it is used, the result is an Excel formula.
All Excel formulas begin with an equals sign. The simplest of all formulas would be something like:
…typed into a cell. This simple formula does not use any function, so functions are not required to make a formula. But obviously you could just type the numeral “1” into the same cell. So what is the benefit of this formula? There is none with such a trivial example.
But that little equals sign opens the door to a universe of possibilities. The equals sign enables a calculated value, instead of a hard value in the cell, and with that the possibilities are only limited by your creativity. Literally millions of different formulas are possible.
A more interesting example would be to add the values in two disconnected cells:
= A10 + C10
And when you start including the built-in worksheet functions the results become very useful quickly:
= A10 / SUM(C2:C9)
Here the slash is the division operator, and the formula calculates a percentage.
With Excel formulas it is extremely efficient to add totals based on criteria. For example, to add all the sales by a particular salesperson from within a larger list of all sales, just make a formula that uses the SUMIF function.
In no time you will be crafting your own Excel "dedupe" formula. Let’s say you have a list of Customer ID’s in column A and you want to identify any duplicate entries. In the first appropriate cell of column B (probably B2), you could enter this formula:
= COUNTIF(A:A, A2)
Now any row where column B has a number greater than one is a duplicate.
With a little practice and imagination you can create Excel formulas for payroll, and countless other tasks. Interestingly, there seems to be Microsoft Excel functions included for just about any scenario.
When you dig into the subject of Excel formulas, you may find them addictive. And if so, rest assured that it is so vast that you will have an enjoyable hobby for years. This subject is deep but every little nugget mastered pays huge dividends in time and sanity saved!
Be forewarned that Excel will continually present you with challenging problems in your day to day work life. I thrill at solving these with Excel formulas. If you get hooked, you will too.
There are dozens of published books specifically focused on Excel formulas – that is how deep this subject is, but there are many excellent and free resources on the web as well.
While mastering all aspects of Excel Formulas would take years, mastering tiny bits is easy and quick and each bit has the potential to make mince meat out of your work!