Plot a Fibonacci Spiral in Excel
How To Plot A Fibonacci Golden Spiral Using An Excel Spreadsheet
How To Graph The Fibonacci Spiral
How To Draw A Fibonacci Spiral
Golden Ratio: An Excel Spreadsheet Tutorial
This tutorial illustrates the correct way to:
- generate the Fibonacci Sequence of numbers
- calculate the Golden Ratio, Phi (φ) to more than 15 significant places
- plot the sunflower seed pattern
- plot the Fibonacci Golden Spiral
Reasonable knowledge of spreadsheets and an interest in the Fibonacci sequence of numbers and the Golden Ratio, Phi (φ)
Download The Excel Spreadsheet
A completed Excel spreadsheet containing the Fibonacci sequence, sunflower seed pattern and Golden Spiral can be downloaded by clicking here. The spreadsheet is annotated to make the calculations understandable.
If you would prefer to generate your own and learn by doing, then follow the instructions below.
1. Calculate the Fibonacci Sequence of Numbers in Excel
The Fibonacci Sequence of numbers is defined as:
f(n) = f(n-1) + f(n-2)
which means that the next Fibonacci number in the sequence is the sum of the previous two.
Open up a new Worksheet.
Define two columns; one labelled n (index) and the other entitled Fibonacci Sequence.
The first two rows of numbers need to be entered manually to prime the sequence, so enter the first two index numbers: 0 and 1 and the Fibonacci numbers: 1 and 1
Your Worksheet should look like something like this:
Click on cell B5 and enter this formula with whatever method you prefer (typing directly or using cell selection with the mouse)
Now click on cell A5 and type the number
Click on cell A5 and drag the mouse to B5 to highlight that data.
Drag the bottom right hand corner square down to copy the highlighted cells.
As you drag, a number in a grey square box will be shown next to your mouse. This is the index number you have reached during the copy operation. Drag all the way down until that index number reaches 100.
And voila! You have the beginning of the Fibonacci Sequence. Drag and copy the cells down further for more of this infinite sequence.
2. Calculating the Golden Ratio, Phi (φ)
It can be shown that the Golden Ratio, Phi (φ) can be calculated with this formula:
In the same worksheet that you calculated the Fibonacci Sequence, enter the Golden Ratio formula into a cell:
The value of Phi (φ) will be calculated and displayed. You can expand the number of decimal places by right-clicking the cell > Format Cells… > Number and then increase the number of decimal places. Click OK when finished.
Alternatively, in the Home tab, look for the number group and press the button to increase decimal precision.
To 15 significant places, Phi (φ) is shown as:
NOTE: The decimal expansion of Phi goes on forever, without a repeating pattern ever developing. This is because root 5 is used in it’s calculation and the square root of 5 is an irrational number.
However, Excel will not show decimal precision beyond 15 significant digits. Numbers are subject to truncation and are not rounded. The highlighted red zeros are not part of the decimal expansion of Phi. This limitation on decimal precision is a consequence of the micro processor in your computer, which sacrifices precision for speed.
How To Increase Decimal Precision in an Excel Spreadsheet
If you want to calculate Phi to more than 15 significant places, it can be done with a spreadsheet Add-In. Visit xlPrecision to download the free edition software and read the documentation.
With the xlPrecision Add-In installed, Phi can be calculated with this formula:
To 100 significant places, Phi (φ) is displayed as:
3. Ratio of Adjacent Terms in the Fibonacci Sequence
The ratio of adjacent terms in the Fibonacci Sequence is an approximation of the Golden Ratio, Phi (φ). Take a pair of adjacent Fibonacci numbers and divide the larger number by the smaller one and you will get a number that is very close to Phi (φ). And the further down the Fibonacci Sequence you go, that approximation becomes asymptotically closer to Phi (φ).
In a column next to the Fibonacci Sequence of numbers, enter Ratio of Adjacent Terms as a heading.
Click on cell C4 and enter the formula:
Then copy that cell down until you reach the end of your Fibonacci Sequence on your spreadsheet.
It can be seen that the ratio of adjacent terms in the Fibonacci Sequence converge on Phi (φ) very quickly. The two values are accurate to fourteen decimal places by the 38th pair of Fibonacci numbers.
A XY scatter plot of the ratio of adjacent terms and the index demonstrate this rapid convergence.
4. Plot the Sunflower Seed Pattern in Excel
Start on a fresh, blank Worksheet by clicking one of the Worksheet tabs at the bottom left of the Excel window. It’s a good idea to start renaming your tabs at this point too. Right click and select Rename to change the names of your tabs.
As described in Section 2, calculate the Golden Ratio (φ) with the given formula in cell B3.
Calculation of the Golden Angle
The Golden Angle is the smaller of the two angles when the Golden Section is formed into a circle.
The Golden Angle: The ratio of the whole circumference to the length of the larger arc is equal to the ratio of the larger arc to the smaller arc.
In a cell beneath your value of Phi (φ) enter this formula to calculate the Golden Angle:
Label your values in A3 and B3 accordingly.
Click on cell B4 (the value of the Golden Angle) and then write the word angle in the name box to give that value a useable name that we can reference in the next set of calculations.
Now, create a series of headings as shown below:
Where index starts at 1 and increments by 1.
Distance from centre is calculated by taking the square root of the index number. So the formula in cell E4 is
Theta is the angle at which a new seed will be generated after the preceeding one. It is simply a multiple of the Golden Angle since it starts at zero and increments by the value of the Golden Angle.
In cell F4, type a zero. In cell F5, enter the formula:
The x,y coordinates are now easy to calculate. In cell G4, type:
and in cell H4, type:
Copy all the items by dragging down to make 1,000 rows.
Create a XY scatter plot of the x and y values to generate your model of sunflower seed arrangement.
Unfortunately, Excel doesn’t realize that you’re attempting to model a sunflower and presents you with a distorted, flattened view of your representation. Re-size the graph and at the same time correct the aspect ratio to make it more square.
This tutorial concludes with a method to make Excel graphs square. A method which is required to produce an accurate interpretation of the Fibonacci Spiral and can correct the aspect ratio in this graph too.
Experiment with the value of Phi (φ). Change it a little or change it a lot to see what happens to the pattern. It becomes immediately clear that Phi (φ) is the only number that packs the seeds into the sunflower’s head with the most efficiency.
5. Plot the Fibonacci Golden Spiral in Excel
The Fibonacci Golden Spiral can be approximated very closely by a logarithmic spiral that grows away from it’s origin with a growth factor of φ with every quarter turn.
The formulae for a Golden Spiral are given as:
x = a.cosθ.ebθ y = a.sinθ.ebθ
where a is an arbitrary scaling factor and b is the growth factor of the spiral. For the Golden Spiral:
b = ln(φ)/90 = 0.00534679805621781608330843237138
Begin in a blank Worksheet called Spiral.
Then enter the following text, values and formulae.
The column entitled theta simply starts at zero and increments by 1.
Taking note of my cell references, the formula for E15 is
Ensure you replicate the ABSOLUTE cell references denoted with the dollar sign ($).
Copy the theta, x and y cells down until theta reaches a value of 2173.
Plot the x, y data as a XY scatter plot with lines. Excel will present something like this. A flattened Golden Spiral. The aspect ratio will be all wrong:
Now move on to the next step to get the aspect ratio of this graph correct.
6. How to Make Graphs Square in Excel
Click the Excel Spreadsheet Option Button in the top left of the application window and select Excel Options.
Tick Show Developer Tab in the Ribbon if it is not ticked already.
Click OK to exit the options menu.
Click on the Developer tab in Excel.
Click Macros and name the macro. Any name will do. Then click Create.
In the Visual Basic window that appears, highlight the default code and press delete on your keyboard so that nothing remains.
Open this text file and paste the contents of it into the code window.
The macro window will look like this with the code has been copied across.
Close the Visual Basic Editor.
With the macro in place, now comes the easy bit:-
- Delete the Series1 legend on your graph.
- Resize your graph to occupy a large proportion of your monitor.
- Ensure that your graph is selected and active.
- Click Macros in the Developer tab.
- Ensure that MakePlotGridSquareOfActiveChart is selected, then click Run. The aspect ratio of the selected graph will be corrected.
With the correct aspect ratio, your Golden Spiral should look something like this:
Now it’s time to add the Fibonacci squares to the plot.
Click on the Insert tab and select Shapes. Choose a rectangle.
Using care, draw in the Golden Rectangle so it fits the Spiral, snugly.
Right click the rectangle and select Format Shape… to increase Transparency with the slider so that the Spiral can be seen.
When you are happy with the fit, choose another rectangle, hold down the shift key and draw in the first square. Take care to ensure all your lines are aligned. Use the View tab and Zoom feature for more accuracy if you need it.
Alter the color and transparency of your new square.
Continue to draw in the smaller squares. Remember to hold down the shift key before you start drawing any of the squares. This will prevent you from drawing rectangles.
Ultimately, your Golden Spiral, Golden Rectangle and Fibonacci Squares will look something like this when you are done.
If you’ve enjoyed this tutorial, please take a look at another article of mine based upon the misconceptions and use of the Golden Ratio in photography. I’m collecting data there to determine if the Golden Rectangle is indeed, the one most desired over any other rectangle.