This guide will explain how to create your own Fibonacci sequence in Microsoft Excel.
Table of Contents
The Fibonacci sequence is a mathematical sequence where each number is the sum of the two preceding numbers, usually starting from 0 and 1.
Numbers that are part of the Fibonacci sequence are known as Fibonacci numbers, commonly written as Fn. These numbers have a wide scope of applications due to the sequence appearing in biological settings, and the sequence itself has a unique array of mathematical properties.
In this guide, we will provide a step-by-step tutorial on how to create the Fibonacci sequence in Excel.
We will cover how to create the sequence starting from 0 and 1 using the addition operation. Afterward, we’ll go through a custom formula that calculates the nth Fibonacci number using Binet’s formula.
A Real Example of Creating a Fibonacci Sequence in Excel
Using Addition to Generate the Fibonacci Sequence
Let’s explore a simple method to recreate the Fibonacci sequence in Excel.
The Fibonacci sequence typically starts with the numbers 0 and 1. Formally, this is defined by the equations F0 = 0 and F1 = 1.
In the table above, we’ll write the values 0 and 1 in cells A1 and A2.
To create the third number in the sequence (F2), we’ll use the equation Fn=Fn-1 + Fn-2. This equation states that the nth Fibonacci number is the sum of the two preceding Fibonacci numbers.
In Excel, we’ll use the formula =A1+A2 to find the next number in the sequence.
As seen in the example above, we find out that F2 = 1. We can hold the fill handle in cell A3 and drag it down to find more numbers in the Fibonacci sequence.
Using Binet’s Formula to Find the Nth Fibonacci Number
In mathematics, Binet’s formula can be used to find the nth term of the Fibonacci sequence.
Assuming that the value of n is in cell A2, we can use the following Excel formula:
=ROUND((POWER((1+SQRT(5))/2, A2) - POWER((1-SQRT(5))/2, A2)) / SQRT(5), 0)
In the example above, we used the custom formula to determine that F11 is equal to 89.
Click on the link below to create your own copy of our examples.
Head to the next section to read our step-by-step tutorial on how to create the Fibonacci sequence in Excel.
How to Create Fibonacci Sequence in Excel
- Insert the values 0 and 1 into your spreadsheet. These values will serve as the starting point of our Fibonacci sequence (F0 and F1).
- Select the next empty cell and calculate the sum of the previous two Fibonacci numbers.
In our example, we’ll use the formula =A1+A2. - Evaluate the formula to return the next Fibonacci number in the sequence.
- Drag down the formula using the AutoFill feature. Excel will automatically update the cell references to ensure that the succeeding formulas will always add the last two numbers calculated in the sequence.
- To find the nth number in the Fibonacci sequence, we can use Binet’s formula.
In our example, we’ll use the custom formula =ROUND((POWER((1+SQRT(5))/2, A2) – POWER((1-SQRT(5))/2, A2)) / SQRT(5), 0).
Since the output of our formula uses the value in cell A2 as n, we can adjust the cell value of A2 to find different Fibonacci numbers.
These steps should be everything you need to know to create your own Fibonacci sequence in Excel.
FAQs
- Are there any Excel functions specific to Fibonacci sequences?
Excel does not have specific functions for Fibonacci sequences, but you can leverage existing mathematical functions to generate the Fibonacci sequence. - What formula can I use to find the nth Fibonacci number in Excel?
To find the nth Fibonacci number, you can use the formula =ROUND((POWER((1+SQRT(5))/2, A2) – POWER((1-SQRT(5))/2, A2)) / SQRT(5), 0). Replace the cell reference A2 with the cell reference in your sheet containing the value of n.
If you found this guide useful, you may also be interested in our guide on how to find the sum of the largest N numbers in a range.
That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!
