Suppose you have a random set of (x,y,z) data points for some function z=f(x,y). How would you plot the 3D surface in Excel? the short answer is you can't unless you can resample the function at a uniform grid. If the function f(x,y) is unknown, re-sampling at a grid may not be an option. In this example, we will show how you can use GRIDXYZ to interpolate the scattered data set onto a uniform grid for easy plotting. To illustrate the concepts, we will sample the random set from a famous function: the Mexican hat function, a radially symmetric sinc function given by: $$f\left(x,y\right)=\frac{\sin \left(\sqrt{\left(x^2+y^2\right)}\right)}{\sqrt{\left(x^2+y^2\right)}}$$ which has a shape like a Mexican hat.

To generate the scattered data, we use Excel Rand() function to generate random values for x, and y in the interval [0 12]. To preserve the generated random numbers from continuously changing, we must copy them into a new range as numbers only. The table below shows our generated scattered (x,y) points and corresponding z values for the Mexican hat function using Excel's AutoFill feature.

We have generated about three hundred points to obtain good coverage for the large [0 12] by [0 12] grid area we plan to use to be able to to show the Mexican hat function.
The steps to generate a grid in Excel are straight forward. we create a uniform row vector for the x-values and a uniform vertical vector for the y-values, as shown below. We chose to create a grid in increment of 0.4 from 0 to 12 for both x and y directions using AutoFill. Note how the x- and y-coordinates are arranged. the yellow shaded area represents the grid (x,y) points at which the z values will be found.

Now to interpolate the scattered (x,y,z) data onto the grid, we evaluate the array formula =GRIDXYZ(A3:B300, C3:C300, I3:AM3, H4:H33) in the yellow range. The first argument A3:B300 is the (x,y) values of the scattered data points, the 2nd argument C3:C300 is the corresponding z values, the 3rd argument I3:AM3 is the generated grid x values, and the 4th argument H4:H33 is the generated grid y values. Excel does the rest for us and correctly computes the interpolated z values at each grid location. The interpolated values are partially shown below.

To generate the surface plot in Excel, we simply highlight the yellow range and insert a surface plot. The plot is shown below and shows only one quarter of the radially symmetric Mexican hat function.