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

Fiverr Microsoft Excel 2016 Skill Test and Answers 2021

This video will help you about preparing Fiverr Excel Skill Test. I've just taken this Fiverr Excel Skill Test and successfully passed with a scored 6 out of 10. I'm sharing my Fiverr Excel Skill Test and Answers 2021 video so that you can prepare yourself if you would like to give a test on Fiverr.    I have taken the Fiverr Excel Skill Test on Fiverr and obtained 6 out of 10 scores. If you take a test and pass, it will greatly help the buyer to trust you and your skills. Please note that your exam will not be exactly the same as mine because there are many more questions in the pool than 40 which appeared in my test. But I am hopeful that you will succeed in it. If you have any queries, please write them in the comment section. I will try my best to guide you.Follow this video and try to get a general idea. By following the instructions, you can successfully pass the test.    After passing the Fiverr Excel Skill Test, Fiverr shared a Excel Expert Badge on my Profile, so that
2 comments
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