Can Excel provide 3 dimensional plots?

Índice Show

How to draw?
Insert 3D chart
Example 1 Dynamic chart depended from value in cell
Example 2 Dynamic chart which expanding scope
Dataset for 3-D plotting
Setting up a 3-D graphic with XLSTAT-3DPlot
Axes configuration
Object manipulation
Annotations
Final steps (filtering...)

Details

As you know, it's easy to draw 2D (with 2 axis) graphs in Excel. You can also draw a 3D chart in Excel, with 3 axes: X, Y and Z.

How to draw?

The procedure is very similar to how you draw a 2 dimension graph. Suppose you have data on quarterly sales in millions for the last 10 years, something like the following.

See the data carefully. You have 4 x 10 data fields. Just follow these steps:

Insert 3D chart

Select the data, including labels, in the Insert ribbon tab go to the column and select the 3-D chart.

And that's it! You've just inserted a 3 axis chart.

Right-click on bars and select 3-D Rotation to adjust the grade visibility.

Note: For learning purposes, use the table shown above (numbers in an increasing pattern). Otherwise, the result will be fairly unreadable since 3D charts just don't work on a 2D surface, unless you actually rotate them and get things into perspective.

Further reading: 4 axis chart Chart with multiple series Chart with subcategories

Page 2

In this lesson you will learn how to create dynamic chart in Excel. There are useful when you want to have charts which change when you change the scope of data.

People who can make dynamic charts in Excel are truly Excel's experts. Become one of them and learn how to create dynamic charts.

There are two ways to create dynamic charts in Excel.

Example 1 Dynamic chart depended from value in cell

First prepare table of data. In my example there are values of sales.

Create chart. It should be just basic statis chart which you will turn into dynamic chart.

Next go to Ribbon > Formulas > Define name. Define B and C column as Year and Sales.

Use OFFSET function. Formulas are:

=OFFSET(Sheet1!$B$2;;0;Sheet1!$A$1) for Year

=OFFSET(Sheet1!$C$2;;0;Sheet1!$A$1) for Sales

Next go to chart. Right click on the chart and go to Select Data.

Edit your series. In there is only one serie - Sales but you can have more.

Series name is: =Sheet1!$C$1

Sheet1 is the name of my sheet

C1 is the cell where I have my Sales serie

Series values are: ='Dynamic chart.xlsx'!Sales

Dynamic chart is the name of my spreadsheet

Sales is the name which I defined

Now the chart is dynamic. You created dynamic chart which works really easy way.

Just write into A1 cell value of years you want to see on the chart.

There is an example for 13 years.

The is an example for only 6 years.

As you see chart changes. On the chart there are only as many years as you wrote in A1 cell before.

You can also start from the newest year. Just change formulas from:

=OFFSET(Sheet1!$B$2;;0;Sheet1!$A$1) for Year

=OFFSET(Sheet1!$C$2;;0;Sheet1!$A$1) for Sales

to:

=OFFSET(Sheet1!$B$14;;0;Sheet1!$A$1) for Year

=OFFSET(Sheet1!$C$14;;0;Sheet1!$A$1) for Sales

Now write some values into A1 cell with minus sign. That how it looks like for 6 years and 8 years.

Chart changes really good. Isn't it amazing?

Example 2 Dynamic chart which expanding scope

There are second way to dynamic the chart in Excel. Dynamic means here that chart will expand when you will add new data. It's useful when you have worksheets which are often updated.

You can use the table of data and the static chart from Example1. You must change the name which you define. It should be:

=OFFSET(Sheet1!$C$2;;;COUNTA(Sheet1!$C:$C)-1) for sales

=OFFSET(Sheet1!$B$2;;;COUNTA(Sheet1!$C:$C)-1) for years

Now expanding the dynamic chart is ready. When you will add some data the chart will dynamically expand.

Dynamic chart is one of the most impressive Excel charts which I know. It works really good and proves that you are Excel's PRO.

You can download a free dynamic template from this link.

This tutorial will show you how to draw a 3 dimensions - 3D - plot in Excel using the XLSTAT add-on statistical software.

Dataset for 3-D plotting

The data correspond to the outputs (rows points and columns points) of the correspondence analysis that has been performed in another tutorial. Using XLSTAT-3DPlot we will be able to visualize the correspondence map in 3 dimensions.

Setting up a 3-D graphic with XLSTAT-3DPlot

Before you activate XLSTAT-3DPlot, you can select the data you want to use for the 3D visualization. Note that the sum of the contributions on the 3 axes is included: it could be used to determine the size of the bubbles on the 3D map.

Once XLSTAT is activated, select the XLSTAT / Visualizing data / 3DPlot command, or click on the corresponding button of the Visualizing data toolbar. If you cannot see the button, you need to install XLSTAT-3DPlot. XLSTAT-3DPlot can be downloaded from the XLSTAT website.
Once you’ve clicked on the 3DPlot button, the XLSTAT-3DPlot device appears.

First, a dialog box appears asking you to select the input data format: a table with observations in rows and variables in columns or a pivot with data organized in a grid. Select the first option and click on OK.

XLSTAT-3DPlot automatically draws a plot out of the data you selected. Notice that XLSTAT chose to draw a scatter plot, but many other types of plots are available (check the Charts tab). Starting from this plot, you can manipulate several display options to reach the result that fits your needs.

We will manipulate axes, objects (points), annotations as well as lattices.

Axes configuration

By default, XLSTAT-3DPlot chooses the three first columns in the dataset (Labels, F1 and F2 in our case) to build the X, Y and Z axes. We would like to switch to F1, F2 and F3 instead.

Click on the Axes tab. Make sure you select the F1, F2 and F3 data under the X, Y and Z axes, respectively. Uncheck the Data labels option as they are not very important in the context of correspondence analysis.

Resulting plot:

You may modify many other options related to the axes (arrow type, labels…).

Object manipulation

Coloration: It would be interesting to color points according to the data column “type”. Go to the Object tab. Under the Color option (top left), select the Type column.

Size: You may want to let the point size be proportional to the Sum of the contributions column data. Select this column in the corresponding slot in the objects tab. Notice that doing so makes the interpretation in perspective a bit more complicated. Thus we won't do it in this tutorial. Maybe you should rather use filtering to remove data points associated to a sum of the contributions lower than a certain threshold (see last paragraph in this tutorial).

Annotations

We will use the names stored in the Labels columns to tag the points. Go to the Annotations tab and select the Labels column under the Labels menu. Increase the font size for a better visibility.

Resulting plot:

Lattices

Lattices allow a more comfortable navigation in 3D. We will add a lattice at the base (axes F1/F2) and remove cumbersome annotations.

Resulting plot:

Final steps (filtering...)

Finally, many more options are available.

For example you may use filtering to only keep data points that have a sum of contributions higher than, say, 40%:

The mouse wheel allows you to move the image back and forth. Last, rotations can be done by clicking on the right button and by moving the mouse to the left and to the right (while keeping the button down).

Once you are satisfied with the result, you might want to copy the image (use the Ctrl C shortcut) and paste it in another application (Word or Powerpoint for example), or to save the visualization for future use or modification.

Was this article useful?