Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Media
Contact
Home

  Bookmark on del.icio.us!

Solve Sudoku with Excel

We are a little slow here in the midwest. The Akron Beacon Journal just recently started publishing a daily Sudoku puzzle in our comic strip pages. Wow! Talk about a time waster! I nearly became addicted to solving these puzzles.

Sudoku is a 9x9 grid. In the newspaper each day, they may fill in 20-30% of the 81 numbers in the grid. Your goal is to fill in the remaining numbers. Each row, column, and 3x3 subgrid must contain each of the digits from 1 through 9 without any digits repeating.

When you consider the puzzle above, the top left square can not contain 1, 5, 6 or 8 because these numbers already appear in the first column. The top left square can not contain 4 or 9 because these numbers already appear in the same row. It can not contain a 4 because a 4 appears in the upperleft 3x3 subgrid. Thus, the possible numbers for the top left cell are 2, 3, or 7. Trying to solve the puzzle from the morning paper quickly consumes all of the time for breakfast and can make you late for work if you are not careful.

Enter Mike Oldroyd. A few months ago, he sent me an Excel workbook to solve Sudoku. Mike writes, "Just for fun a few days ago I decided to try and use Excel to solve Soduku puzzles. Attached is the result. I have not protected the VBA code so anyone who is interested can poke inside to see how it works."

Mike's workbook is very cool. It gives you the complete solution in a few seconds. I can now concentrate on writing the next Excel book instead of trying to solve these puzzles. It would probably be cooler if I adapted the workbook to solve the puzzle but only reveal the one square that I'm stuck on. Sometimes, if I could just solve one square, the rest of the puzzle would start to fall into place.

So - a big thank you to Mike for contributing this workbook. I've zipped it at this link.

For more about Mike, visit http://www.mikeoldroyd.com/.


Update! Jobey Jones from Surrey, England sent in a workbook that is a Sudoku Assistant. Instead of giving you the solution, it makes quick work of a lot of the work involved in solving Sudoku.

Enter the puzzle in his grid, and many formulas provide statistics. The fact is that the digits from 1 through 9 add up to 45, so first Jobey figures out the total for each row, column, subgrid and compares this to 45. He reports which numbers are missing in the row, the column, and each subgrid.

My favorite part is this section where he shows which numbers are still possible in each cell of the grid. Clearly, the 2nd column in the 5th row must be a 9, since that is the only value left.

If you still want to do some of the work of solving Sudoku, download this zipped sudoku2.zip. This file was updated to version 2 on Aug 10, 2006.

Tip: if you have both programs, you can enter the puzzle in one form, then use Copy / Paste Special - Values to copy to the other program.

Thanks to Jobey for sharing this workbook with Sudoku fans everywhere.


Update Again! Jerry from Erie, New York sent in another workbook that is a Sudoku solver. This one is cool because it lets you see how to solve Sudoko iteratively. Enter the puzzle from the paper. Jerry's workbook shows you which squares can be solved right now. Click Solve to put those numbers in the grid. Then, new squares light up in yellow as being solvable. Press Solve to put those squares in the grid. If you are new to Sudoku, this will help you to understand that solving Sudoku is a step by step process.

Enter the puzzle in the upper left grid.


The upper right grid will light up in one of two shades of yellow to tell you the squares that can be solved.

Here is how it knows the lower right square has to be a 6: All of the other numbers from 1 through 9 are already used: 2, 3, 4, and 5 are in the same row. 4, 7, and 9 are in the same column. 1, 2, 5, and 8 are in the same subgroup. This means the only possibility left is a 6.

Click the Solve!!! button to transfer the yellow squares to the upper left grid. Once that information is known, new yellow squares show up in the upper right. Continue the process.

Here is a good example in the second round. There are three unfilled cells in the right column. They all could host a 3. The one in yellow can only host a 3, meaning that the square with the possibility of a 3 or 8 must be an 8. In these cases, the 3,8 square is highlighted in lighter yellow and when you click solve, an 8 will be sent to the grid. The lower right grid shows you which of the light-yellow numbers will go to the grid.

Thanks to Jerry for contributing this workbook. Download it zipped from JerrySuduko.zip.


Update Again! Simon Lindsey built another Sudoku solver. This one is cool, because it is somewhat visual. You can see the program grey out cells - if you end up with only one white cell - the program writes the solution there. Eventually, the program stops and you have to make a few guesses and then continue.

Download it from this link.



Update For 2010!
David Dawson of Sydney has sent along his version of a MS Excel Suduko solver. The interface is colourful and offers not only the working version of the puzzle, but the starting puzzle as well.
 
You may download the zipped version of David's Puzzle using DDawsonSuduko.zip .

This page originally published on October 27, 2005. Updated January 18, 2010. The permanent URL for this page is http://www.mrexcel.com/tip109.shtml.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.