Home>Blogs>Power Pivot>KANBAN Board with Power Pivot and VBA for Project Management
KANBAN Board using Power Pivot
Power Pivot Dashboard

KANBAN Board with Power Pivot and VBA for Project Management

In today’s fast-paced business environment, effective project management is essential for achieving success. One tool that has gained significant popularity in recent years is the KANBAN board. In this comprehensive guide, we will explore the process of creating a KANBAN board using Power Pivot and VBA, allowing you to automate your project management dashboard and streamline your workflow.

Understanding the KANBAN Board

A KANBAN board is a visual representation of a project’s workflow, illustrating the various stages of tasks and their current status. It enables teams to manage and monitor project progress more efficiently, identify bottlenecks, and ensure timely completion of tasks. There are three primary stages in a KANBAN board: Pending, In-Progress, and Completed.

Setting up the Data Model

Before building our KANBAN board, we need to set up the data model. We will use the following columns:

  1. Task_Id
  2. Task Category
  3. Task Subcategory
  4. Task Name
  5. Status (Pending, In-Progress, Completed)
  6. Assigned To
  7. Due Date
  8. Last Update Date
Data Sheet
Data Sheet

To convert the range to a table and add it to the Power Pivot data model, follow these steps:

  • Select the data range.
  • Press Ctrl+T to convert the data range into a table.
  • Name the table (e.g., ‘Table1’).
  • Click on the Power Pivot tab in the Excel ribbon.
  • Select ‘Add to Data Model’ to import the table into Power Pivot.

Creating a Measure in Power Pivot

Measures in Power Pivot enable us to perform calculations on our data. In this example, we will create a measure called ‘Task_ID_And_Task_Name‘, which concatenates the Task_Id and Task Name columns, separated by a period.

To create this measure, follow these steps:

  • Open the Power Pivot window by clicking ‘Manage’ in the Power Pivot tab.
  • Select the ‘Table1’ tab.
  • In the formula bar, type the following DAX formula:
Task_ID_And_Task_Name = CONCATENATEX(Table1,Table1[Task_Id]&"."&Table1[Task Name],UNICHAR(10))
  • Press Enter to create the measure.

 

Visualizing Data with Charts

Now that we have our measure in place, we can create visual representations of our data using charts.

Creating a Pie Chart

Create a new worksheet to serve as a support sheet for the pivot table.

Insert a pivot table and use the ‘Status’ column as the row field and the ‘Count of Status’ as the value field.

Status Pivot Table
Status Pivot Table

Create a 3D pie chart using the pivot table data, with red, yellow, and green colors representing Pending, In-Progress, and Completed tasks, respectively. Display percentages in the data labels and include a legend.

3D pie chart
3D pie chart

Creating a 100% Stacked Bar Chart

In the support sheet, create a new pivot table with ‘Assigned To’ as the row field, ‘Status’ as the column field, and ‘Count of Status’ as the value field.

Assigned To Pivot table
Assigned To Pivot table

Create a 100% stacked bar chart using the pivot table data, using the same color scheme as before (red, yellow, and green).

100% stacked bar chart
100% stacked bar chart

Adding Slicers and Timelines

Slicers and Timelines
Slicers and Timelines

To make our KANBAN board more interactive, we will add an ‘Assigned To’ slicer and a ‘Due Date’ timeline. These filters will enable users to narrow down tasks based on their assignee and due date.

Building the KANBAN Board Pivot Table

KANBAN Board Pivot Table
KANBAN Board Pivot Table

With the data model, measure, and charts set up, we can now build the KANBAN board pivot table. Here’s how:

  • On a new worksheet, insert a pivot table.
  • Use the ‘Task Category’ and ‘Task Subcategory’ as row fields.
  • Use ‘Status’ as the column field.
  • Use the ‘Task_ID_And_Task_Name’ measure as the value field.
  • Apply formatting to the pivot table cells, using the same color scheme: red for Pending tasks, yellow for In-Progress tasks, and green for Completed tasks.

This KANBAN board pivot table provides a quick overview of tasks in different stages, making project management more effective and streamlined.

Adding Interactivity with VBA

We can further enhance our KANBAN board by adding interactive buttons that allow us to edit tasks and move them between stages.

Buttons
Buttons

Edit Record Button

The Edit Record button navigates to the data sheet for the selected task. The VBA code for this button is as follows:

Sub Edit_Record()

'Define the worksheet and range

Dim dsh As Worksheet

Set dsh = ThisWorkbook.Sheets("Data")

Dim rng As Range

Set rng = ActiveCell




'Initialize task_id and row_num

Dim task_id As String

Dim row_num As Integer

row_num = 0




'Handle any errors

On Error Resume Next




'Check if the active cell is within the KANBAN board

If rng.Column >= 4 And rng.Column <= 6 Then

    If rng.Row >= 10 Then

        If rng.Value <> "" Then

            'Extract the task_id from the active cell

            task_id = VBA.Left(rng.Value, VBA.InStr(rng.Value, ".") - 1)

           

            'Find the row number of the task in the data sheet

            If VBA.IsNumeric(task_id) Then

                row_num = Application.WorksheetFunction.Match(CInt(task_id), dsh.Range("A:A"), 0)

            End If

        End If

    End If

End If




'If no task is selected, default to the first row

If row_num = 0 Then row_num = 1




'Select the row of the task in the data sheet

dsh.Activate

dsh.Range("A" & row_num).EntireRow.Select

End Sub

Move to Next Stage Button

The Move to Next Stage button moves the selected task to the next stage (e.g., from Pending to In-Progress or from In-Progress to Completed). The VBA code for this button is:

Sub Move_To_Next_Stage()

'Code is similar to Edit_Record until finding the row_num




'Change the status of the task based on its current status

If dsh.Range("E" & row_num).Value = "Pending" Then

    dsh.Range("E" & row_num).Value = "In Progress"

ElseIf dsh.Range("E" & row_num).Value = "In Progress" Then

    dsh.Range("E" & row_num).Value = "Completed"

ElseIf dsh.Range("E" & row_num).Value = "Completed" Then

    MsgBox "Already Completed"

End If




'Update the Last Update Date and refresh the workbook

dsh.Range("H" & row_num).Value = Now()

ThisWorkbook.RefreshAll

End Sub

Move to Previous Stage Button

Similarly, the Move to Previous Stage button moves the selected task to the previous stage (e.g., from Completed to In-Progress or from In-Progress to Pending). The VBA code for this button is:

Sub Move_To_Previous_Stage()

'Code is similar to Move_To_Next_Stage until finding the row_num

'Change the status of the task based on its current status

If dsh.Range("E" & row_num).Value = "Completed" Then

dsh.Range("E" & row_num).Value = "In Progress"

ElseIf dsh.Range("E" & row_num).Value = "In Progress" Then

dsh.Range("E" & row_num).Value = "Pending"

ElseIf dsh.Range("E" & row_num).Value = "Pending" Then

MsgBox "Already Pending"

End If


'Update the Last Update Date and refresh the workbook

dsh.Range("H" & row_num).Value = Now()

ThisWorkbook.RefreshAll

End Sub

Conclusion

By combining the power of KANBAN boards, Power Pivot, and VBA, we have created an effective project management dashboard. This dynamic tool provides a comprehensive view of project tasks, allows for easy task edits, and supports efficient task progression management. With the addition of interactive charts and filters, managing projects becomes a breeze, leading to improved productivity and project success. Try creating your own KANBAN board today, and experience the benefits firsthand!

Advantages of KANBAN Board using Power Pivot and VBA

Improved Visibility and Organization:

KANBAN boards help visualize the workflow, making it easier to see the progress and status of each task. This enhances team communication and collaboration.

Increased Productivity:

The clear visualization of tasks and their status allows teams to identify bottlenecks and prioritize work, leading to better time management and increased productivity.

Enhanced Flexibility:

KANBAN boards can be easily customized to fit the specific needs of any project, making them a versatile project management tool.

Real-time Tracking:

The dashboard updates in real-time, ensuring all team members have the most accurate and up-to-date information on project progress.

Scalability:

Power Pivot and VBA can handle large amounts of data, making this solution suitable for managing small or large projects.

Opportunities for Improvement in KANBAN Board using Power Pivot and VBA

Integration with Other Tools:

Enhance the dashboard by integrating it with other project management or collaboration tools, such as Microsoft Teams, Slack, or Trello, to streamline workflows.

Automation:

Incorporate more automation features to reduce manual work and further increase productivity.

Enhanced Reporting:

Develop more sophisticated reporting and analytics capabilities to provide deeper insights into project performance.

Mobile Compatibility:

Create a mobile-friendly version of the dashboard for easy access and updates on the go.

Best Practices for KANBAN Board using Power Pivot and VBA

Regularly Update Task Status:

Encourage team members to update task status frequently to maintain accurate and up-to-date information on the KANBAN board.

Limit Work in Progress (WIP):

Set WIP limits for each stage to prevent bottlenecks and ensure a smooth workflow.

Prioritize Tasks:

Clearly prioritize tasks based on their importance, deadline, or dependencies to optimize team productivity.

Use Color Coding:

Utilize color codes to differentiate between task categories, status, or assignees for easy visual identification.

Review and Adjust:

Regularly review the KANBAN board, identify areas for improvement, and make adjustments to optimize team performance.

Frequently Asked Questions (FAQs)

Q. Can I customize the KANBAN board to fit my specific project needs?

Yes, the KANBAN board created with Power Pivot and VBA can be easily customized to fit the unique requirements of any project.

Q. Can I integrate the KANBAN board with other project management tools?

While this specific dashboard may not have out-of-the-box integrations, it is possible to develop custom integrations with other tools, depending on your needs and technical expertise.

Q. Is there a limit to the number of tasks or projects that can be managed using this KANBAN board?

Power Pivot and VBA can handle large amounts of data, making this solution suitable for managing a wide range of project sizes. However, performance may be affected if the data size becomes too large.

Q. How secure is the data stored in the KANBAN board?

The security of your data depends on the settings and policies in place within your organization and the software used. It’s essential to follow best practices for data security, such as regularly updating passwords and limiting access to sensitive information.

Q. How can I share the KANBAN board with my team members?

You can share the KANBAN board by providing access to the Excel file or by integrating it with collaboration tools used by your team. Always ensure that you follow your organization’s security policies when sharing sensitive information.

 

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step-by-step video tutorial:

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com