Making geometric figures (a nifty trick with text orientation and cell merging)

Last week I promised that I’d discuss how to use text orientation in spreadsheets to make regular geometric figures.

To start off with, a regular geometric figure (also known as a regular polygon) is any figure where all the sides are the same length, and all the angles are the same size. A square is a regular polygon, but a rectangle isn’t because the sides aren’t all the same.

Now, I don’t know about you, but the only regular polygon I could have a prayer of actually drawing by hand would be a square, and that’s only because I’d use a ruler and the corner of the sheet of paper to cheat. However, I remember needing a pentagon when I was trying to draw a soccer ball for a report on Germany, and any time you want to put up a “stop” sign you need an octagon. My mom also used to cut geometric shapes out of felt or construction paper, and we could use them to build pictures. I don’t have any kids myself so I don’t know all the possibilities, but I’m sure there’s a fair number of children’s librarians, teachers, and even parents who’ll be able to attest that these shapes come up more often than you’d think when dealing with kids.

These days you can always try to look up a template online, but one advantage of being able to do it in a spreadsheet is that you can control the size, line weight, etc.

So, without further ado, here’s how you do it.

Step 1: Make a line of dashes. This line should be as long as you want each of the sides to be. You might need to start the line with a single quote (‘) to keep the spreadsheet from thinking you’re trying to write a negative number. If you want to make it bold or change the font size, do that too.

Step 2: Move/Copy the line to the appropriate number/location of cells.
For a triangle you’ll want cell A1, B1, and the merged cell A2&B2. Any time you have an odd number of sides, you’ll need at least one merged cell since there’s going to be a vertex opposite a side.

For a square the simplest pattern would be A2, B1, C2, and D1. There are, however, other ways to do it.

For a pentagon the pattern is pretty complex, but it makes sense if you think about it carefully. The bottom three lines are going outward, and the top two are going inward. Therefore you’re at least going to need 3 cells on the bottom, and 2 on the top. However, in order for the top vertex to be directly over the bottom line, the bottom line has to be in a merged cell, meaning that there must be 4 cells in the bottom. So the lines go in merged a1&b1, merged c1&d1, a2, merged b2&c2, and d2.

For a hexagon you get to do the simplest pattern yet: a1,b1,c1,a2,b2,c2.

For an octagon you also get a relatively simple pattern: a1,b1,c1,a2,c2,a3,b3,c3.

Step 3: Calculate (or look up) the interior angles. There’s a simple formula for figuring out the interior angles of a regular polygon. It’s 180-(360/n) where n is the number of sides. If you’re curious, it’s because the sum of the supplementary angles to the interior angles of a regular polygon always add up to 360 degrees. This means the angles for a triangle are all 60 degrees, squares are 90 degrees, pentagons are 108, hexagons are 120, and octagons are 135.

Step 4: Calculate how angles convert to the direction in a cell.
Bottoms for all polygons are exactly the same: 0 degrees. You don’t have to do anything for them.

Tops for even-sided polygons also stay at 0 degrees.

The sides on a square, and the center-sides on an octagon are both 90 degrees. This will be true of any polygon with a number of sides that’s divisible by 4, just as any polygon with an even number of sides has a top that’s at angle 0.

The top left side for odd-sided polygons is It’s 90-(i)/2, where i is the interior angle. So for a triangle it’s 90-60/2 = 90-30 = 60, and for a pentagon it’s 90-108/2 = 90-54 = 36.

The top right side for odd-sided polygons can be gotten from that. If you’re in a program that goes all 360 degrees, it’s 360 minus the top left angle (the one you just calculated). If you’re in a program that only goes -90 to 90 degrees it’s even easier: its the negative of the top left angle.

The bottom right side of any polygon is just 180 minus the interior angle. (This even works for triangles and squares, though the top pair in triangles have already been addressed, as have the sides of a square.)

The bottom left side can be calculated from that: Similar to the top right pair, if you’re in a program that goes all 360 degrees, it’s 360 minus the bottom right angle (the one you just calculated). If you’re in a program that only goes -90 to 90 degrees it’s even easier: its the negative of the bottom right angle.

Upper right in hexagons and octagons is the same as the bottom left.

Upper left in hexagons and octagons is the same as the bottom right.

Step 5: Change the directions of the lines to what you calculated for each. The lines might cross each other, or might not meet, and that’s okay.

Step 6: Change the alignment of the lines, if necessary. You want to have the best chance of meeting possible. Therefore if you have two sides that are touching the bottom edge of their cells, then the line in between them needs to be bottom aligned, and probably should be center-aligned as well. It depends on your exact setup, but with the patterns I’ve described here the lines on the left should be right-aligned, the lines on the right should be left-aligned, the lines on the sides should be middle-aligned (vertically), the line on the bottom (and top, for even-sides) should be center-aligned (horizontally), and it should be obvious what the vertical alignments of the top & bottom cells should be once you look at them.

Step 7: Adjust the column widths so that the ends of the lines meet each other (or as closes as possible). If necessary, also adjust the row height.

Congratulations! You now have a regular geometric figure.