Wednesday, December 08, 2004

Square Cells in Excel

If you have done much drawing using Excel's drawing tools, you may have figured out that you can use the cells as a grid.
For example, if you press the Alt key as you draw, your shapes will snap to the corners of the cells. (You can also turn on the "Snap to Grid" option, but using Alt is more convenient) Holding the Shift key will scale shapes proportionally.

Instead of explaining the steps to creating the grid, I'll just provide a couple of tips. First, the column width is dependent upon the font of the Normal style (which by default is usually Arial). Quoting from Excel's VBA help file (the ColumnWidth property):

"One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used."
The row height is measured in points (1pt = 1/72 inch or about 1/28 centimeters). A point is a "unit of measure referring to the height of a printed character" (quoting from the VBA help file for the RowHeight property).

Now, assuming that you are using an Arial font, you create different grid sizes using the following table:

Font SizeRow HeightColumn Width

To make sure that the cells are square, create a square using the drawing tools (making sure you hold down the Shift key, or you'll end up with some arbitrary rectangle).

I've notice that when I print from Excel, the grid doesn't always end up square, so the drawing is no longer the same aspect ratio as on the screen. To get around this, I just copy and paste into PowerPoint. There may be a better solution, but I haven't found it yet. One approximate rule of thumb for printing a square grid is to make the row heights 1 pixel larger than the column widths.

Update 2/9/2010: I just created a Graph Paper template for Excel and Word that include a variety of different square grid sizes as well as an isometric grid. I also came across a good article on Excel as Gridpaper for Drawing.


Jon said...

The code to square up cells in Excel is pretty easy. You can read the column width in points, and adjust the columnwidth in character units. If you set the row height first, this will square up the columns:

Sub SquareCells()
Dim i As Integer
For i = 1 To 4
With ActiveSheet
.Columns.ColumnWidth = _
.Columns("A").ColumnWidth / .Columns("A").Width * _
End With
End Sub

You need to loop a few times, because at first attempt, Excel tries to use an arbitrary fraction of a character. In Arial, Excel likes increments of 1/7, in Arial Narrow it's 1/6. For wider columns, you only need a couple loops, but for smaller ones (like the needlepoint grid I wrote this for), you need at least three. Four seems robust enough, and it's so quick no human can detect the looping. (Use ScreenUpdating = False/True if you do notice it.)

The non squareness of the printed grid relates to the fact that the screen fonts are only an approximate representation of the printed truetype fonts, blah blah. What I do is print out one page, get a ruler and measure the grid, and determine a fudge factor to multiply the column width by to square it up. It's only off by a couple percent, but that's enough to look wrong.

- Jon

Anonymous said...

Good luck with the new blog, I look forward to reading it!

Anonymous said...

Wow... look at all the information out here... Square cells in Excel. And I was actually just wondering this. Thanks for whoever posted this... keep up the good work!

Anonymous said...

Actually, I have been using the following code, which sets the width first:

Private Sub MakeSquareCells()
Dim duh As Long
Dim myPts As Long
Dim myRange As Range

On Error GoTo TheEnd
Set myRange = Application.InputBox("Select a range in which to create square cells", Type:=8)
On Error Resume Next
If myRange.Cells.Count = 0 Then Exit Sub

duh = Val(InputBox("Input Column Width: "))
Application.ScreenUpdating = False
For Each c In myRange.Columns
c.EntireColumn.ColumnWidth = duh
Next c
myPts = myRange(1).Width
For Each r In myRange.Rows
r.EntireRow.RowHeight = myPts
Next r

Application.ScreenUpdating = True
End Sub

Anonymous said...

Thank's a lot!

Anonymous said...

a simple solution could be

- select all cells
- drag and resize the columns to a desired pixel size (you will see column width in both points and pixels as you drag and resize)
- repeat the same for rows (selecting the same pixel size)
- this should give you a worksheet with perfectly sized square cells

Jon Wittwer said...

Making the column widths and the row heights equal to the same number of pixels works for on screen, but this will not mean that the grid will be square when printed.

Anonymous said...

Wow, Great thanks for square cells. You rock Jon!