Does writing an Excel formula seem like a daunting task to you? Do you cringe every time you hear the word “function”?
You’re certainly not alone. This is one of the most common problems among new Excel users – getting that formula out of your brain and onto your spreadsheet. There’s just something about all of those symbols and references that scares away anyone trying to lift the lid on them.
But the truth is, writing an Excel formula is much easier than you think!
In the post below, I propose an easy 5-step approach that you can use to create any formula you fancy! This is a post for anyone who knows what an Excel formula is and also has a vague idea of the formula you want to implement, but don’t quite know how to express yourself in “Excel formula language.”
What we’ll be doing is to write a single formula in one cell from start to finish. If you want to replicate that formula for several rows of data, I provide advice on doing that in Step 5.
So if you’re ready, let’s get started!
1. Map out the formula on a piece of paper
First thing’s first. You need to sit down and get your thoughts in order. Before you can understand how to program a solution to your problem, you first have to understand the solution in your head. So sit down for a while and think about what you want your formula to accomplish. What are the inputs you provide Excel with (i.e. the data)? What is the output you would like to receive? What are the steps you would need to follow to get from input to output on a calculator?
The easiest way to do this is to draw a flow diagram on a piece of paper. The idea here is to break the formula down into smaller, more understandable parts that sequentially follow after one another.
Use the following steps to draw your flow diagram:
- Start by writing down all your inputs below one another and draw a circle around each one of them. These are the data or information you want to be processed by your formula.
- Write down the output you want your formula to produce and draw another circle around it as well. This is the answer of your formula.
- Think about how you’ll get from the input to the output. What operation will you use (addition, subtraction, multiplication, division)? Write down the operation between the input and output and draw a box around it.
- Connect the circles accordingly using lines and arrows.
- Make sure you write down only one calculation per box (that is, one operation). If you need more than one calculation to get from the input to the output, write down intermediate answers and draw circles around them as well. These are stepping-stones to get from the input to the output.
I wish to calculate the total height of a rocket if the first stage is 5m high and the second stage is 7m high:
I wish to determine if the diameter of an apple in a packing line is greater than 12cm. If it is, display “Accept”. Otherwise, display “Reject”.
I wish to add 3 angles (30°, 25°, and 75°) to one another and find the cosine of the combined angle. This flow-chart has an intermediate step – a stepping stone to get to the final answer.
We’ll be using Example 3 in later steps to complete the process.
Now that you have a better mental understanding of what your formula will do, we can start looking for ways to program it.
2. Look for places where you can use a function to do the job
As you might have heard, functions are built-in shortcuts in Excel that perform a predetermined set of calculations. There are hundreds of these in Excel, and you can even create your own to re-use later.
Functions take a great deal of work out of your hands, sparing you the process of having to reason through unnecessary calculations. They can be great time-savers during the coding process as well. Imagine having to program the entire formula for determining the average of ten values, instead of just using the built-in AVERAGE() function:
So how do you find a suitable function?
I provide detailed instructions for finding the right function for the job in this post. Here’s a quick recap:
- Open the Formula tab and browse the drop down lists for the correct function.
- Click on the fx sign next to the formula bar. This will open a dialog box where you can search for functions to use.
- If you have a brief idea of the function’s name, you can click on a cell and start typing an equal sign, followed by the first letters of the function. Excel will show you a list of functions starting with those letters along with a tooltip explaining what each one does.
- Search Google for several keywords of what your function has to do. There are countless websites (including ApplyExcel.com) providing documentation and examples of each function.
Having found the right function for the job, you can now mark sections of your formula that the function takes care of. Find these areas in your flow-chart and draw a dotted line around them. Write the name of the particular function on the edge of the block so that you can remember it later.
For Example 3 above, this step might look like this:
As you can see, we’ll use the SUM() function to calculate the first part of our flow diagram and the COS() function to get to the final answer.
Analyse function inputs and outputs
List the inputs and outputs of each function you plan to use. When you write your formula later on, you will need to provide a value for each of the required inputs (as well as some of the optional ones), either in the form of a number/text or a reference to a cell that contains it.
For Example 3 above, the following inputs and outputs exist:
Input: A list of numbers to add up
Output: The sum of the numbers added up
Input: The size of an angle in radians
Output: The cosine of the given angle
We can see from the analysis above that the COS() function takes as input an angle size in radians (a unit for measuring angle sizes based on pi, π). Thus, we will need to convert the radians to degrees. Luckily, Excel has another function, RADIANS() that does exactly that.
So our analysis has proved to be fruitful, and we can now add the RADIANS() function to our flow diagram.
Alright. Research done! The hard part is behind us.
We are now ready to start typing our formula into Excel.
3. Type out your formula using the correct syntax
Once you’ve created a flow chart of your formula and determined which functions to use, you can start typing out your formula. Use your flow chart to guide you through the process. This part works in conjunction with step 4, so you might want to read that step as well before you start coding.
Formulas have a special syntax in which they are entered. Here are a few syntax rules you should know of.
- A formula always starts with an = (equal sign):
- When you make use of a function in your formula, it is always followed by a set of parenthesis (both opening and closing). These parenthesis can be filled with arguments, or left empty, depending on the function’s input requirements:
- Cell references are indicated by the letter of the column in which it occurs, followed by the number of the row in which it occurs (for instance, cell A1 is located in column A and row 1):
- A range of cells can be specified by typing the first and last cell references in the range with a comma in between them (for instance, the cell range spanning from cell A1 to cell A5 is expressed as A1:A5):
- Mathematical operators are used in a formula in the following way:
Operation Syntax What it means Addition =A1+A2 A2 added to A1 Subtraction =A1-A2 A2 subtracted by A1 Multiplication =A1*A2 A2 multiplied with A1 Division =A1/A2 A2 divided into A1 Power =A1^A2 A1 raised to the power A2
4. Start from the answer and work your way backwards
Depending on the complexity of your formula, it might contain smaller sections of simpler calculations. It might also contain a function within another function (called a set of nested functions).
Getting to a place where all of these calculations and functions work together in one formula can be difficult and confusing. It is hard to see which function should be contained within another. Each function also needs to have its own set of parenthesis, and placing all those parenthesis in the correct places can be quite challenging.
But if you make use of the flow chart that you’ve created in Step 1, it all becomes very easy. The best way is to start from the last step in your flow chart (your final answer). Type that function into the spreadsheet and place an “x” where the input should be entered. Then figure out which part of your formula acts as the input to the function you’ve just entered, and replace the “x” with that part. Keep doing this, moving backwards through your chart until you reach the start:
The last step in Example 3 is the COS() function with which we can find the cosine of the angle. We start by typing it into a cell (don’t press enter yet):
Next, we see that the input to our COS function is the angle size in radians. Thus, we add the RADIANS() function in the place of the “x”:
Next on the flow chart, we add up all three angles using the SUM() function. We can thus add the SUM() function inside the RADIANS() function:
Finally, we can add the reference to the range of cells containing the 3 angle measurements:
And finally, our formula is finished. Pressing enter will produce the result:
That’s it! Formula done!
5. Replicate your formula
You might wish to use your formula in other cells as well, such as other rows in your data list. This is done by copying the cell to different locations in the spreadsheet (before copying your formula to any different location, remember to consider relative vs. absolute referencing, which dictates how cell references in your formula change when the formula is moved).
To copy your formula to another location, there are a few options:
- Copy and paste on the formula bar
Select the cell containing your formula, and then click on the Copy button in the Home tab. Select the cell you wish to move move the formula to and click on the Paste button on the Home tab.
- Copy and paste using keyboard shortcuts
Select the cell containing your formula, and press Ctrl + C on the keyboard to copy it. Select the cell you wish to move the formula to and press Ctrl + V on the keyboard to paste it.
- Copy down by dragging
You can use this method if you want to copy the formula down to several cells below it. Select the cell and look for a small square in its lower right-hand corner. Click on this block and drag it down to the desired location. The formula is copied to every cell in between:
That wasn’t so bad, was it? Writing formulas can be much simpler if you follow these 5 simple steps. Just as a reminder, here they are again:
- Map out the formula on a piece of paper
- Look for places where you can use a function to do the job
- Type out your formula using the correct syntax
- Start from the answer and work your way backwards
- Replicate your formula
Are you struggling to write a specific formula? Tell us about it in the comments. We’ll be more than happy to help and guide you through it.