Creating an XYZ Chart Where Z Is a Function of X and Y

 Last reviewed: November 3, 1994
Article ID: Q68720
The information in this article applies to:
  • Microsoft Excel for Windows versions 3.0, 4.0

SUMMARY

In Microsoft Excel you can create an XYZ chart or graph. To do this you must first enter the appropriate X and Y values into the spreadsheet (one set of values down a column, and the other set across a row). Because Z is a function of X and Y, or a function of two variables, the formula for Z is entered and copied into the cells below and to the right of your X and Y values.

Example

In the function 

   Z= X*Y
the following constraints apply: 

   -3 <= X <= 3,  -3 <= Y <= 3
Constraints are specified for the X and Y values. If constraints are specified for a variable, it means that the values for the variable must obey the constraints. For instance, in the above example, X and Y must take on values between -3 and 3.

MORE INFORMATION

To create the graph for the above function:

  1. Enter the appropriate values for X and Y into a spreadsheet. Enter the values for X down column A, beginning with A2, and the values for Y across row 1, beginning with B1. Use the values -3, -2.5, -2, -1.5, -1, -.5, 0, .5, 1, 1.5, 2, 2.5, 3 for X and Y. Keep in mind that if you enter more points, the graph will appear smoother. You can have Microsoft Excel automatically enter many values for you, subject to the constraints, by using Goal Seeker to interpolate (see Interpolating section below).

  2. Enter the formula for Z into B2:

    =$A2*B$1

    Note that column A and row 1 are entered as absolute references in the formula. The rest of the formula uses relative references.

  3. Select the remainder of the work area (B2:N14). Press F2 to enter edit mode and press CTRL+ENTER to Fill Down and Fill Right.

  4. Select all of the data (A1:N14), including the X and Y values, and create the graph by pressing F11 or by choosing New from the File menu and selecting Chart.

  5. Choosing 3-D Line from the Gallery menu will change the chart to a three dimensional line chart. Choose Main Chart from the Format menu and set Gap Depth to 0 percent to create a smoother picture. Remember, the picture will not appear smooth, unless enough X and Y values have been plotted.

Other Functions to Try

   Z = (sin(X^2+Y^2))/(X^2+Y^2)
   Z = cos(X*Y)

Interpolating

Estimating and inserting values between two known values. The inserted values for the graph examples above will be evenly distributed between the two known values. For more information on interpolating, query on the word "interpolate".

REFERENCES

"User's Guide 1," version 4.0, page 486 "User's Guide 2," version 4.0, pages 78-79 "Microsoft Excel User's Guide," version 3.0, pages 290-291, 492-497

KBCategory: kbusage
KBSubcategory:

Additional reference words: 3.0 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.