How to make a 6×6 magic square using a spreadsheet

You may think that, the way I offer instructions here, that I could make this article shorter. And you can make this less work: just take out all the safeguards and self-checks that I have programmed into this spreadsheet, and you are back to something close to a pencil-and-paper experience.  If you are actually using pencil and paper, and plan to make more than a few, be prepared to go through a lot of paper.

I use Apache Open Office as the spreadsheet, and I can assure you that the same commands work in Excel. There are a lot of commands to invoke, but once they are set, you need only modify 12 different numbers on one of the preliminary squares if you want to change anything. So, while it’s a bit of work, it is rewarding in the end. There are also several shortcuts to forula making that save work, but in one part of the operation, the construction of the second preliminary magic square there are no shortcuts that I am aware of. But after all that work, you have a constructed a system by which a good number of the de la Hire squares can be made. I say “a good number”, since there are two methods which seem to be attributable to de la Hire: one described by Clifford Pickover in his book The Zen of  Magic Squares, Circles and Stars, and another in a PDF I found on the internet originating from the King Fahd University of Petroleum and Minerals, located in Saudi Arabia. While the PDF cites Pickover in the referece section, both methods of construction which are attributed to de la Hire are quite different. This article favours the King Fahd method. The King Fahd method is likely a simplification, but it works well. Pickover’s is more open-ended, and one would need to fiddle with it a lot more to get it to work.

I don’t give mouse-based or keyboard-based shortcuts in this article, since it would make this article even longer. I suggest that you need some familiarity with your chosen spreadsheet and its shortcuts to make this process a lot easier.

The idea is that the magic square we want is the sum of two preliminary 6×6 squares. The first square, as in the odd-ordered square, consists of the numbers 1 to 6. Both main diagonals must contain the magic numbers 1 to 6 in sequence, increasing from left to right.

1 1 6
2 2 5
3 3 4
4 3 4
5 2 5
6 1 6

Next, you work in columns. The values in the last 3 columns are dependent on the values of the first 3 columns. The first and last column are dependent, and in the first column you arrange random 6s and 1s so that there are 3 of each. The last column is an anti-reflection, in that a 1 appears in a cell of the last column if a 6 appeared in a corresponding cell in the first column. In case you want to change your square around, you can get the spreadsheet to do this by placing an =IF function in the cells of the last column. For cell F2, you can type the formula: =if(a2=6,1,6). If you are using open office, use semicolons in place of commas. This says that if the value in A2 is 6, then place a 1 here, othwerwise place a 6 here. This formula can be repeated for cells F2 through F5, changing “a2” to the corresponding cell.

Your square might now look something like this:

1 1 6
2 6 2 5 1
3 1 3 4 6
4 6 3 4 1
5 6 2 5 1
6 1 6

We may consider 1 and 6 as being complementary pairs. Similarly, 2 and 5 are another such pair, while 3 and 4 are a third. Notice that the last column consist of nothing but the complement of the first column. In every corresponding row, a 6 is exchanged for a 1, or a 1 for a 6. You proceed similarly for the other four columns. For column B, you need three 5s and one 2. Column E will have their complements, possibly by inserting =IF() statements in the blank cells. Proceeding similarly for columns C and D, you get something like:

1 1 5 3 4 2 6
2 6 2 4 3 5 1
3 1 2 3 4 5 6
4 6 5 3 4 2 1
5 6 2 4 3 5 1
6 1 5 4 3 2 6

Now, check to see if there are duplicate rows. This should be somewhat easy to spot, but if you want this to be auto-detected, you can use formulas. My experience tells me that you ought to watch out for certain pairs of rows: rows 1 and 6, rows 2 and 5, and rows 3 and 4. You may set up an =IF() to check this. Outside of your square, type:


one command in each cell. If everthing goes right, each of these statements sould say “FALSE”. If anything reads as “TRUE”, then duplicate rows exist. In the above square, rows 2 and 5 are duplicated. Switching the values in A2 and A3 was enough to fix that problem. The square, along with the second square, is shown below:

1 1 5 3 4 2 6 FALSE 0 0 30 30 30 0
2 1 2 4 3 5 6 FALSE 24 6 6 24 6 24
3 6 2 3 4 5 1 FALSE 12 18 12 12 18 18
4 6 5 3 4 2 1 18 12 18 18 12 12
5 6 2 4 3 5 1 6 24 24 6 24 6
6 1 5 4 3 2 6 30 30 0 0 0 30

The three “FALSE”s above are from the =IF() statements shown above which assure that there are no repeated rows in the first square.

The second square. written in columns K through P, is merely the consequence of the first square. It contains multiples of 6 from 0 to 30, but whose values are the products of the first square, as a matrix which has been turned on its side. The first row of this new square are derived from column A of the first square. If we let n be any row of the first matrix, then the first row shall take on the value given by the spreadsheet formula =(An – 1)6, replacing n with the numbers 1 through 6. These formulas occupy the first row of the new matrix. For example, M1 can take the formula =(A3 – 1)6.

The second and subsequent rows of the new matrix are thus derived from values of the corresponding columns of the first matrix. N5 would have the formula =(E4-1)*6 typed into it. And if you construct the square this way, it is rewarding once it’s done, since you only need update the 12 numbers in the first 3 columns of the first square; the ones which omit the diagonal numbers. The rest of the spreadsheet will figure everything out in real time. So, in my opinon, it’s worth the slight tedium, and takes only a few minutes of concentrated effort.

Now, we can add the squares. To do this, you need a third square, which will be the matrix sum of the first two. Place the square in an out-of-the-way location, such as below the first two, and in those cells, enter your sums. The first row and column should contain (in consistency with my example) the formula =A1+K1. This goes on until the last row and last column, which contains the sum: =F6+P6. The last square then probably looks something like this:

8 1 5 33 34 32 6
9 25 8 10 27 11 30
10 18 20 15 16 23 19
11 24 17 21 22 14 13
12 12 26 28 9 29 7
13 31 35 4 3 2 36

Now, we need to do a sanity check. Did we actually get all numbers from 1 to 36? You can sit there and hunt around, but since we have a spreadsheet, we can lay out the numbers 1-36 in sequence in one column from R1 to R36, and in the next column do a =countif() to the absolute cell addresses $E$8:$J$13. The countif command in cell S1 would be: =countif($e$8:$j$13, R1), and for cell S22 would be: =countif($e$8:$j$13, R22).The resulting columns under R and S:

1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
11 1
12 1
13 1
14 1
15 1
16 1
17 1
18 1
19 1
20 1
21 1
22 1
23 1
24 1
25 1
26 1
27 1
28 1
29 1
30 1
31 1
32 1
33 1
34 1
35 1
36 1

Column R contains the numerical value, and column S is the frequency that number appears in the magic square. The 1 in that column assures us that we have the right number of each of the 36 numbers. A “2” or a “0” ought to worry us, since that tells us there are duplicates or missing numbers. But it looks like we’re doing just fine.

Now finally to check for magic. The first row of the magic square is row 8, so place this formula in L8: =SUM(E8:J8). Do this for each row. The cell ranges represent the corresponding row. Under L13, we type: =SUM(E13:J13), for example. All values should equal 111.

The columns also get this treatment: cell E15 gets the formula: =SUM(E8:E13). Do this for all columns. Column J should receive the formula =SUM(J8:J13) in cell J15. All sums should equal 111.

The diagonals are a little more picky, since they are not really amenable to a =SUM() command, and they are not tangible cell ranges. So, the formula: =E13+F14+G15+H16+I17+J18 is placed in cell L15 for the left-to-right diagonal which slants down. The other diagonal has the formula =e13+f12+g11+h10+i9+j8 placed in cell C15.

This all results in the following arrangement:

8 1 5 33 34 32 6 111
9 25 8 10 27 11 30 111
10 18 20 15 16 23 19 111
11 24 17 21 22 14 13 111
12 12 26 28 9 29 7 111
13 31 35 4 3 2 36 111
15 111 111 111 111 111 111 111 111

The 111’s tell us that we have achieved the magic sum in all rows, columns and both diagonals. The King Fahd variant appears to allow for only 43 = 64 magic squares out of the thousands that are possible, and even then, not all of them work. Pickover’s method may lead to 4096 possibilities, but it would appear that most of them are dead ends, making the latter variant more difficult to work with.

The de la Hire method is not the only method that exists. 24 more squares are possible using the “Magic Carpet” method elucidated by the Grogno website. Many methods such as Magic Carpet conceive of the square as a collection of smaller squares, and the method fpr their construction appear to be non-random. de la Hire can be used on all singly even magic squares. From what I have been able to find there appears to be no truly random method for constructing singly even squares such as 6×6 as there was for 5×5, 7×7, and other odd-ordered magic squares.

Published by


I am Paul King, a math and science teacher. I help maintain the MIT FAQ Archive along with Nick Bolach. I am also the maintainer of the FAQ for