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.

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

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.

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.

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.

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