How to create a Kanban table in Excel

 

Using Kanban boards for your projects is a great way to streamline processes and increase productivity. Whether working alone or with a team, the Kanban board clearly shows your progress, making it easy to manage.

Create backlog for Kanban board

Start by opening a new Excel workbook. Once opened, you'll see the Sheet1 tab at the bottom of the workbook. Double click on this tab, you will see a Rename Sheet pop-up . Rename sheet to Backlog .

We will use this sheet to set up a backlog of all the tasks in the workflow to make up the Kanban board.

To create a backlog, follow the steps below:

1. Select the first few cells in the top two rows and merge them by clicking the Merge icon inside the toolbar.
2. Now write Backlog inside the merged cells to title the table.
3. Adjust font size, cell color and title alignment to your liking.
4. The article explains how to create a simple Kanban board, so we will add two columns to the backlog: Task Title and Task Description .
5. To add borders to the table, select 12 rows in the Task Title column . Go to the Borders icon and click the drop-down arrow. Select Outside Borders .
6. Do the same for the rows in the Task Description column .

The backlog table is ready. Now all you need to do is fill in the data. Here is an example:

Create Kanban board

Once we have the backlog, it's time to create the Kanban board. Click the plus sign at the bottom of the table to create and open a new sheet.

In this new sheet, create a Kanban board by following these steps:

1. Create a table name by merging the top cells and writing the title. You can adjust the font size and cell color as you like.
2. Since the example focuses on simplicity, only 3 table sections are added: To Do , In Progress and Done .
3. Add thick borders to Kanban board sections using the Borders icon .

The Kanban board is ready, let's move on to card creation.

Create Kanban Cards

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

2. A pop-up window will open. Here you need to enter the desired height. The example is 25.

3. Similarly, set the height of the row below it to 50 and another row below it to 25.

4. You will have a card containing 3 cells with the following row height: 25:50:25.

5. Select each of the 3 cells of the Kanban card and add a border by going to the Borders icon and selecting Thick Outside Borders .

6. Copy the newly created tag to create as many tags as you want.

7. You will see the cell size is set by default. Therefore, you have to resize each cell in turn.

8. To do this faster, press Ctrl and select all cells that need a row height of 25. Now right click on any header of the selected row and adjust the row height. The entire selected row will now have a row height of 25.

9. Repeat this process for all cells that need a row height of 50.

10. Place a thick border around the area around the card and fill it with a nice background color.

Linking Kanban Cards to Backlog

To link the data in the backlog to the Kanban tag, follow these steps:

Add task title to Kanban card

To get the name of the first task in 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) in the Task Title column.

3. Go back to the Kanban board sheet and you will see the following formula:=Backlog!C4

4. Click and enter, it will return the data in cell (C4) of the Backlog sheet.

5. Copy the formula =Backlog!C4 in the top cells of the remaining Kanban cards and change the number of cells for each card. The formula here would be =Backlog!C5 for the second tag and =Backlog!C6 for the third task name, and so on. The Kanban card will look like this:

Use conditional formatting to make cards look good

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

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

Added a quest description for the Kanban card

The steps are the same as when writing the task description from the Backlog for the middle cells of the Kanban card. The formula here would be: =Backlog!D4, =Backlog!D5… Like the task name, the cells would be: =Backlog!D4, =Backlog!D5… black from Backlog will return 0. You can fix this value through conditional formatting as mentioned above.

Another problem you'll notice when adding a task description to a Kanban card is that the text will overflow the cell. To prevent this, 'pack' the text in Excel cells:

1. Select all the cells in the center of the card.
2. Click the Wrap icon (next to the Align icon) on the toolbar.

Final Edit

1. Use the box at the bottom of the card to add the person assigned the task.
2. Align cell data in the center of the cell using the Align icon .
3. Remove gridlines: Go to the toolbar, click View and uncheck the box next to Gridlines to remove them.

If you want to create a movable Kanban card:

1. Copy the Kanban card you want to place on the board.
2. Right-click the board at the location where you want to place the card.
3. Go to Paste Special and select Linked Picture .
4. Do the same with other Kanban cards.

Above is how to create a Kanban table in Excel . Hope the article is useful to you.