Excel Sine Waves

Excel Sine Waves

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

Today I have another small project to share with you1. 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 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 coordinate2. z corresponds to the color value of that point (or cell, in this case)3. Since the formula for a circle is r2 = x2 + y2, you may be able to see how the two are similar; in our 3-dimensional example, this would be z = x2 + y2. 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.

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 – 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 shapes4. We can see the x value oscillating up and down from left to right, and the y value oscillating up and down5. These combine to form these waves.

I also tried the function z = sin(x2 + y2), or the sine of the circle formula6.

These waves, however, have too high a frequency7 (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.

  1. 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.
  2. You can also use z = sin(x) • sin(y).
  3. Here’s the same function graphed as a 3D model on Google and WolframAlpha.
  4. 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.
  5. Yes, the y value is inverted.
  6. 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.
  7. 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.