Grogono Photo Pages
Size:  Index   3x3   4x4   5x5   6x6   7x7   8x8   9x9   10x10   11x11   12x12   13x13 

Analysis with Spreadsheets & Unique Identifiers


This report describes how a spreadsheet can be used to analyze the structure and the underlying patterns which are present in so many Magic Squares. For many sizes, the technique yields a series of patterns which can be utilized both to re-construct the original square and to create a whole family of other squares which will appear to be totally different.


The technique has been developed utilizing Microsoft Excel on a Macintosh. There is every reason to suppose that the method would run equally well on any spreadsheet program but some of the more complicated expressions might require some adaptation.

Transform to start at zero

Nearly all magic squares start at 1 and terminate at NxN where N is the size of the square. The first transformation is to subtract 1 from every cell. This produces the sequence 0 to NxN-1. It has the advantage that the subsequent mathematics is simpler - none of the resulting patterns or "Magic Carpets" have to be corrected.

3x3 Spreadsheet


The spreadsheet is set up to accept a magic square. The number can by typed or "pasted" in. The numbers are repeatedly processed. Each step in the process yields two more squares which are expected to also have the same magic properties:

  1. The first square shows the results when a factor is divided into all of the cells of the original square to yield a "Magic Carpet"
  2. The second square shows the remainders after the division; and is also the magic square will be processed by the next step.
  3. This process is continued until there are no "remainders".
  4. If the original square is pan-magic, then at each stage the two derived squares should also be pan-magic.

Description with 3x3

The 3x3 square serves well because it is so simple. The original square, starting at B2, is treated in two ways. The factor chosen for a square of order three is - not surprisingly - "3".

The square starting at H2 is composed of cells with formulae of the type:

The square starting at B8 is composed of cells using formulae of the type:

The sums for the rows, columns, and diagonals are merely conventional additions. The "plus" mark indicates that the resulting square is magic; this cell is composed of a lengthy formula of the type:
      =if((K1=$G$1) + (K2=$G$1) + .... + (H5=$G$1) = 8,"+","x").

Horiz Bar

4x4 Spreadsheet

Description with 4x4

The 4x4 square offers more options and begins to show the power of this technique. Just as three was chosen as the factor for the 3x3 square, so four could be chosen here. However, far more interesting results are obtained when the square is broken down into its smallest components. This is achieved by using, in succession, the largest factors possible - in this case 8, 4, 2, 1. These factors are shown in the left margin for each square.

The expected line total is shown above the top of each square. As above, the "+" sign indicates that rows, columns, and main diagonals are all "magic". The extra totals for all of the broken diagonals are omitted here for simplicity.

It was using this technique which first made it possible to recognize that all of the patterns are really the same, components of one "Magic Carpet: composed of pairs of ones and zeros. (See also 4x4 pan-Magic Squares):

1 1 0 0 1 1 0 0 1 1 0 0
0 0 1 1 0 0 1 1 0 0 1 1
1 1 0 0 1 1 0 0 1 1 0 0
0 0 1 1 0 0 1 1 0 0 1 1
1 1 0 0 1 1 0 0 1 1 0 0
0 0 1 1 0 0 1 1 0 0 1 1
1 1 0 0 1 1 0 0 1 1 0 0
0 0 1 1 0 0 1 1 0 0 1 1

The technique also makes it intuitively obvious that the patterns can be used in multiple ways. The square could be re-synthesized using the original factors; however, new squares can be also be created by substituting the same factors but into different patterns. The reader will find that with very little additional work, a new square can be calculated. The factors 8, 4, 2, and 1 can be used in conjunction with the various patterns to produce all the possible results.

Larger Squares

Larger squares - such as Order-6 Magic Squares , offer greater diversity of options in the analysis as well as in the subsequent reconstruction. With order 6 the factors which succeed in breaking a square down could be: 18-9-3-1. When the resulting patterns are used to reconstruct other squares, a different sequence could be employed for the same patterns, e.g., 9-18-1-3; or totally different factors may be required, e.g., 1-2-4-12, or 18-1-6-2, 18-3-6-1. The size of the steps depends on the number of bits found in each "Magic Carpet". The interest in this exercise is that although the resulting squares appear to be entirely different, they are based on identical "Magic Carpets"


Some squares are irregular and have magic properties which are not retained during this breakdown process, e.g., some magic squares of order 6 and some pan-magic squares of order 7. For Pan-Magic Squares of order 4 and order 5, there are no surprises - all the squares are regular.


The spreadsheet program offers a simple, widely available, tool to facilitate the analysis of the magic square.


Copyright © Mar 2010 Magic Squares
Mar 6, 2010