How to Create Fibonacci Sequence in Excel ( 2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Today we will discuss how to create a Fibonacci sequence in Excel. Numerous mathematical applications exist for the Fibonacci sequence. The Fibonacci sequence is also employed in computer algorithms and the determination of the golden ratio. And Fibonacci retracement is a popular analysis tool in trade. In this article, we will learn two quick and suitable ways to make the Fibonacci sequence in Excel effective with appropriate illustrations. Here is the overview of our today’s task.

fibonacci sequence in excel


Introduction to Fibonacci Sequence

Fibonacci sequence is a set of integers starting with 0 and 1. The next number in the sequence is found by adding the 2 numbers before it. So, our 1st and 2nd Fibonacci numbers are 0 and 1. So, our next number is 0+1=1. Similarly, the next number will be 1+1=2. In this way, we can easily calculate the following numbers in the sequence by summing the 2 previous numbers. So, the Fibonacci sequence is like 0, 1, 1, 2, 3, 5, 8….


How to Create Fibonacci Sequence in Excel: 2 Suitable Ways

We will easily create the Fibonacci sequence using the Mathematical formula, and VBA macros as well. So, let’s go through the steps one by one.

Method 1: Use Mathematical Formula to Create Fibonacci Sequence in Excel

This section will apply the Mathematical formula to create a Fibonacci sequence in Excel. Let’s follow the instructions below to create a Fibonacci sequence!

Steps:

  • First of all, insert 0 and 1 in cells B5 and B6 respectively.

insert 0 and 1 to create fibonacci sequence

  • After that, select cell B7 and write down the below formula in that cell. The formula is,
=B5+B6
  • Hence, simply press Enter on your keyboard. As a result, you will get 1 which is the third number of this sequence.

Apply mathematical formula to create Fibonacci Sequence

  • Further, AutoFill the Mathematical formula up to B14.
  • Finally, we will get our desired Fibonacci sequence.

Apply mathematical formula to create Fibonacci Sequence

  • Now, we will see our Fibonacci sequence with the formula.

Apply mathematical formula to create Fibonacci Sequence


Method 2: Run an Excel VBA Code to Create a Fibonacci Sequence in Excel

Now, we will create a User-Defined Function using the VBA Code to make a Fibonacci sequence in Excel. Let’s follow the instructions below to create a Fibonacci sequence!

Step 1:

  • Open a Module first; to do so, select the Developer tab and then click on,

Developer → Visual Basic

open visual basic window

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module 

insert module

Step 2:

  • Hence, the fibonacci_number module pops up. In the fibonacci_number module, write down the below VBA code.
Option Explicit
Private Function FibNum(nthFib As Double) As Double
 Dim x As Double
 Dim y As Double
 x = 1.618034 ^ nthFib - (1 - 1.618034) ^ nthFib
 y = x / Sqr(5)
 FibNum = Round(y)
End Function
Function FibonacciSeries(StartSr As Double, EndSr As Double) As Double()
Dim a As Double, b As Double, xy As Double
Dim xy() As Double
ReDim Preserve xy(EndSr - StartSr, 0)
xy = 0
For a = StartSr To EndSr
b = FibNum(a)
arr(xy, 0) = b
xy = 1 + xy
Next a
FibonacciSeries = arr
End Function
Sub Fibonacci_Number()
End Sub

			

Insert VBA code

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

execution of VBA code

  • After running the VBA Code, you will be able to create a user-defined function.
  • Now, go to the Excel sheet, select cell B5, and write down the below function.
=FibonacciSeries(0,9)
  • Then hit Now you will able to see the series of the first 10 numbers.

Return of the FibonacciSeries function


Things to Remember

👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously. You can open the Microsoft Visual Basic Applications window by using the ALT + L + V shortcut as well.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I’m hoping that all of the useful techniques for making a Fibonacci sequence after the last using VBA code will inspire you to use them to make your Excel spreadsheets more productive. If you have any questions or concerns, please feel free to remark in the comments section.


<< Go Back to | Excel for Math | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mizbahul Abedin
Mizbahul Abedin

Md Mizbahul Abedin, BSc, Textile Engineering and Management, Bangladesh University of Textiles, has been working with the ExcelDemy project for 11 months. Currently working as an Excel and VBA Content Developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He has published almost 20 articles in ExcelDemy. He has passions for learning new things about Microsoft Office Suite and Data analysis. Besides, he also likes to travel, photography, international politics, and read... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo