Most individuals see Excel as rows, columns, and limitless formulation. I see a clean canvas for organizing chaos. So I constructed a Kanban board in it, full with standing monitoring, colour coding, and simple updates.
Constructing My Kanban Board From the Floor Up
I constructed my Excel Kanban board utilizing two sheets: one for the duty checklist (the brains of the operation) and one for the board itself (the sheet everybody sees). Although the duty checklist powers the board, I began by designing the board first, as a result of I wished to see the place I used to be headed.
I used the primary two rows for my board’s heading. That is the place you possibly can add your mission identify, a fast instruction (like “Add duties within the Job Listing tab”), and any keys it is advisable make the board simple to learn.
Screenshot by Ada
I popped the board identify into Row 1, inserted a heading for my activity playing cards in Row 2, and dropped in a brief word telling folks to not edit the playing cards immediately, additionally in Row 2. Then, I stuffed these prime two rows with my most popular shade of inexperienced, adjusted the font and textual content colour, and usually made it seem like one thing I’d truly need to use.
2
Create Job Playing cards
My go-to card structure is three rows tall: a slim header for the ID, a tall center for the duty identify, and a slim footer for the assignee.
I skipped Row 3 after the heading rows and used three rows throughout 5 columns to create two activity blocks. Rows 4 and 6 are slim, and Row 5 is tall. Columns A, C, and E are skinny, whereas Columns B and D are broad, which is ideal for holding the precise playing cards.
Listed below are the measurements I used:
Slim columns
3 factors broad
Vast columns
30 factors broad
Row 3
25 factors excessive
Clean rows between the playing cards
10 factors excessive
Slim rows for the playing cards
20 factors excessive
Tall rows for the playing cards
60 factors excessive
To regulate row top or column width, choose the rows and columns you need, right-click, and select Column Width or Row Top.
Screenshot by Ada
You don’t have to make use of the identical numbers as I used, however you want neat, readable playing cards that don’t take up your entire display.
3
Add Borders
Borders will make the playing cards seem like precise activity playing cards. I chosen B4–B6, opened the border menu (Residence tab > Borders menu icon), clicked Thick Exterior Borders, and did the identical for the matching cells in Column D.
I additionally added borders to the center activity title cells (e.g., B5 and D5) for additional definition.
If you happen to want extra playing cards, copy the construction downward, leaving a clean row between every. After copying them down, regulate the row heights as wanted. Your activity playing cards are all able to go, and you may at all times add extra as the necessity arises.
4
Set Up Workflow Columns
I designed 4 columns to characterize 4 phases of labor: Drafting, Enhancing, Scheduled, and Revealed. Since my activity playing cards are 30 factors broad, I set the stage columns to not less than 35 factors so the playing cards can match comfortably.
Screenshot by Ada
From there, it was simply formatting—adjusting font measurement, centering the textual content, choosing background colours, and including borders round every stage column. I additionally stuffed the clean background areas round my activity playing cards with a contrasting colour and hid Excel’s gridlines (Web page Format tab > Gridlines > Uncheck View).
Screenshot by Ada
The columns are all broad sufficient for activity titles, slim sufficient to suit on one display, and every card is readable at a look. And that’s what I had in thoughts whereas designing the board.
5
Make the Board Self-Replace
The actual magic of this Excel Kanban board is that the playing cards replace routinely whenever you change one thing in your activity checklist. For this to work, I created a sheet referred to as Job Listing Sheet with 4 columns: Job ID, Title, Assignee, and Description.
Screenshot by Ada
This sheet is the grasp sheet. Each card on the Kanban board will pull its data from right here. Mainly, every card is just a bit show window into the grasp sheet. As an illustration, the highest cell of a card hyperlinks to a Job ID (=’Job Listing Sheet’!A3), the center cell to a title (=’Job Listing Sheet’!B3), and the underside cell to an assignee (=’Job Listing Sheet’!C3), all from one row within the grasp sheet.
Screenshot by Ada
In your case, merely substitute “Job Listing Sheet” with the precise identify of your personal grasp sheet. I am simply utilizing that identify right here as a result of that’s what mine is titled.
I formatted all of the cells in my activity playing cards such that the textual content is centered each vertically and horizontally, and turned on Wrap Textual content to maintain longer titles neat. You may make all these modifications by right-clicking the cells and clicking Format Cells > Alignment.
Screenshot by Ada
While you first hyperlink every thing, any empty row within the Job Listing Sheet will present up on the board as a zero, and that’s not precisely fairly.
Screenshot by Ada
Right here’s how I mounted it, utilizing Conditional Formatting in Excel:
- Choose all the cardboard cells you need to clear up (maintain Ctrl to pick a number of without delay).
- Go to Residence > Conditional Formatting > New Rule.
- Select Format solely cells that comprise.
- Within the rule description, set Cell Worth equal to 0.
- Click on Format, set the font colour to white, and hit OK twice.
Screenshot by Ada
Screenshot by Ada
Now, any unused card will look clean till you add a activity to the Job Listing Sheet. As soon as the hyperlinks are all set, strive enhancing a activity title in your grasp sheet. The corresponding card in your board will replace instantly.
6
Make Excel Playing cards That Can Truly Transfer Round
The actual problem with an Excel Kanban board isn’t simply creating the playing cards; it’s making them transfer with out breaking their connection to your activity checklist. That’s the place Excel’s Linked Image function is available in.
Begin by deciding on all of the cells that make up one activity card. Press Ctrl + C to repeat. Proper-click within the column the place you need the cardboard to look, then go to Paste Particular and select Linked Image (I). It’s normally the final choice beneath Different Paste Choices.
Screenshot by Ada
This creates a floating picture of your authentic card. It might look just a little clear at first, however that’s regular and simple to repair.
To provide the image a strong background, right-click it, select Format Image, click on the Fill icon (the primary icon), and choose a colour that matches your design.
As a result of the linked image is a picture, you possibly can merely drag it from one column to a different (e.g., Drafting to Scheduled) as the duty progresses.
Additionally, because the linked image is tied to the unique card (which is tied to your grasp sheet), any modifications to the supply knowledge, like assigning a brand new crew member, will seem routinely on each linked image of that card.
7
Preserve Your Board to Hold It Working
The actual take a look at of a DIY Excel Kanban board is whether or not it nonetheless works six months later, when your mission has ballooned to dozens (and even tons of) of duties. To maintain mine easy and frustration-free, I added just a few safeguards:
- A easy word on the prime of the board reminds my crew to edit solely the grasp sheet and by no means the playing cards immediately.
- “Drafting” because the identify of a piece stage labored for me, however you possibly can name your columns something that matches your workflow. And in case you want much more columns later, you possibly can simply add them.
- The linking course of works for as many playing cards as you want. You may repeat it time and again with out breaking the system.
- I turned off gridlines on the board for a cleaner, app-like look, however saved them on within the Job Listing sheet to make knowledge entry simpler.
These small steps would possibly really feel like overkill for a tiny mission, however they’re what make the board a dependable, low-maintenance different to devoted desktop Kanban instruments. I can monitor a fancy workflow completely in Excel, with drag-and-drop motion, stay updates, and nil reliance on additional software program.
What began as an experiment has change into my go-to system. With a little bit of planning, some intelligent linking, and a handful of Excel tips, you possibly can construct a visible workflow that’s each bit as purposeful as a devoted app—and it received’t price you a factor.
