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 standard line and bar graphs. This isn’t to say Excel cannot handle complicated graphs, Excel certainly can, but it is not an easy endeavor. However, there are several applications available that make this task extremely simple. Of course, I will be covering these products as well as the most cost-effective method, doing it yourself.

How to plot XYZ data in 3D – Surface, WireFrame and MESH

(Surface) Method 1: 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 at 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 reads 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.

Of course, there are many customization options inside of XYZ Mesh; color variation, decimal replacements, user preferences, etc.  But, for  99% of users, the default settings are perfect! 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, surface graph, heat chart, or many other graphing options. 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….

(Surface) 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.

This is the section where we teach you how to accomplish the curving 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.

1. Pick a calculation method you like; I personally prefer the method of VALUE = A [+/-] ((B – A) / C).
2. Place the calculation into an empty cell.
3. 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.

Method 3: Other third-party applications

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 very few options available. From our research, there are a total of three applications that can do this for you. A free ‘program’ published on GitHub, MatLab and XYZ Mesh. But, each one has its draw backs.

How to plot XYZ data in 3D – Scatter Plot and 3D Lines in Excel

(Scatter) Method 1: XYZ Mesh

Yes, XYZ Mesh again, but, when you dedicate over 10 years to make an application that does one thing, it does that one thing pretty good!

XYZ Mesh was originally made to take X Y Z data and convert it into a MESH format compatible with Excel. After we figured that hurtle, we moved to plotting X Y Z scatter plots in 3D.

XYZ Mesh makes plotting 3D scatter plots in Excel easy. Simply add in your X Y Z values into XYZ Mesh and click ‘Excel 3D Export’. In this new window select ‘3D Line’ or ‘3D Scatter’, and then ‘Export to Excel’. That’s it! Once loaded you will see a new Excel document with your 3D plot; rotation, zoom and pan bars included!

Something to know about XYZ Mesh’s scatter plots is that it is really good with data that is close together and now wide spread. What I mean, if you have a set of 20, 30 or 40 data points close together, then it plots great! If you are plotting, say, Lat. Long. and Alt. data, not so much. You see, Excel was not created to plot things in 3D. When you are creating 3D data in Excel, which we outline in the next section, you need to convert the data into 2D data. When you are dealing with wider areas like geographical data, you really should be using surface graphs.

(Scatter) 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.

And if you would like to support George on YouTube, follow his YouTube channel where he teaches you step by step how to make his amazing graphs!

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)

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!