How to graph XYZ data in 3D inside Microsoft Excel
How to graph XYZ data in 3D inside Microsoft Excel
Most people find this page by searching ‘XYZ Mesh’
CLICK HERE FOR XYZ MESH
This new post covers more methods on plotting XYZ values in 3D inside Excel and explains how to with and without third-party applications.
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.
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.
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.
GitHub XYZMesh
XYZMesh (the GitHub ‘program’) was cleverly named ‘XYZMesh’ two years after we published ‘XYZ Mesh’. Sadly, because they are technically different names, (and because it is not a licensed, paid for or company distributed product) not much can be done about the similarity of the product names. Simply put, the similarity ends there.
XYZMesh (the project posted on GitHub) is not an actual program. Well, it is, but it is not in a format that you might be familiar with. The program itself is nothing more than a search and find. It is also not in a format that is usable by Excel. If you were to download it, the application would be unusable in its current state. That is, unless you know how to read the program language and could convert it to an Excel VBA, at which point, it would be faster for you to write your own search and find subroutine without transcribing one from another language.
MatLab is an amazing application with wonderful graphics and complex formulas. The downside is that ‘Simple’ is not the first word that comes to mind when MatLab is mentioned. Many users that I have spoken with says the functionality is unbeatable, but the learning curve is daunting, with a price point to match. I have personally never tested it myself, but from what I have heard and read, MatLab is a very powerful application that can pretty much do anything you want. Of course, I am the one that developed the next application on the list, so I might have a little bit of bias.
XYZ Mesh was built to be simple. It requires no programing, no previous knowledge of formulas and can export your data directly into a format that anyone with a spreadsheet viewer can see. It does not have elaborate graphs, or mass functionality. It was built for one purpose, which is plotting X Y Z data in Microsoft Excel quickly with with little effort from the user. If you are wanting to work with Excel, and get results as fast as possible with minimal effort, XYZ Mesh is simply the best option.
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!
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!
Previous Post
Next Post
