“What’s the difference between a Function and a Formula?”
These two words, “function” and “formula” are often thought of as meaning the same thing; in fact, they have very different meanings.
A FUNCTION is a tool that performs a specific task, like adding, multiplying, averaging, or counting.
A FORMULA is a statement that utilizes a function or combination of functions used to solve an overall problem.
Think of it this way…
A musical instrument, like a trumpet, tuba, or clarinet, each makes a specific type of sound that is unique to itself. No other instrument sounds more like a trumpet than a trumpet. However, a trumpet can’t make any other type of sound; it can’t sound like a snare drum.
When you have a collection of instruments all playing at the same time, you can produce sounds that no single instrument can produce. This is known as an orchestra. Each instrument plays its specific part in an overall grander song.
We can equate the instruments to functions and the song to a formula.
It is possible to have only one instrument play an entire song. This is known as a solo. We can write a formula that uses but a single function. Many of your formulas will indeed be “solos”.
= SUM( A1:A10 )More complex formulas will require the use of several functions to arrive at the result.
= SORT( UNIQUE( FILTER( A1:A100 ) ) )Speaking the Language of Excel Formulas
The first thing you must understand is that all formulas begin with (rather than end with) with an equals sign.
NOTE: If you happen to run across an Excel file where the author uses plus signs to begin their formulas, then you can almost certainly guess that they were born before 1970. This was a common practice in “The Old Days” when compatibility with Lotus was needed.
If we wanted to add the contents of two cells (C3 and C5), we can write the following formula:
= C3 + C5
You can use arithmetic operators but understand that some of the symbols may be different from what you are used to.
You can also perform parenthetical operations to control the Order of Operations.
= (A3 + A5) / (B2 - B10) * C3Dynamic Recalculation
One of the best things about referring to a cell as opposed to typing the exact numbers being processed is that the formula will automatically recalculate whenever anything in the logic chain changes.
Alter the contents of either cell C3 or C5 (or both) and you will receive an updated result.
Manipulating Many Values
Imagine trying to add up dozens, hundreds, or thousands of numbers spread across your spreadsheet. Pointing to each cell individually would become tedious and eventually impossible given enough values (Excel has a limit as to the number of characters in a single cell: 32,767 characters.)
Adding Numbers Using the SUM Function
The SUM function is the single most used function in the entire Excel function library. It has one job: add numbers together to get a total.
If you want to add dozens, hundreds, or thousands of numbers (i.e., cells) together, you don’t have to reference every number (cell), you can just reference the first cell in the series and the last cell in the series.
Assuming your numbers are listed together, your formula would look something like this:
=SUM( C3 : C10 )The colon character can be thought of like the word “through”.
“What do I get when I sum the contents of cells C3 through C10?”
NOTE: If you only wanted to add the values of two cells that are separated by other cells, you would write the formula as follows.
=SUM( C3, C10 )
The comma character can be thought of like the word “and”.
“What do I get when I sum the contents of cells C3 and C10?”
Because adding values is the MOST performed mathematical operation in Excel, the SUM function gets its own special button called “AutoSum”.
This feature can also be found on the right side of the Home ribbon.
Clicking the AutoSum button will create the formula and even attempt to determine the values needing to be summed.
Other very popular functions can be accessed by clicking the down arrow next to the AutoSum button.
“What if I don’t know the name of the function I need?”
Great question! This is a bit of a “chick or the egg” scenario. Luckily, we can get some help from Excel.
If you select the Formulas tab and click the Insert Function button…
… we can ask Excel what function would be best to perform our given task.
Other Popular Functions
You can perform other mathematical operations on data, like:
- Calculate the average of a set (AVERAGE)
- Count numbers in a range of numbers and words (COUNT)
- Count occupied cells (COUNTA)
- Find the smallest value in a set (MIN)
- Find the largest value in a set (MAX)
Below are examples of performing all the above-mentioned actions.
Creating More Complex Formulas
Suppose you wanted to determine the average of all salaries in the “Sales” department. This would require the use of a more sophisticated function: the AVERAGEIFS function.
The AVERAGEIFS function is like an AVERAGE function that you can apply a filter onto. It’s like saying, “Average all the salaries that belong to the “Sales” department.”
“I need help understanding a function!”
If you begin writing a formula that uses a function you are unfamiliar with and you need some guidance, click the “f of x” button to the left of the Formula Bar.
This will open the formula/function in a form that presents all the arguments with explanations.
As you click or tab through the various fields, an explanation of the selected field appears in the lower area of the dialog box.
If you require additional assistance, including examples of usage, you can click the “Help on this function” link in the lower-left of the dialog box.
“How do you get to Carnegie Hall?”
“Practice, practice, practice.” It’s an old joke, but it’s true. The more you work with Excel the easier it gets but that’s not to say that the challenges become easier.
You’ll find that the more you learn about what Excel can do, the more you’ll want it to do, the more you’ll want to challenge yourself to push its boundaries.
Excel is one of the most satisfying of all Office applications, but that satisfaction doesn’t arrive without time and effort.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
