How to Create Fibonacci Sequence in Excel

This guide will explain how to create your own Fibonacci sequence in Microsoft Excel.

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.

start with first two fibonacci numbers

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-2This 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.

add previous two fibonacci numbers to create next in 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.

drag formula down to create fibonacci sequence in Excel

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.

Binet's formula

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)
use Binet's formula to find the nth fibonacci number

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

  1. Insert the values 0 and 1 into your spreadsheet. These values will serve as the starting point of our Fibonacci sequence (F0 and F1).
    insert values 0 and 1 in the spreadsheet
  2. Select the next empty cell and calculate the sum of the previous two Fibonacci numbers.
    calculate the sum of the previous two fibonacci numbersIn our example, we’ll use the formula =A1+A2.
  3. Evaluate the formula to return the next Fibonacci number in the sequence.
    evaluate the sum
  4. 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.
    drag formula down to create fibonacci sequence in excel
  5. To find the nth number in the Fibonacci sequence, we can use Binet’s formula.
    type binet's formula to create fibonacci sequence in excelIn 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.
    formula updates when changing the input

These steps should be everything you need to know to create your own Fibonacci sequence in Excel.

FAQs

  1. 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.

  2. 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! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like