How to Use Checkboxes to Create Checklist Template in Excel

 

In this article, you will learn how to insert check boxes to create the checklist template. We are going to create the wedding checklist and budget planner. When we plan to make or purchase something, we keep few things in our mind, such as:-

Why do we need it and what is the purpose?

How can we use of it?

What are the benefits of wedding checklist?

Why we need it and what is the purpose?

For everyone, wedding is the most important celebration of life. To celebrate, we have to organize and manage so many things. Sometimes, due to lot of things, we forget few things, and to avoid this kind of mess, we need to create the wedding checklist.

How can we use of it?

We can use it as per our budget and to check whether everything has been covered or not.

What are the benefits of wedding checklist?

We will be able to track each and everything to manage the budget and will not forget anything.

 

image 1.1

 

To prepare the wedding checklist, we need item list for wedding preparation, occasion, budget and checklist.

  1. Items & Occasion:- Below are the list of Main Items and Occasions:-

image 2

 

  1. Budget & %age of Items and Occasion need to spend: -Below is the list of Budget

image 3

 

Let’s continue with items:-

Step 1:-For every item, we have to define the sub items in which we will include each and everything. We have prepared the list for each item and occasion:-

image 4

 

Step 2:-

In front of list, enter the Estimated budget, Actual budget and Checklist:-

For example: -We have Apparel list in range C6:C26.

  • Insert 3 columns: first for estimated budget, second for actual money spent and third for Checks
  • In cell D6, we will have the estimated budget which is linked to Sheet 2 where we have mentioned the estimated budget

image 5

 

  • Actual would be the sum of actual range =SUM(E7:E25)

image 6

 

  • Insert the check boxes by following below steps:-

Go to Developer tab > Controls group > Insert > Check box (form control)

 

image 7

 

  • After inserting the check box, right click with the mouse on check box

image 8

 

  • After inserting the check box, right click with the mouse on check box, pop up will appear

image 9

 

How to make a checklist?

  • Click on Edit text and delete the name of check box

image 10

 

  • Again, right Click on check box with the mouse and click on Format control from the pop-up
  • Format Control dialog box will appear
  • In Color and lines tab > Select the color for check box from fill color

image 11

 

  • In Color and lines tab > Select the color for check box

image 12

 

  • Once more, right click on the check boxes > Format Control > Control

image 13

 

  • Link the cell with cell G7. When we check and uncheck the checkboxes, linked cell will change into true and false

image 14

 

  • Click on ok.

image 15

 

  • Now, copy the check box in the range and change the linked cell for every check boxes

image 16

 

image 17

 

  • According to the check boxes, we will return total no. of checkpoints completed and number of things that are left

image 18

 

  • Enter the formula for total nos:- =COUNTA(G7:G25)

image 19

 

  • Enter the formula for total Completed:- =COUNTIF(G7:G25,”True”)

image 20

 

Note: When we check the check box then linked cell will show true and, on uncheck, result would be false.

  • To return pending no. we will subtract completed numbers from total numbers

image 21

 

This table will be helpful to track how many items are pending in the list.

Now, we will put the Conditional Formatting on pending cell, if number of pending cells is zero, then cell color would be highlighted in green color; in case of greater than zero, it will be highlighted in red color.

Follow the steps below:-

  • Select the cell of Pending numbers
  • Go to Home tab > Conditional Formatting >New rule

image 22

 

  • New formatting rule dialog box will appear >Use a Formula to determine which cells to format >Enter the formula in format values box> =$F$31=0
  • Click on Format > Format cells dialog box will appear > Fill tab > Select green color

image 23

 

  • Click on OK

image 24

 

  • For second, follow the same process
  • Home Tab > Conditional Formatting >New formatting rule dialog box will appear > Use a Formula to determine which cells to format > Enter the formula in format values box > =$F$31>0
  • Click on Format > Fill Tab > Choose Red color > Click on ok > Click on ok

image 25

 

Note: – If you forgot any item, then this cell will be helpful to remind you that the items are still pending in the checklist.

 

image 26

 

Now, we will do the same thing for every checklist and then our wedding checklist will get prepared.

Key take away points:-

  • After reading this article, you will be able to create quick checklist by your own
  • You can keep record of office or home budget after creating the checklist template
  • You can also track the pending items basis on the checklist

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 



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=""> <strike> <strong>