Back to Advanced Charts

Excel scatter plots are limited to showing points in two dimensions only. However, the QuickChart 3D Scatter Chart projects points to appear in 3D for purely visualisation purposes. Unfortunately, it is not possible to retain meaningful values of the points – it can only be a visualisation of how points are distributed relative to each other. While not as fully featured as in some programs, examining data in this way can be enlightening as to the distribution of points and clusters within the three-dimensional space.

Simple 3D Scatter Chart

If you have a dataset with three coordinates (X,Y,Z) then highlighting the data and pressing the 3D Scatter button on the ribbon will plot that data in 3D according to the default projection parameters (see Settings).

Creating a one-click 3D Scatter Chart

If an additional column is used to the left of the data to hold series names for each point then they will be allocated to the correct series in the plot.

3D Scatter Chart with Discrete Series

There is always a problem with perception of 3D data on a 2D surface; normally you have to rotate it in order to understand the location of the points. This is another chart of the same data rotated to show it from another perspective.

3D Scatter Chart with Discrete Series (Rotated)

The angles of the projection are set in the Settings window, accessible from the 3D Scatter dropdown on the Excel ribbon.

The most effective way of viewing the data, however, is to be able to rotate it in real-time. This can be done using the array formula “=QC.3DSCATTER(…)”. This formula will output the projected coordinates for the same source data as the one-click version but it will allow you to link to rotation parameters in the workbook which can be controlled by worksheet form controls to make the projection interactive.

“=QC.3DSCATTER(…)” Formula with Form Controls

There are 6 input parameters for the formula:

  • Data (the individual X, Y and Z coordinates in three columns)
  • Series names (the name of the series to which each point belongs)
  • Axis names (the quantity that each coordinate represents)
  • The separate X, Y and Z axis rotations in degrees which specify how the data is projected

Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.

The height of the array formula output is 8 rows longer than the length of the input dataset in order to contain the Axis points. The width of the array formulas is 2 greater than the number of different series within the dataset. There will always be a title row as well, to hold the axis names. So, for example, if there are 100 data points (plus a title row) spread across 5 different series then the array formula output size will be 109 rows ×7 columns.

Settings

The following settings can be adjusted in the 3D Scatter Chart Settings dialog box, accessible through the 3D Scatter drop-down menu on the Excel Ribbon

3D Scatter Settings
  • Lock Axes – Only relevant to the “live” variant, this keeps the axes a fixed size regardless of the rotation. If this is left unticked, the chart may zoom in and out slightly as it is rotated to fill the space more efficiently.
  • Keep Chart Square – this keeps the plot area of the chart as a square which is usually helpful for visualisation. There may be instances where the chart is better viewed as a rectangle, though.
  • Make Axes Equal – this makes the X, Y and Z axes the same absolute length. Combined with Keep Chart Square, this makes the space viewable as a perfect cube. However, if the distribution of data is on dissimilar scales across the three axes it might be better to clear this.
  • Label Axes X,Y,Z – this adds labels to the three “imaginary axes” drawn as lines on the chart, set by the first line of the data selected.
  • Default Perspective – These controls set the default projection for the one-click chart. Set to {0,0,0} the chart will just be a plot of the X and Y coordinates of each point. {330,315,0} will clearly show the points in 3D. The best projection from your data will require some trial and error to identify so it may be useful to experiment with the formula version first.