Nice-looking sine waves in Excel, with conditional formatting.

Today I have another small project to share with you^{1}. I decided to create a sine wave pattern in Microsoft Excel, using mathematical operations and conditional formatting. To create a basic sine wave, we can use the function *f(x) = sin(x)*. This will create a wave graph, where the *y *value of the graph represents the value of the function for the corresponding *x* value. It is possible to expand this into a 3D wave, by using two input values, instead of one: *x* and *y*. In this case, our output value will now be *z*. We can display a 3D function on a 2D surface (like a spreadsheet) by using colors to represent the *z* value. For this project, I’ll use a basic color range with red, orange, yellow, and green.

Our equation will now be *z = sin(x) + sin(y)*, where *x* represents the *x* coordinate on the plane, and *y* represents the *y* coordinate^{2}. *z* corresponds to the color value of that point (or cell, in this case)^{3}. Since the formula for a circle is *r ^{2}*

*= x*, you may be able to see how the two are similar; in our 3-dimensional example, this would be

^{2}+ y^{2}*z = x*. This produces a circular gradient around the point (0, 0). However, instead of a square function, we are using a sine function. This will allow the circular waves to repeat, instead of only producing one shape or gradient.

^{2}+ y^{2}Since we can’t directly retrieve the *x* and *y* coordinates of each cell in an Excel spreadsheet (as far as I know of), we must first create two grids of cells: one to represent the *x* location of the cell and one to represent the *y* location. We can then create a function using these with Excel expressions, by combining these two grids. These are the grids that represent the coordinates – *x *on the left, and *y* on the right.

This is what we get when we simply add together the *x* and *y* values (using the function *z = x + y*):

And this is what we get when we use the function *z = sin(x) + sin(y)*. We produce circular diamond-like shapes^{4}. We can see the *x* value oscillating up and down from left to right, and the *y* value oscillating up and down^{5}. These combine to form these waves.

I also tried the function *z = sin(x ^{2} + y^{2})*, or the sine of the circle formula

^{6}.

These waves, however, have too high a frequency^{7} (although they do form some cool patterns). To fix this, we can divide the *x* and *y* values by whatever number we want, before taking the sine of their sum, to change the size of these waves. In this case, I used 10. This is the result:

If we increase the size of the grid, we can view the waves at a higher resolution.

And we can also spread the waves out more:

Pretty cool. I’ll probably also do an interactive JavaScript program for this soon, and may try one with 4-dimensional functions, too. Thanks for reading.

- Sorry I didn’t do any big posts in the last two weeks. I was away the other weekend and didn’t get a chance to post, and been really busy since. I’ve also been doing a bunch of small posts and website updates, which you can check out here.
- You can also use
*z = sin(x) • sin(y)*. - Here’s the same function graphed as a 3D model on Google and WolframAlpha.
- This may also have something to do with the way that conditional formatting processes numbers and colors, as the waves were more circular in 3D graphs.
- Yes, the
*y*value is inverted. - This is actually what I originally did – it took me a while to realize this wasn’t the correct function. Although it looks really cool.
- It took me quite a while to realize that I couldn’t see the values past the decimal place because the cells were simply too small.