Sudoku with Power Query

Did you know Excel can fetch a brand-new Sudoku puzzle with a click of the refresh button? In this tutorial, we’re diving into how to tap into an online Sudoku API and use Power Query to turn that data into a styled and playable puzzle and solution right inside Excel.

Even if you’re not much of a Sudoku enthusiast, this exercise teaches some exciting Power Query techniques that demonstrate how to pull, transform, and format live web data. It’s a fun and educational way to practice data manipulation and custom worksheet formatting!

Video

Overview of the Process

Here’s a quick summary of what we’ll accomplish:

• Connect to an online Sudoku puzzle API using Power Query
• Transform the raw data into a grid format for puzzles and solutions
• Apply custom formatting to make the table interactive and visually appealing
• Refresh the puzzle anytime with one click

Step-by-Step Guide: Build a Live Sudoku Puzzle in Excel

Step 1: Get Data from the Sudoku API

Go to Data > Get & Transform Data > From Web

Paste the following URL into the dialog box: https://www.youdosudoku.com/api

Click OK to load the data into Power Query Editor

Tip: This URL fetches a new Sudoku puzzle each time it refreshes.

Step 2: Convert API Results into a Table

In Power Query Editor, choose Convert To Table (use default settings)

Click Close & Load To and select Table:

Step 3: Extract and Format the Puzzle

From the Queries & Connections pane, right-click the original query and choose Reference. This starts a new query that uses the results of the api query as its data source:

Note: the values are different than the previous screenshot because the api refreshed.

Filter the data to only rows where the first column = puzzle

Remove the first column so only the puzzle string remains

Use Split Column > By Number of Characters, and select 9, repeatedly, and be sure to split into rows:

Click OK and:

Power Query automatically converts the data type from text to number. This has the unintended consequence of removing leading zeros. Since we need to retain them for now, just remove the Changed Data Type step by clicking the x in the applied steps listbox. Better:

Then split each row into 1-character chunks into columns by once again using the Split Column command.

Rather than returning 0 to the cells, we want them to be blank. To do this, we select all columns and use the Replace Values command. We change all 0 values with null (all lowercase):

Click Close & Load To and choose to load the puzzle table to a worksheet cell:

Step 4: Do the Same for the Solution

Repeat the same sequence of steps for the solution.

1. Reference the original API query again
2. Filter to rows where the first column = solution
3. Remove unnecessary columns
4. Repeat the same split into rows and columns steps without replacing values
5. Load to a new table in the worksheet

Step 5: Apply Formatting

Now we just need to format the resulting tables to look more like a traditional sudoku puzzle.

• Hide the table header row: Table Design > Header Row (uncheck)
• Change table style to your preferred style (None is what I used): Table Design > Table Styles > None
• Prevent the column widths from changing on refresh: Table Design > Properties > Adjust column widths (uncheck)
• Select the puzzle grid range and do the following:
  • Apply All Borders under Cell Borders
  • Center the text both vertically and horizontally
  • Adjust row height (40 works well)
  • Apply thick borders around each 3×3 block to emphasize Sudoku structure

Repeat the formatting steps for the solution table:

Summary

In just a few steps, we’ve unlocked the ability to pull a fresh Sudoku puzzle and its solution using a live web API … all directly within Excel. We manipulated string data with Power Query, split rows and columns efficiently, and applied custom formatting to deliver an engaging presentation. Now, every time we click Data > Refresh All, Excel fills in a brand-new game ready to play!

Thanks for following along! And remember, the journey to Excel mastery is achieved one click at a time!

Download the Example Workbook

Frequently Asked Questions

Can this technique work with other types of puzzles or data?

Absolutely. The same Power Query techniques apply to any well-structured API. Simply modify the column splitting logic for the data you’re working with.

How do I stop column widths from changing every time I refresh?

Go to the Table Design tab, select Properties, and uncheck Adjust column width on refresh.

Why replace zeros with null instead of just leaving them?

Excel displays null values as blank cells, which makes the puzzle easier to read and looks cleaner than zeros in the grid.

What if the API structure changes?

If the API changes, the Power Query steps might break. In that case, we’d need to re-evaluate the web structure and adjust the query steps accordingly.

What version of Excel is required?

Power Query is available in Excel 2016 and onward. For earlier versions, add-ins may be required.

What happens when I share this workbook?

If shared with others, they’ll need internet access to refresh the data. Otherwise, the current puzzle will remain static.

Can I highlight or validate user answers?

Yes, you can set up a conditional formatting rule that sets the format to green fill if the cell value is equal to the corresponding cell value in the solution table.

Can I remove gridlines from the worksheet entirely?

Yes, go to View > Show and uncheck Gridlines. Rely on cell borders for structure.