Table of Contents
If you have a list of tasks for any project, or a long To-Do list it’s good to know the percentage of completion of your Tasks in a visual way
In this article, I show you how to create a Progressive Data Bar by using Form Controls, functions and Conditional Formatting.
Here is my finished project
Preparation Steps
- Let’s start a new Website development project and write down the list of tasks in column B (B6:B13)
- Select Cells B4:C4 and click Merge and Centre on the Home Tab
NB: “Centre Across Selection” will not work for this functionality
- I also inserted 2 pictures for a “Reset” and “Select All” buttons, to be used later
Add the Developer Tab to the Ribbon
Right Click any Tab and select Customize the Ribbon ► In the Excel Options Dialog Box ► Check the box for Developer ► Hit OK
Adding a Check Box Form Control
On the Developer Tab ► In the Controls Group ► Click on the Down Arrow for “Insert” ► Select “Check Box” (third icon in the Top Row).
- Move the mouse pointer (crosshair) over cell B6 and drag to create a Check Box
- Right click the Check Box ► Select “Edit Text” ► Delete the text to side of the Check Box
- Deselect the Check Box then press CTRL and Click on it one more time.
- In the Controls Group on the Developer Tab click : “Properties”
- In the properties dialog box ► Cell Link ► Click on Cell B6 ► Ok
Copying the Check Box to all other cells
- Select Cell B6 and drag down from the AutoFill Handle to copy the Check Box to all other tasks in the list.
- From the Options Tag ► Select ‘Fill Without Formatting”, to get rid of the lines.
- All Check Boxes copied are linked to the same cell B6. We need to change that.
- Select the second Check Box while pressing CTRL and Click “Properties”. In the Cell Link box ► Select Cell B7.
- Repeat for all other Check boxes, linking each one to the cell upon which it is sitting.
- Checking a Box returns TRUE (=1) in the cell underneath.
- Unchecking a Box returns False (=0) in the cell underneath.
- Test then select the range B6:B13 (adjust to your range) and change the Font Color to White (to hide the TRUEs & FALSEs)
Adding Conditional Formatting
Select the Merged cell B4:C4 and create an Average Function:
=AVERAGE(B6:B13/1
NB: The division by 1 converts it to a percentage.
- On the Home Tab ► Format as %
With 2 boxes Checked I get 25%
With the same 2 cells selected(B4:C4) ► Click on Conditional Format (Home Tab) ► Data Bar ► Solid Fill ►Blue
We see a Blue Bar filling the 2 Cells.
- Now click on Conditional Format ►Manage Rules ► edit Rules.
- In the Edit Formatting Rules Dialog Box
- Change “Type” to “Number” for both Minimum & Maximum.
- Then set the Value to 0 for Minimum and 1 for Maximum.
- Hit OK twice and now test by checking and unchecking boxes.
Next Level
Adding a Reset & Select All Functionality:
- Switch to the Visual Basic editor ALT + F11
- Insert a New Module ALT + I + M
- In the blank window of the module copy and paste these 2 codes:
Sub Reset()
For Each cb In Sheet1.CheckBoxes
cb.Value = xlOff
Next
End Sub
Sub SelectAll()
For Each cb In Sheet1.CheckBoxes
cb.Value = xlOn
Next
End Sub
- Close the Visual Basic window
- Select the Reset button ► Right Click ►Select Assign Macro ► “Reset” ►OK
- Repeat for the “Select All” button and assign the “SelectAll” macro
- Test the functionality of these 2 buttons and their effect on the Progressive Data Bar
Download the Exercise File and Follow along
Enjoy your project.
