How to Make FOR Loop in Excel Using Formula (3 Examples)

Don’t you want to use Excel VBA and want to make a FOR Loop in Excel using Formula? In this article, I’ve shown how you can make FOR Loop using formulas.

If you know how to code with Excel VBA, you’re blessed 🙂. But, if you never wrote code in VBA or want to keep your Excel workbook free of Excel VBA code, then most of the time you have to think out of the box to create a simple loop.

Download Working File

Download the working file from the link below:

3 Examples to Make FOR Loop in Excel Using Formula

Here, I will demonstrate 3 examples to make FOR Loop in Excel using a formula. Let’s see the detailed examples.

1. Applying Combined Functions to Make FOR Loop in Excel

Now, let me know the background that is encouraging me to write this example.

I am the author of some courses on Udemy. One of the courses is on Excel Conditional Formatting. The course title is: Learn Excel Conditional Formatting with 7 Practical Problems. [to get free access to this course, click here].

In the course discussion board, a student asked me a question as below [screenshot image].

Question asked by a student in Udemy.

Read the above question carefully and try to solve it…

Steps to Solve the Above Problem:

Here, I will use OR, OFFSET, MAX, MIN, and ROW functions as Excel Formula to create a FOR Loop.

• Firstly, your job is to open a new workbook and input the above values one by one into the worksheet [start from cell C5].
• Secondly, select the whole range [from cell C5:C34].
• Thirdly, from the Home ribbon >> click on the Conditional Formatting command.
• Finally, select the New Rule option from the drop-down.

At this time, New Formatting Rule dialog box appears.

• Now, in the Select a Rule Type window >> select Use a formula to determine which cells to format option.
• Then, in the Format values where this formula is true field, type this formula:

• Now, select the appropriate format type by clicking on the Format… button in the dialog box.

At this time, a dialog box named Format Cells will appear.

• Now, from the Fill option >> you have to choose any of the colors. Here, I selected the Light Blue background. Also, you can see the sample instantly. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.
• Then, you must press OK to apply the formation.

• After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.

Lastly, you will get the formatted numbers.

Let me show you the algorithm to solve the above problem:

• Here, to make you understand the algorithm easily, I will explain the whole thing with two reference cells: cells C11 and C17. In cells C11 and C17, the values are 10 and 20 respectively (above image). If you are used to Excel formulas, then you can smell the OFFSET function, as OFFSET function works with reference points.
• Now, imagine I am taking the values of cell ranges C8:C11 & C11:C14, and C14:C17 & C17: C20 side by side [image below]. Reference cells are C11 and C17 and I am taking a total of 7 cells around the reference cell. You will get an imaginary picture like the following. From the first part, you can find a pattern from the image. C9–C12=3, C10-C13=3, there is a pattern. But for the second part, there is no such pattern.

• So, let’s build the algorithm with keeping the above pattern in mind. Before building the common formula, I shall show what the formulas will be for the cells C11 and C17 and then will modify the formula to make it common for all. For a reference point (like C11 or C17), I shall take a total of 7 cells around it (including the reference point) and place them side by side in the formula creating arrays. Then I shall find out the difference of the arrays if any of the differences is equal to 3 that the reference cell will be TRUE valued.
• Here, I can do that easily using the OFFSET function as the OFFSET function returns an array. Say for cell reference C11, I can write the formula like this: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3). What will this formula return? The first offset function of the formula will return array: {10; 11; 12; 15}, second offset function will return array {5; 8; 9; 10}. And you know {10; 11; 12; 15} – {5; 8; 9; 10} = {10-5; 11-8; 12-9; 15-10} = {5; 3; 3; 5}. When this array is logically tested with =3 then Excel calculates internally like this: {5=3; 3=3; 3=3; 5=3} = {False; True; True; False}. When the OR function is applied on this array: OR({False; True; False; True}, you get TRUE. So cell C11 gets true values as returned.
• So, I think you have got the whole concept of how this algorithm is going to work. Now there is a problem. This formula can work from cell C8, above cell C8, there are 3 cells. But for cells C5, C6, and C7 this formula cannot work. So the formula should be modified for these cells.
• Now, for cells C5 to C7, we want that the formula will not take into consideration the upper 3 cells. For example, for cell C6, our formula will not be like the formula for cell C11: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3).
• Here, for cell C5, the formula will be like: OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3).
• Then, for cell C6, the formula will be like: OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3).
• After that, for cell C7, the formula will be like: OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3).
• Again, for cell C8, the formula will be like: OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3); [this is the general formula].
• Then, for cell C9, the formula will be like: OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3); [this is the general formula].
• Finally, do you find some patterns from the above formulas? The first OFFSET function’s rows argument has decreased from 3 to 0; the height argument has increased from 1 to 4. The second OFFSET function’s rows argument has decreased from 0 to -3 and height argument has increased from 1 to 4.
• Firstly, the first OFFSET function’s rows argument will be modified like this: MAX(ROW(C$5)-ROW(C5)+3,0)
• Secondly, the second OFFSET function’s rows argument will be modified like this: MAX(ROW(C$5)-ROW(C5),-3) 
• Thirdly, the First OFFSET function’s height argument will be modified like this: MIN(ROW(C5)-ROW(C$5)+1,4)
• Fourthly, the second OFFSET function’s height argument will be modified like this: MIN(ROW(C5)-ROW(C$5)+1,4)
• Now, try to understand the above modification. These are not that tough to understand. All these four modifications are working as FOR LOOP of Excel VBA but I’ve built them with Excel Formulas.
• So, you got the ways how the general formula works for the cells from C5:C34.

So I was talking about Looping in Excel spreadsheets. So, this is a perfect example of looping in Excel. Here, every time the formula takes 7 cells and works on the cells to find out a specific value.

2. Use of IF & OR Functions to Create FOR Loop in Excel

In this example, suppose you want to check if the cells contain any values or not. Furthermore, with Excel VBA FOR Loop, you can do this easily but here, I will do that using an Excel formula.

Now, you can use the IF, and the OR functions as Excel Formula to create FOR Loop. Furthermore, you can modify this formula according to your preference. The steps are given below.

Steps:

• Firstly, you have to select a different cell E5 where you want to see the Status.
• Secondly, you should use the corresponding formula in the E5 cell.

• Subsequently, press ENTER to get the result.

Formula Breakdown

Here, the OR function will return TRUE if any of the given logic becomes TRUE.

• Firstly, B5=”” is the 1st logic, which will check whether the cell B5 contains any value or not.
• Secondly, C5=”” is the 2nd logic, which will check whether the cell C5 contains any value or not.
• Thirdly, D5=”” is the 3rd logic. Similarly, which will check whether the cell D5 contains any value or not.

Now, the IF function returns the result which will fulfill a given condition.

• When the OR function gives TRUE then you will get “Info Missing” as Status. Otherwise, you will get “Done” as the Status.

• After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E13. Or you can double-click on the Fill Handle icon.

Finally, you will get all the results.

3. Employing SUMIFS Function to Create FOR Loop in Excel

Suppose, you want to make the total bill for a certain person. In that case, you may use the FOR Loop using the Excel formula. Here, I will use the SUMIFS function to create the FOR Loop in Excel. The steps are given below.

Steps:

• Firstly, you have to select a different cell F7 where you want to see the Status.
• Secondly, you should use the corresponding formula in the F7 cell.

• Subsequently, press ENTER to get the result.

Formula Breakdown

• Here, $C$5:$C$13 is the data range from which the SUMIFS function will do the summation.
• Then, $B$5:$B$13 is the data range from where the SUMIFS function will check the given criteria
• Lastly, E7 is the criteria.
• So, the SUMIFS function will add the payments for the E7 cell value.

• After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F8:F10.

Finally, you will get the result.

Conclusion

We hope you found this article helpful. Here, we have explained 3 suitable examples to make FOR Loop in Excel using formulas. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Read More

• How to Use the Do While Loop in Excel VBA
• For Next Loop in VBA Excel (How to Step and Exit Loop)