How To Create 3d Graph In Excel

How to graph XYZ data in 3D inside Microsoft Excel

A couple years back we published a blog post about how to graph XYZ Data into MESH inside Excel, How to Graph XYZ Data into MESH Inside Microsoft Excel. Today we would like to take that post a little bit further and explain different options on how you can graph XYZ data in 3D inside Excel; with third party applications and by yourself.

First, please know that Microsoft Excel, while great as a spread sheet application, does not offer user friendly graphing options for anything more than an standard graphs. This isn't to say Excel cannot handle complicated graphs, Excel certainly can, but it is not easy by a long shot. However, there are several applications available that make this task extremely simple. Of course, we will be covering these products as well as the most cost-effective method, doing it yourself.

How to plot XYZ data in 3D – Line, Spline and Scatter

Method 1: Application – Cel Tools

Currently the easiest way to take XYZ data and produce a 3D graph with rotation and zooming is with the Excel Add-in, Cel Tools . Cel Tools is a toolbelt full of features that makes Excel easier for the normal user to operate. Functions like hiding and password protecting content, reversing list orders, emailing worksheets or workbooks, exporting pages, random selections, and many others. The function that we are interested in is with the graphing section of Cel Tools.

Under the graphing tab in cell tools you will find many options for graphing. What we will be focused on is in the section, '3D XYZ Tools'. This add-in makes it so easy to graph your data in 3D that it is as simple as: select, click '3D Line', '3D Spline' or '3D Scatter' and your graph is produced.

Cel Tools creates calculations based on the selected XYZ data, scroll bars to adjust X rotation, Y rotation, Zoom and Panning, and finally graphs it for you in a clean chart that you can adjust for your needs. Another great feature about this is the ability to send your graph to anyone. Because everything this is contained inside the Excel document you can email this document to anyone with Excel and they will be able to rotate, zoom and pan just like you can with Cel Tools.

Method 2: DIY – Use some impressive formulas!

This method is very complicated, and we will do a brief job explaining the complexities of this. Simply put, we really do not feel like recreating the wheel. George Lungu has done an amazing job explaining the mathematic equations behind 3D graphing inside of Microsoft Excel. He offers many tutorials on this and we highly suggest you visit his page if you have an interest in creating amazing graphs using Excel; Excel Unusual .

There are several different equations you need in order to graph XYZ data inside Excel. Excel can only graph two points of data at one time (X and Y). Therefor you need to convert XYZ into just XY. That is the tricky part, and the equation is broken down for you on the right:

With the equation(s) above you can graph XYZ values on a 2D graph in X and Y. The numbers needed are your original X, Y and Z values (OX, OY, OZ), Azimuth (X rotation) and Altitude (Y Rotation). Once your formulas are placed you adjust the rotations and watch as the graphed X and Y values are automatically updated.

Again, not as easy or clear cut as with Cel Tools, but it does work. And shout-out to George Lungu again for the formulas.

XYZ Data Converted to XY Data for 3D Graphing Calculations

Altitude = degrees of rotation from 1 to 360 ( Y )

Azimuth = degrees of rotation 1 to 360 ( X )

ALPHA = (3.1415926535/180)*Azimuth

BETA = (3.1415926535/180)*Altitude

OX = (Original Z Values)

OY = (Original Y Values)

OZ = (Original Z Values)

Xr = Sign (OY) * Sin (ATan ( OX / OY ) + ALPHA) * SQRT( OY^2 + OX^2) [<– this is what you are graphing]

Yr = Sign ( XXX ) * Cos (ATan (OZ / XXX ) + BETA) * SQRT ( XXX ^2 + OZ ^2) [<– this is what you are graphing]

XXX = Sign(OY)*COS(Atan(OX/OY)+ALPHA)*SQRT(OY^2+OX^2)

(equations above were modified from linked Excel Unusual URL XLS document download)

How to plot XYZ data in 3D – MESH, Surface Plot, Wireframe, Grid Frame

Now we are going back to our previous post on how to graph 3D MESH plots in Excel using XYZ data. If you missed that article you can visit it with the link(s) avaliable here, but I will be summarizing that post shortly.

Simply put, it is very easy to create a MESH drawing in 3D only using XYZ values. However, it is extremely tedious if you do not use the aid of a formatted calculation for placement and point averaging. As before I will be showing you two methods. First, a third party application, and second, a method you can do yourself.

Method 1: Application – XYZ Mesh

There are several methods to convert XYZ into a MESH layout that Excel can read, however there is only one program available that will convert this data into the correct layout and fill in empty data points. This method of filling in missing data is called curving. Curving is a very complex strand of calculations that average the variables over distances and creates an average curve in values depending on the distance. Basically, it makes a calculated guess as to what the numbers should be in that missing data point.

Why are the missing data points such a big issue? Microsoft Excel looks as these empty data points as data and not missing values. This means that instead of simply skipping these values (like most graphing engines would do), Excel counts them as zeros '0', and in return inputs false data into the graphed picture.

XYZ Mesh is currently the only software available that will take XYZ data and convert it directly into Excel's MESH format with data curving. Simply paste your values into the corresponding X, Y and Z columns, select your settings and click convert.

In no time the data is converted and displayed for you in a preview window, displaying a wireframe mesh of the converted data. From here the data can be exported into Excel. Fast and easy conversions with curving and none of the hassle associated with the next portion of this post….

Method 2: DIY – Placing values! And one formula…

As previously mentioned, Excel has a nasty habit of thinking that no values are the same as zero. While this might be true in some instances (where zero means nothing), as far as numbers are concerned, zero is an actual number. This oversite can cause data to be plotted incorrectly, which is why using XYZ Mesh is very important for creating smooth data.

But, this is the section where we teach you how to accomplish this yourself. First thing you need to realize is that XYZ data is plotted in three columns, X, Y and Z. In order for Excel to plot this into a 3D Surface graph the data must be in a MESH format. A MESH format is a structure that contains rows and columns, much like a spread sheet. X values are set stationary in the first column of every row, Y values are set stationary in the first row of every column and Z values are placed exactly where the X means the Y for the corresponding Z value. To get a better understanding of this, please see the image provided below:

As the image above illustrates, the X and Y values are easy enough to place, however the Z values will take time. The reason why is because you are going to need to track down and paste every single Z value in the correct place to make this MESH format graph properly. But wait, that was the easy part. Next comes the challenge. You need to calculate the differences between values to get a correct curving of the data. If you skip this part than Excel will assume your empty data points are zero and your graph will look like this (image to the right ->). Which, if you can't tell, is incorrect.

Calculation for curving data in MESH

There are several different ways to calculate differences between values and distances, but all of them have the same process.

Pick a calculation method you like; I personally prefer the method of VALUE = A [+/-] ((B – A) / C ).
Place the calculation into an empty cell.
Drag and drop until you reach the next value.

It might sound simple, but its very time consuming, and if you don't have your calculations perfect, the graph will be wrong.

Sadly, there is no other way of phrasing or copy and pasting these values by yourself unless you utilize the aid of a third-party application. If you do choose to use a third-party application to simplify the process please know that there are several options avalible. Some will convert XYZ to MESH and others will take MESH data and fill in empty data points. If you are going this route, why not use an application that will both? XYZ Mesh .

I hope that you have found this post useful. If you would like to talk to us about your feelings on this post please leave a comment down below. We love to hear from our readers!