69

In Microsoft Excel,

alt text

I want to make the height of first three rows and first four columns (12 cells in the top left corner) such that these cells are squares. How can this be done?


Surprisingly, Excel says:

Row height: 15
Column width: 8.43

So, these are not on the same scale.

Making both of them 8.43 gives me this:

alt text

Now, what should I do?

2

22 Answers 22

70

One more way...change your view to Page Layout. This changes the grid scale to inches, and then you can specifically set both height and width to the same value (e.g..25 inches). IMO page layout is the best standard view for working on the appearance of the spreadsheet.

Here's an example with the actual dimensions for both views:

Square Cells

4
  • 9
    The most perfect answer to this idiotic Excel problem I have ever heard!! I normally don't measure stuff in "points"...
    – namezero
    Aug 16 '14 at 6:11
  • If you go back to the regular view and want to hide page breaks which were previously disabled: File -> Options -> Advanced -> Display Options for this worksheet -> uncheck Show page breaks.
    – BurnsBA
    Jun 8 '17 at 14:06
  • 5
    the grid scale is not inches but the system or Excel default unit which can be cm or mm
    – phuclv
    May 5 '19 at 3:37
  • 2
    Great answer. Worth noting that Excel will round your values to its internal grid - and as it doesn't use a square internal grid, it won't necessarily round to the same value. E.g. I have put in 0.8cm for both, and ended up with 0.79cm wide and 0.77cm high. That's Excel 2010, so this may be better in future versions.
    – almcnicoll
    May 21 at 10:41
22

Select all (or the rows/cols you need), then drag to resize to your desired size.

  1. Drag a column header's edge to resize the column width. A tooltip appears with the exact pixel count.
  2. Remember the pixel value!
  3. Drag a row header's edge to resize row height, it works the same way.
  4. Drag to the same pixel value.

Done!

2
  • This is the simplest answer. No scripts or changing views. Very nice. Mar 8 '16 at 18:48
  • 1
    Upvoted. This is great as I was able to find the pixel equivalent for inches :)
    – deepakssn
    Mar 22 '18 at 6:47
8

Excel's column width is measured by the number of zeros (0) that can fit in the cell at the Normal style. To convert to points (how row height is measured), see

http://www.dailydoseofexcel.com/archives/2004/06/01/column-widths-in-points/

If you don't need to be exact, just eyeball it. If you do need to be closer than eyeballing, put a square from the Drawing toolbar on your sheet and size it. If you want it 10 x 10, use code like this:

sheet1.Shapes(1).Height = 10
sheet1.Shapes(1).Width = 10
sheet1.Shapes(1).Top = sheet1.Shapes(1).TopLeftCell.Top
sheet1.Shapes(1).Left = sheet1.Shapes(1).TopLeftCell.Left

Then you can manually size your row and column to fit the square and read the height and columnwidth.

2
  • 3
    I'd think with a program as powerful as Excel that something as simple as this would be easier. But it's not. Jul 20 '10 at 21:12
  • 14
    That's because Excel is a spreadsheet/accounting program, not a document layout engine. It also makes little sense to give absolute heights/widths to cells since different systems use different default fonts, which have different character dimensions. Using relative units like em makes more sense. Jul 20 '10 at 23:18
6

VBA seems a little overkill for such a simple outcome.. If you click and hold when you go to drag to change the row/col size, the size in pixels is shown in brackets. These units are not scaled and thus if you set the row and column sizes to equal pixels, they will be square. Of course, this is a manual process.. but you can find the equivalent sizes and then select a range of rows/columns and set all of their sizes at once.

6

The following code worked for me

Sub Grid_Squares()
    With Cells(1, 1)
        Cells.RowHeight = .Width
        Cells.ColumnWidth = .ColumnWidth
    End With
End Sub

Result:

Result

4

You need to be aware that what is mathematically a square and what is visually a square are different. Not all monitors are made the same way. Typically pixels are wider than they are tall.

Look at the following picture:

enter image description here

Each red, green, and blue subpixel make up the whole pixel. As you can see, the combination of the 3 are wider than the height of 1 subpixel. In most cases, the difference is subtle, and most people might not notice it. However, in some cases, people do.

2
  • 2
    This image is perfect if any one wants a migraine :)
    – Dave
    Jul 4 '17 at 12:33
  • I have absolutely no idea how this answer relates to the question, except that both mention the concept of a "square".
    – bers
    Apr 26 at 4:17
3

I determined a square can be made with a ratio of 7.25 row height for every 1.0 point of column width.

1
  • ...for me it's .ColumnWidth = .RowHeight / 7 but +1 for the idea of simply using a ratio.
    – ashleedawg
    Sep 28 '18 at 16:58
2

If you wanted to do it for the whole sheet, you could use this trick - which may be helpful anyway: click the box to the left of column heading A to select all cells; click on and drag one of the column header dividers to the size you want, noting the number of pixels for the resulting cell width (I'm using Excel 2007, which shows this); do the same for one of the row label dividers, matching it to the column width by pixels. This should make all cells in the sheet boxes. Which of course is not what you asked, but I had hoped this trick would work with a subset of cells. Unfortunately it doesn't.

2

Actually, I had the same issue in the past. What works the best for me is the following VBA code. I found the linear relation by just trial and error.

The code works for single cells, but also for a selection. In the latter case, the squares are based on the total selection width or height.

Sub MakeCellSquareByColumn()
    Selection.RowHeight = Selection.Width / Selection.Columns.Count
    Selection.ColumnWidth = (((Selection.Width / Selection.Columns.Count) / 0.75 - 5) / 7)
End Sub

Sub MakeCellSquareByRow()
    Selection.ColumnWidth = (((Selection.Height / Selection.Rows.Count) / 0.75 - 5) / 7)
    Selection.RowHeight = Selection.Height / Selection.Rows.Count
End Sub

You can put these macro's in a Module and assign buttons to them in the quick-access toolbar

Note that the squares disappear (by a changing column width) when you change the font type or size. This is due to the way Excel calculates the column width. See: https://support.microsoft.com/en-us/help/214123/description-of-how-column-widths-are-determined-in-excel

2

Here is a VBA solution.

Private Sub MakeSquareCells()

'//  Create graph paper in Excel see    http://www.erlandsendata.no/english/index.php?d=envbawssetrowcol  if you want cm or inches


    Set Cursheet = ActiveSheet

    'don't drive the person crazy watching you work
    UpdateScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    With wksToHaveSquareCells
        .Columns.ColumnWidth = 5 '// minimum 2, max 400 ; above 7 --> zoom doesn't work nice
        .Rows.EntireRow.RowHeight = .Cells(1).Width
        '// ActiveWindow.Zoom = true
    End With

    Application.ScreenUpdating = UpdateScreen
    Cursheet.Activate           '// Reactivate sheet that has been active at entrance of this subroutine

End Sub
1
  • What is wksToHaveSquareCells?
    – Excellll
    Aug 25 '12 at 20:51
1

First, select the cells you want to resize. Then on the Home tab, go to Cells box and click on Format option. Here you can change the Row Height and Column Width of the selected cells as you want.

2
  • Please see my updates in the question.
    – Lazer
    Jul 20 '10 at 14:55
  • You can manually try to obtain a square. For example, set row height = 15 and column width = 3. Jul 20 '10 at 14:59
1

I wanted to make a perfect square grid for a sewing project and kept getting all kinds of weird answers for this question, so I decided to play with it myself to figure it out. I discovered it's impossible to get a perfect square, but I came as close as you can get, just a sliver off.

  1. Highlight the squares you want to format.
  2. Go to the format tab.
  3. Format the column width at 12.43
  4. Then format the cell height to 75.00.

Using a ruler I found I was just a fraction off at 7 and 10 inches in length. Hope this helps.

1

I use a ratio of 5-1/3, row height to column width.

For example, make a row 53.33 high, and the column-width 10, or 106.66 and 20, respectively, and you will be close enough for government work.

1
  • ...for me it's .ColumnWidth = .RowHeight / 7 but +1 for the idea of simply using a ratio.
    – ashleedawg
    Sep 28 '18 at 16:57
1

I believe this to be the simplest of solutions...

This method utilizes the Excel ruler thus more comprehensible/easier & accurate row & column dimensions.

  • Select View on the Ribbon
  • Within Workbook Views select Page Layout
  • If the ruler does not display, select the Ruler checkbox within the Show group.
  • Hit the Select All button (upper left corner below the name book)
  • Right click a Row, select the size adjustment option & then enter the desired measurement value. Right click & repeat for a Column.

That's it really. The result is what appears as visually perfect squares. There is even an option to change the units of measurement within the Display group of the Advanced tab in Excel Options. Hope this will be most helpful! :)

1

This does the trick pretty neatly using VBA. Set a uniform rowHeight, then use the Width property (returns column size in points) and divide RowHeight by it to get a unit-less height/width ratio. Make the new ColumnWidth that times the original ColumnWidth to make make everything square.

Sub makeSquares() 
Cells.RowHeight = 20
With Cells(1, 1)
W = .ColumnWidth
HWratio = .RowHeight / .Width
Cells.ColumnWidth = W * HWratio
End With
End Sub
1
  • Just realized JackRni did this a little cleaner.
    – Max
    May 4 '13 at 11:46
0

Click and drag on the border between the rows. To resize more than once column/row at a time, select them all, right click and click "Row Height..." and set it to the same height as the rows are wide.

1
  • There is no "row height" in the right click menu.
    – Lazer
    Jul 20 '10 at 15:42
0
  • Select the columns (click the A column, then hold shift and click the other end)

  • Right click on one of the columns, click Column Width and then enter a new value.

  • You can do the same with a row, then click Row Height to get the height of a row.

0

If you want to make row & column looks square, please select all cells those you want to make square and change the height of row to 28.8 AND change the width of column to 4.56.

0

A simple solution

  • select all cells
  • drag the columns to a desired pixel size (you will see the column size in both points and pixels as you drag and resize the columns)
  • repeat for rows (choosing the same number of pixels)
  • this should give you perfect square sized cells across the worksheet
0

To make a single cell square, set it to the desired width and then use

cell.RowHeight = cell.Width

Be sure not to use .Height or .ColumnWidth - why?

  • .Height (and .Width) seem to be read-only.
  • .ColumnWidth is measured in the weird units everyone is talking about here.

I manually squared a cell in Excel (to a size of 20.71 "something", and 150 "pixels") and looked at its properties in VBA:

enter image description here

So, you see how every single answer manipulating .ColumnWidth must be very complicated - and in my case, a solution that worked at 100% (Windows 10) zoom level failed at 125% zoom level. There is a host of questions related to settings columns to the desired width (in pixels or whatever unit), but this actually not what this question is about. Again, set width (manually or however), then set height from width.

This answer is inspired by @AmitPanasara's answer, be sure to upvote his as well if you like this one.

0

Excel 2010

Adjusting in Page Layout view and then switching back to Normal view does not work. I was able to validate by using the drawing tools and making a perfect square in the Normal view. This is the easiest method to make squares any size.

Column Width = 2.71
Row Height = 18.

1
  • What, exactly, is your answer? “Insert a square from the Drawing toolbar”? That answer was given over four years ago. “Size your cells to 2.71 × 18”? How is that a way “to make squares any size”? “Size your cells to a ratio of 2.71 × 18”? This is something like the fourth answer that suggests a particular numeric ratio.
    – Scott
    Sep 9 '14 at 0:04
0
  • Select All - Ctrl+A
  • Right click any column header and select Column width
  • Enter value = 4 > Ok
  • There you will see all cells in perfect square shape.
1
  • 1
    Not on my monitor with my version of Excel with my settings. A specific value for the column width is only guaranteed to work on your exact setup and is useless as an answer without explicitly stating what that is. Please read the other answers to find out why.
    – robinCTS
    Aug 17 '18 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.