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 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 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 – 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 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:
- 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.