Using Kanban boards for your projects is a great way to streamline the process and boost productivity. Whether working with a team or going solo, Kanban boards visually represent your progress, making it easier to manage.

This article provides a step-by-step guide on creating and using a Kanban board in Excel.

Creating a Backlog for Your Kanban Board

Let’s start by opening up a new Excel workbook. Once opened, you’ll find a tab named Sheet1 at the bottom of the workbook. Double-click the tab, and you’ll get a Rename Sheet pop-up. Rename the sheet to Backlog.

We’ll use this sheet to set up a backlog table of all the workflow tasks that’ll form the basis of our Kanban board.

To create a backlog, follow the steps given below:

1. Select the first few cells of the top two rows and merge them by clicking the Merge icon located inside the toolbar.
2. Now write Backlog inside the merged cells to give your table a title.
3. Adjust the font size, cell color, and title text alignment according to your liking.
4. Since we're creating a simple Kanban board, we’ll add two columns to our backlog (below the title): Task Title and Task Description. (For a more detailed backlog, simply add the required columns).
5. To add borders to the table, select 12 rows (or as many as needed) under the Task Title column. Go to the Borders icon (next to the Bold icon) and click the drop-down arrow. Select the Outside Borders option.

6. Do the same for the rows under the Task Description column.

Your backlog table is ready. All you need to do now is to fill it up, and you should have something like this:

Creating the Kanban Board

Now that we’ve got our backlog, it’s time to create the Kanban board we’ll use in our Kanban board. For that, click the plus sign at the bottom of the sheet to create and open a new sheet.

In the new sheet, let’s create a Kanban board by following the steps given below:

1. Create the board's title by merging the top cells and writing in the title. You can adjust the font size and cell color to your liking.
2. Since we’re aiming at simplicity here, we’ll only add three board sections, i.e., To Do, In Progress, and Done. (You can add more sections if your workflow has more phases).

3. Add thick borders to the Kanban board sections using the Borders icon.

The Kanban board is pretty much ready, so we’ll move on to creating the cards.

Creating Kanban Cards

Follow the steps given below to create Kanban cards:

1. Right-click the row heading and select Row Height.

2. A pop-up window will open where you need to enter the desired height; 25 in our case.
3. Similarly, adjust the row height of the row below it to 50 and the one below that to 25.
4. You’ll get a card consisting of 3 cells with the row heights as follows: 25:50:25
5. Select each of the three cells of the Kanban card and add borders by going to the Borders icon and selecting Thick Outside Borders.

6. Copy the newly created card to create as many cards as you like.
7. You’ll notice that the cell sizes will be set to default. So, you’ll have to adjust the cell sizes one by one.
8. To make it faster, press Ctrl and select all the cells that need to have a row height of 25. Now right-click any of the headings of the select rows and adjust the row height (as previously mentioned). All selected rows will now have a row height of 25.
9. Repeat the same process for all the cells that need a row height of 50.
10. Put a thick border around the area surrounding the cards and fill it with a background color to make it aesthetically pleasing.

Linking the Kanban Cards to the Backlog

To link the data in our backlog to the Kanban cards, follow the steps below:

Adding Task Titles to the Kanban Cards

To get the task title of our first task from the Backlog sheet:

1. Select the top cell (B5) of the first card and write =.
2. Go to the Backlog sheet and select the first cell (C4) under the Task Title column.
3. Go back to the Kanban Board sheet, and you’ll see the following formula:

   =Backlog!C4

4. Press and enter, and it’ll return the data in the cell (C4) of the Backlog sheet (that is, Research in our case).
5. Copy the formula =Backlog!C4 in the top cells of the remaining Kanban cards and change the cell numbers for each card. So the formula would be =Backlog!C5 (corresponding to the second task title) for the second card and =Backlog!C6 for the third task title, and so on. The Kanban cards should look as follows:

Using Conditional Formatting to Make the Cards Visually Pleasing

You’ll see that the top cells of all the cards now return the values of the Task Titles column from the Backlog sheet. However, a few cards (in our case, the last two) return 0 in their top cells because there is no value in the corresponding cells in the Backlog. To make the cards visually pleasing, we'll use conditional formatting in Excel to format data and remove the 0s:

1. Select all the top cells of the Kanban cards and go to Conditional Formatting > Set New Rule.

2. In the Conditional Formatting tab, select the cell value equal to 0, change the font color to white, and click done.

Adding Task Descriptions to the Kanban Cards

We’ll follow a similar approach to return the task descriptions from the Backlog to the middle cells of the Kanban cards. Only this time will the formulae be =Backlog!D4, =Backlog!D5, etc. Just like with task titles, the black cells from the Backlog will return 0, which you can fix through conditional formatting, as already mentioned.

Another issue you’ll notice when adding task descriptions to the Kanban cards is that the text will spill out of the cells. To prevent this, we'll wrap text in Excel cells:

1. Select all the middle cells of the cards.
2. Click the Wrap icon (next to the Align icon) on the toolbar.

Adding Final Touches

Even though the Kanban cards are ready (and linked to the backlog), here are a few extra steps to make them even better:

1. Use the bottom cell of the cards to (manually) add the task assignee.
2. Align the cell data at the center of the cell using the Align icon.
3. Remove the gridlines: Go to the toolbar, click the View section, and uncheck the box next to Gridlines to remove them.

Creating Moveable Kanban Cards

If you've followed the steps till now, you should have your backlog of tasks, Kanban board, and Kanban cards ready. But you won't be able to easily moves these cards across the board. Follow the steps below to make the Kanban cards moveable:

1. Copy the Kanban card you want to place on the board.
2. Right-click on the board where you want to place the card.
3. Go to Paste Special and select the Linked Picture option (which is the last option under Other Paste Options).

4. Follow the same procedure for other Kanban cards.

Maximize Your Efficiency Using Kanban Boards

Now that you have a basic structure of your Kanban board, it’s up to you to personalize it to suit your project requirements. A great way to maximize the potential of the Kanban technique is to combine it with another productivity strategy, such as the GTD method.