## 3d plotting in Excel

Ever wanted to make a 3-d Scatterplot and find that Excel just won’t do it?  I have.  There are add-ins you can use, that are of limited help in most cases.  There are also commercial add-ins you can buy.  I don’t know how useful these latter products are as I haven’t spent the money on them.

I’m going to show you how to do it here.  There is no magic bullet, no special menu trick.  You have to engage in a little 3-d programming.   But its not as hard as you might think (its not hard at all).  With a tat of diligence, a bit of focus, and some patience, you should be on your way after going through this post.

It suddenly appeared to me that because I have done a considerable amount of 3-d graphics programming in designing my research methods, that the same techniques should apply.  All a 3d image is is simply a projection of the 3d points onto a 2d plane.  So, when a series of XYZ points is entered into Excel, they can be projected into a simple XY series and plotted in a normal 2d scatter graph.   Simple?  Well, yes, it is.

The main step is projecting the 3d points into 2d space. Along the way we will want to do other things, like rotate and move the points.  The last thing is to make sure they are drawn from back to front.  I’ll cover each of these in this post.  You can get the spreadsheet to follow along here.  When you follow that link, google drive will show you the contents of the spreadsheet, which are far more disorganized than the spreadsheet itself. Look for the download link on that page (usually an arrow pointing down at the top of the page- move your mouse around to reveal) to save it.  It will not display correctly if you try to open it in google sheets.

Any graph will need Axes.  Since there are no 3d axes in Excel, we will have to make them.  What we will do is create a series of points that when connected will form an open box.  It will have a bottom and back sides, but no top or front.  In the Spreadsheet, the grid will look like the image below. Each set of XYZ coordinates defines a point, and connections will be made with lines between adjacent points when Excel does the scatterplot.  Some points are duplicated, as are some lines. That is necessary.  Sometimes when you draw from A to B, you have to re-draw from B to A to get from A to C.

It is a good idea to set up each cell as a function ( = +/- .5*\$b\$2 ) with the +/- signs arranged accordingly. That way, we can modify the width (10 in this case) to change the size of our grid on the fly.  Ok- go make your grid and come back.

Next, we must project  those points from 3d to 2d.  The easiest way to do this is simply to do what is called a perspective divide.  Divide each X by Z and each Y by Z.   The resulting XY pairs are your screen coordinates. These projected values are the numbers you will use in your graph series.  After the projection transform, the XY coordinates for your Axes should look like below. Now, go to your INSERT tab on the ribbon.  Insert a SCATTER graph and choose “Scatter with straight lines and markers.”

Now you should see a blank graph.  Right-Click the graph and “Select data”.  In the following menu, click “Add” under Legend Entries (Series).

In the dialog box that will appear, name the Series “Axes”, and select all the data under Screen coordinates X in the spreadsheet for X and all the data under Y in the spreadsheet for Y.  Your resulting Graph should look a lot like this one below. Kinda weird looking…   Its weird looking because it is trying to draw it around our head.  We did our perspective transform as if we were in the middle of that 3d universe.   So, what we need to do is add a “distance” variable.  At Cell D2, type the word “Distance” and put a “10” in Cell D3. We will use this distance to move our grid out away from us.  Over in our screen coordinates, where we divided each X and Y by Z, we are going to change the formula.  We will divide each X and Y by Z+Distance.  So change, for example, = A4/C4 to =A4/(C4+\$D\$3).  Paste that down through your Axes values.  Now your graph should start to look more sensible, like the one below. Above is our box, with no top and no front sides.  Lets clean it up and make it pretty.

Right click the plot area, on one of the horizontal grid lines and delete those.   Then, right click each axis and set the min and max values to –1 and 1, respectively.   Also change where each axis crosses the other to Axis Value –1.   On the Spreadsheet change Distance to 12.  Scale the graph up a bit (moving the width & height of the graph in excel) and play with the Width/Height until your axes look more or less of equal length on the screen.   Your graph should look something like this: Play with the Distance variable some to see its effect.  At this point, you should have a 3d set of axes.  You could start entering real data now as a new series.  Just divide each X and Y by Z+Distance to get what you would plot on the screen.   But, there is more that we will want to do.

First, pretty up your Graph.  Right click on one of the lines and choose “Format Data Series.”  Under Marker Options, choose a round point, about 8 points big.  Make it a nice color of your choice.  Do the same for Line Color- click “Solid Line” and pick your color.  Do the same for “Marker Line color”.  Now add some dazzle.  Click on “3-D format”.  Where it says Bevel, click on Top and click the first option under Bevel which is Circle.  Now you should have 3d-looking points for your Box corners. Ok- the last thing to do with our Axes is to be able to move our graph around to be able to look at the data from different perspectives.  This process is going to involve a little matrix math.  The easiest way to implement the math we need to do is to write some functions in Visual Basic for Applications (VBA).   Its not as hard as it sounds.  I’ll put the functions here, and you can open VBA and paste them in.

The first function we need is one to take three points, add a fourth (XYZW) and multiply them by a matrix

Function vmult(ByRef vector() As Double, matrix() As Double) As Boolean
Dim i As Integer
Dim j As Integer
Dim vals(1 To 4) As Double

vals(1) = vector(1) * matrix(1, 1) + vector(2) * matrix(2, 1) _
+ vector(3) * matrix(3, 1) + vector(4) * matrix(4, 1)
vals(2) = vector(1) * matrix(1, 2) + vector(2) * matrix(2, 2) _
+ vector(3) * matrix(3, 2) + vector(4) * matrix(4, 2)
vals(3) = vector(1) * matrix(1, 3) + vector(2) * matrix(2, 3) _
+ vector(3) * matrix(3, 3) + vector(4) * matrix(4, 3)
vals(4) = vector(1) * matrix(1, 4) + vector(2) * matrix(2, 4) _
+ vector(3) * matrix(3, 4) + vector(4) * matrix(4, 4)

vector(1) = vals(1)
vector(2) = vals(2)
vector(3) = vals(3)
vector(4) = vals(4)

vmult = True

End Function

Next, we need a function to create a rotation matrix.  This function will take an Angle in degrees by which you want to rotate the points as well as an Axis (“X” or “Y”) around which you wish to rotate.  I’m not going to include a “Z” option as with 3 axes we have 2 degrees of freedom.  Any orientation can be achieved by a combination of rotating around X and Y.  This function will be somewhat hidden.  You should never have to use it directly as it will be used in yet another function.

Function return_rotation(angle As Double, axis As String, ByRef result() As Double) As Boolean
Dim x As Double: Dim y As Double: Dim z As Double
Dim num As Double: Dim num1 As Double:
Dim num2 As Double: Dim num3 As Double:
Dim num4 As Double: Dim num5 As Double:
Dim num6 As Double: Dim num7 As Double:
Dim num8 As Double

axis = UCase(axis)

If axis = “X” Then
x = 1: y = 0: z = 0
Else
x = 0: y = 1: z = 0
End If

angle = angle / 57.2957795

num = Sin(angle): num2 = Cos(angle)
num3 = x ^ 2: num4 = y ^ 2:  num5 = z ^ 2:
num6 = x * y: num7 = x * z: num8 = y * z

result(1, 1) = num3 + num2 * (1 – num3)
result(1, 2) = num6 – num2 * num6 + num * z
result(1, 3) = num7 – num2 * num7 – num * y
result(1, 4) = 0
result(2, 1) = num6 – num2 * num6 – num * z
result(2, 2) = num4 + num2 * (1 – num4)
result(2, 3) = num8 – num2 * num8 + num * x
result(2, 4) = 0
result(3, 1) = num7 – num2 * num7 + num * y
result(3, 2) = num8 – num2 * num8 – num * x
result(3, 3) = num5 + num2 * (1 – num5)
result(3, 4) = 0
result(4, 1) = 0
result(4, 2) = 0
result(4, 3) = 0
result(4, 4) = 1

return_rotation = True

End Function

Ok- we have functions to create a rotation matrix and multiply our points by that matrix.  Now we need a function that we will call from the spreadsheet cells to put them together and do the rotation.

Function rotate(x As Double, y As Double, z As Double, xrot As Double, yrot As Double) As Double()

Dim rotate_x(1 To 4, 1 To 4) As Double
Dim rotate_y(1 To 4, 1 To 4) As Double
Dim vector(1 To 4) As Double
Dim retval(1 To 3) As Double

vector(1) = x: vector(2) = y: vector(3) = z: vector(4) = 1
ok = return_rotation(xrot, “X”, rotate_x)
ok = return_rotation(yrot, “Y”, rotate_y)

ok = vmult(vector, rotate_x)
ok = vmult(vector, rotate_y)

retval(1) = vector(1)
retval(2) = vector(2)
retval(3) = vector(3)

rotate = retval

End Function

When we want to rotate our points, we call =Rotate(xcell,ycell,zcell, x-axis rotation, y axis rotation) and it will return an array of 3 points- our original points rotated by X axis rotation around the X axis and Y axis rotation around the y axis.

To use these functions, we have to get them into Visual Basic for Applications.  That isn’t hard.  Open Excel.  Do you see a Tab that says “Developer” ?  If so- good.  If not- you have to change your Excel Options to show the Developer tab.

If you don’t have a Developer tab. Click on the Office Button… and choose Excel Options. Choose “Popular” and click Show Developer Tab in the Ribbon. Now, click the developer tab. And on that tab click Visual Basic. You should have just opened the Visual Basic for Applications Editor.  On the left you should see the “Project Explorer” window.  If not, you can have it displayed under the “View” menu.  Once displayed, look for “VBAproject(Book1) as circled in Red below. Right-Click that text and select “Insert” and then “Module.” A new module should now appear, with a blank window to the right.  There may be text at the top of the window (e.g., “Option Explicit”).  Whether there is text or not, delete it and make sure the first line reads “Option Base 1”. Below that line “Option Base 1” is where you will paste the functions from here.  Just copy & paste.

You might want to save your spreadsheet at this point.  It must be saved as a “Macro enabled” spreadsheet (xlsm), otherwise the VBA code won’t be saved and available.

Go back to your sheet, and click on Column F and then on the Home tab go to “Insert” and Insert 3 columns.  We’re going to need a bit more space.

To make this more fun- lets get a little fancy.  Lets set up a couple of scroll bars to use to update our X and Y axis rotation values.  Go to cell D4 and type X rotation.  In Cell E4 type Y rotation.   Put a zero in each cell below them.

Now go to the Developer Tab, click on “Insert” and under “Form Control” choose the scrollbar. Your cursor will change to a “+”.  Left click and hold to draw the scroll bar in Column D under the X rotation cells.  Repeat these steps to put a scroll bar under the Y rotation cells.

Right click the bar under X rotation and choose “Format Control.”  On the “Control” tab, set current value to 0, Minimum Value to 0, Maximum value to 360, Incremental change to 1, and page change to 5.  Set Cell Link to the cell containing the X rotation. Repeat that for the other scroll bar and the Y axis.  Now we have these bars linked to these cells so that updating the bar will change the cell values.  We will use these cell values in our rotate functions.

We’re almost there-  Go to G2 and type “Rotated Values” and then on G3-H3, put X, Y, and Z.

Now, select cells G4 to I4.  Type =Rotate(a4,b4,c4,d\$5,e\$5) and DO NOT PRESS ENTER.  Press SHIFT+CNTRL+ENTER.   This formula returns an array of 3 points.  The special way of entering the formula tells excel to expect more than one result and to put it them in the cells you’ve high lighted.

What you should see is a copy of cells A4-C4 (assuming X rotation and Y rotation are both Zero).   Highlight G4-H4 and paste them down to row 15 to apply the rotation to every point in our grid.

We can now rotate our original points.  So we need to put the rotated points onto the screen rather than the original points.  Go back to the formula for our Screen Coordinates.  Modify the values to take the Rotated Values of X, Y, and Z rather than the original ones.  For example change =A4/(C4+\$d\$3) to = g4/(I4+\$d\$3).  Modify X and Y, then paste the new formulas down the line.

Now start playing with your scroll bars and watch the magic in your axes.

Lets create some interesting series to put in to complete the example.

The data we are going to fill in have nothing to do with the steps involved in making a 3d plot.  I’m just generating some cool data to plot.

First, set your grid to a width of 20.  Set your distance to 25.

At A17 I’m going to begin a new series of data.  I’ll call it GreenStuff. I’ll put the Labels Factor, X, Y, and Z in cells A18 – D18.

Set A19 to –10.  Set A20 to =A19+20/50 and copy that down to A69.

In Cells B19-D19, put the formula “=\$A19+NORMINV(RAND(),0,1)”  and then paste it down to row 69.  These are our raw data.  Once you have the pasting done, select all those data and paste-special them back into the spreadsheet where they are.  The special paste operation will be “Values.”  Otherwise, every time we do something to the sheet, the formula will update and the pattern will change.

Now rotate them. Hilight cells G19-I19 and SHIFT-CNTRL-ENTER =Rotate(b19,c19,d19,\$D\$5,\$E\$6) into those cells.  Paste that down to row 69.

Do the perspective transform. Copy cells M15 and N15 (the last row of your Screen Coordinates) and paste them in the same columns between rows 19 and 69.

Ok- right click your graph. Select series, “add”, select “Greenstuff” for the name, and the new X values (M19..M69) for X, and the new Y screen values for Y.

You should have an ugly graph now! Click on the new data points and format the series.  Remove the connecting lines (line = none), and make your symbols something interesting, like green 3d formatted circles.

Copy all the new stuff you just made, from lines 17 – 69, and paste them beginning at line 72.  Change the name “Greenstuff” to Redstuff.

In Cells B74 put the formula “=-\$A74+NORMINV(RAND(),0,1)”  Leave the other cells alone.  Copy and paste that cell down to row 124.  Once done, copy the results and paste them back into the same place using paste special –> values.

Now, enter the new screen coordinates as a new series.  Once in, format it to be red circles.  Play with your rotations.   Here is what mine looked like. You should observe two things now.  First, small Z values are close to you initially.  That is, –Z points towards the viewer.  Second, as you rotate, the points don’t necessarily obscure each other as they should.  That is because Excel draws the series in order, from first values to last, and then from Series 1 to Series N.  Later drawn points will always be on top of earlier drawn points regardless of where they are supposed to be in space.

That can be corrected by sorting the points in Z order.  There are ways to do this on the fly, but I’m not going to get into it here, as even that won’t solve the series order problem.  But for a single series, sorting the points by Z improves things.  If you’re determined to do it, look here.

In another spreadsheet you can download, I’ve implemented the sorting.  It does make a difference within a single series.  It also creates a neat effect to add some transparency to the points.

What if your points are out of the range of the graph?  Modify your axes width and distance.   Or, scale your points down.

In short, create a set of XYZ points to make some form of Axes.  Make that your first series.

Enter your XYZ data for each subsequent series.

Use the Rotate function to create rotated points.

Apply the perspective division with the distance added to convert each XYZ triplet to an XY set.

Sort by Z, large to small, if you want.

Create a 2d scatterplot.  Select your series as appropriate based on their XY screen coordinates.  Format your series to your liking.

That’s about it.  You should be able to take it from here.

This entry was posted in Programming in general, Statistics. Bookmark the permalink.