excel sudoku formula

"Solve Sudoku with Excel: The Ultimate Formula Guide"

By Christopher King
AD

Ever found yourself tackling a Sudoku puzzle and wished you could automate the process? Excel, with its powerful formulas, can turn this wish into reality. It's not just about solving puzzles though; understanding and applying Sudoku formulas in Excel opens doors to complex data validation, logical testing, and even game development. Let's dive into the world of Excel Sudoku formulas.

Before we start, ensure you have Excel 2016 or later, which supports the Required ROW Function used in Sudoku solvers. We'll assume you're starting with a blank spreadsheet and a Sudoku puzzle ready to be input.

Setting Up Your Sudoku Puzzle

First, input your 9x9 Sudoku grid into cells A1 to I9. Then, apply the following formatting:

AD
Excel Game : Sudoku in Excel - Work and Play
Excel Game : Sudoku in Excel - Work and Play
7 Fun & Weird Things You Can Create With Microsoft Excel
7 Fun & Weird Things You Can Create With Microsoft Excel
GUESS - Le générateur de sudoku illimité dans Excel
GUESS - Le générateur de sudoku illimité dans Excel
  • Number Cells: Format cells with given numbers (1-9) as 'Input Only' to avoid wrong entries.
  • Default Cell: Format empty cells (where the puzzle's solutions will appear) as 'Number'.

Now, let's delve into the formulas that'll solve your Sudoku puzzle.

Identifying Used Numbers in a Row, Column, or Box

Before solving, we need to check which numbers are already used in each row, column, and box (3x3 square). We'll use the COUNTIF Function for this.

the excel formulas chart with numbers and symbols for each item in this table, you can
the excel formulas chart with numbers and symbols for each item in this table, you can
¿Cómo crear un SUDOKU en Excel? | Tutoriales de Excel #17
¿Cómo crear un SUDOKU en Excel? | Tutoriales de Excel #17
a poster showing the top 22 excel formulas
a poster showing the top 22 excel formulas

Let's start with rows. For example, to check used numbers in row 1 (A1:I1), use:

=COUNTIF($A$1:$I$1,{1,2,3,4,5,6,7,8,9})

AD

Simply copy this for all 9 rows.

Excel formulas
Excel formulas
the top 15 excel formulas for each user in their workflow, including numbers and symbols
the top 15 excel formulas for each user in their workflow, including numbers and symbols
Top 15 Excel Formulas Every Beginner Must Know 📊 | With Examples
Top 15 Excel Formulas Every Beginner Must Know 📊 | With Examples
the excel formula sheet is shown in green and white
the excel formula sheet is shown in green and white
Crear un SUDOKU en Excel
Crear un SUDOKU en Excel
Top 21 Excel Formulas Every Professional Should Know
Top 21 Excel Formulas Every Professional Should Know

Creating Sudoku Solver Formulas

Now, we'll create the solver using a combination of IF, AND, COUNTIF, and Required ROW Functions. Our general formula will look like this:

=IF(AND(Drop down conditions), "1-9", "")

AD

The solver checks if a number fits in a cell. If one does, it displays that number. If not, it leaves the cell empty.

For the first cell (A1), the formula could be:

=IF(AND(B1>0,COUNTIF($A$1:$A$9,B1)=0,COUNTIF($A$1:$I$1,B1)=0,COUNTIF($A$1:$I$3,B1)=0),row(1,),

AD

IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(1,"",1))>0,pred(1),IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(2,"",2))>0,pred(2),

IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(3,"",3))>0,pred(3),IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(4,"",4))>0,pred(4),IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(5,"",5))>0,pred(5),

IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(6,"",6))>0,pred(6),IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(7,"",7))>0,pred(7),IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(8,"",8))>0,pred(8),

IF(COUNTIF({1,2,3,4,5,6,7,8,9},pred(9,"",9))>0,pred(9), "",akarlar(g1,sg1,1room,f1)))))))))))))))

The above formula checks if numbers 1 to 9 fit in the first cell. If none do, it copies the solver for the next cell (B1). Repeat this for all cells (except those with given numbers).

Testing Your Sudoku Solver

Once you've filled all cells with the solver formula, press F9 to see the solutions. If your solver's working, you should see a complete 9x9 grid.

Remember, Sudoku solvers are just the beginning. With this knowledge, you can explore more complex puzzles, like Killer Sudoku, or delve into game development with Excel.

Happy puzzling!

AD