...

Progressive Data Bar For Your Tasks

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.

Watch the tutorial on my YouTube Channel

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Seraphinite AcceleratorOptimized by Seraphinite Accelerator
Turns on site high speed to be attractive for people and search engines.