Skip to main content

New Article

How to SUM by matching partial text in Excel

Post a Comment

How to Create A Checklist in MS Excel

This is 2019. You can use Mobile App in your Smart Phone to maintain a To Do List or Check List. But think you are in Office and you need to create a dynamic report in Microsoft Excel where a Check Box option should present. Or suppose you need to create a custom Check List before going to Office. You don't need to waste your time further if you found my article here.

Image 1: Checklist in Excel


What is a Check List?

Check List is a good way to check your step by step process properly to complete the task. It is the best way of every single task to track or monitor that you are going to finish your task successfully.


How to show or activate the Developer Tab?

First of all you need to show or activate the Developer Tab in your Excel. To activate or show the Developer Tab, you need to follow below steps:

Step 1: Click on Customize Quick Access Toolbar drop down icon and Select More Commands...
Image 2: Customize Quick Access Toolbar drop down list

Step 2: From Excel Options dialog box, Click on Customize Ribbon and Check on Developer. Then Click on Ok.

Image 3: Developer Tab

This will show the Developer tab in the your Excel.

Image 4: Developer tab shown or activated


How to create the Check List in Excel?

Step 1: Design the Check List in Excel file as per your requirement. I've designed my Check List as below image. Do not forget that, when you insert Check Box from Developer Tab, then choose Form Controls Check Box. Below image will show you clear instruction.

Image 5: Checklist Design and Checklist Inserting from Form Controls under Developer Tab

Step 2: Right Click on each Check Box in D Column and Click on Format Controls.

Image 6: Format Controls

Step 3: Under Format Control tab type Cell Link as A7. So that, if the Check box marked, then "TRUE" value will appear in A7 cell other wise if the Check box unmarked, then "FALSE" value will appear in A7 cell.

Step 4: By this way set the Cell Link of D8:D11 Check box as A8:A11 one by one.

Step 5: Now in D13 cell, use a IF formula like below, so that, If all 5 values of D7:D11 are marked, then it A7:A11 cell becomes TRUE. That means IF total TRUE value is 5 then all are checked and it will show a status: "All Done". The formula is in D13 cell which is:

=IF(COUNTIF(A7:A11,"TRUE")=5,"All Done","Pending")

This will looks like below image:

Image 7: Formatting of Check List

Step 6: Select A7:A11 and set Font Color as White. Then Hide the Grid line by Pressing ALT + W + V + G. Finally apply a Conditional Formatting on D13 cell that, If D13 equals to Value "All Done", then Select Fill Color as Green.

By this way you can create a check list and I hope the application of this Check Box will help you to create Dynamic Reports, Dashboard or Forms. This will also increase your Excel skill. Good Luck. 


|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
Labels:

Popular posts from this blog

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is...
Read more

Cumulative Closing Balance like a Bank Statement in PivotTable

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc...
Read more

Value Paste and Formula Paste

In many case, in our professional life, we need to do Copy and Value Paste or Copy and Formula Paste in all most always. Those employees, who works under MIS Department, have to do it lots of time in a single day to prepare reports. It is very time costing task in Office. Many of us use Excel Menubar to do it, others are using Keyboard Shortcut by pressing ALT key (like for Value Paste Press ALT+H+V+V and for Formula Paste Press ALT+H+V+F ). But did you noticed that, these two ways strongly need your attention to do this task. More clearly, if you choose Value Paste from Menubar , then what you need to do? First you should take the mouse in Hand Wheel the mouse in Home menu Then Click on Paste Then you pressure your eyes to find the Value Paste icon And then Click on it to paste it These all steps can take more than 1 second. Am I right? Below is an image of this process: Image 1: Value Paste from Home Menu On the other hand, if you choose the shortcut f...
Read more