You can size the check-box box perfectly to the cell by holding the ALT key while dragging the handles. The edges will snap to the confines of the cell. Blew my mind when I learned this trick. I have used a few before but did not understand what I was doing but now it is much clearer, thank you.
ALT+ Dragging the check box handles does not work ( for ME) for resizing the check box. It jus changes the size of the selection box around the check box without resizing the checkbox itself.
@@Catv47 Hmmm, it sounds like you are unable to select the check box. Try resizing the selection box to either smaller or larger, click away from the box and then retry selecting the check box for resizing.
I'm an Excel junkie, but Google Sheets' checkbox feature blows Excel out of the water. Excel is beyond laborious when it comes to checkboxes. It's so much easier to do all this in Sheets. I really hope Excel ups their game soon.
Wow, Jon you really know how to explain these things. Better than anyone I've seen. I am so impressed at how you present the material. I am now understanding how to do this.
Hello and Good day, Could you make a video on your first example (the text next to the checkbox when pressed turns gray) that links to a cell in which if you selected a certain range on a drop down list, it shows multiple checkboxes. Now if you selected another range on the same drop down list, it shows another set of checkboxes.
Thank you. However, if I wanted to make a checklist for me and my co workers like this, is there a way for the 'time' to be displayed by it so I know the time of completion?
I have used this technique on my guidelines on how to use my excel file. Additionally, I also link the guideline where when the user click on the next step it automatically skips to the right worksheet tab it needs to be.
This great video , its exactly what I was looking for . I have one question. Is it possible to keep the cells locked until the cell above is not ticked to create a step by step process. So when 1st cell is ticked , second gets unlocked , second is ticked , thirst get unlocked. Is that possible ? Could you please provide some guidance on that please?
Hi! New subscriber. What I'm looking the internet for is, how can I highlight a cell if the checkbox it's linked to is "False" after a certain date? If the context helps I have a table that has each month as a row, and each cell in that row has a review for an employee that's due in that month. (For example in the January row it may have 1 cell that has Sally, another cell that has John, etc.) If Sally's review didn't get completed in January when it's due (the box is linked to a checkbox), then I want conditional formatting to highlight Sally's cell if the box isn't checked by February 1st. I hope this makes sense!! I feel so close it's annoying haha. This one I think feels the closest. I'm essentially saying If this cell ($AC4) is FALSE, meaning the checkbox it's correlated to is unchecked, AND it's past the month it's due, then the cell will turn red for me. =IF($AC4)=FALSE"&">MONTH(TODAY())
Great video Jon! I am adding this to my project management Excel template right now and I am going to share it with my network! Do you know why the conditional formatting dialog box will not keep a defined name selection from the F3 Paste Name dialog box?
Thanks Eric! I'm happy to hear you are putting this technique to use. 👍 Great question on the named range references for conditional formatting. Unfortunately, this is a limitation of Excel/conditional formatting. Here is a post on the Excel UserVoice site regarding the issue. You can see Microsoft's response on why it will be a challenge to fix.
Hi! First of all, thanks for the very helpful tutorials :) I badly need your help :( I created a to do list and cannot figure out how to remove the checkmark (column A, referenced to column C) if the task (column B) is blank. What happens is when I delete a task, the checkbox remains checked and is counted in the total tasks done. Please help.
Great question! Since the checkbox is a control (shape) that floats on top of the grid, you will have to delete the checkbox for rows that it is not in use. You can hold the Ctrl key while clicking the checkbox to select the control, then press the Delete key to remove it. Unfortunately, Excel doesn't have checkboxes within cells like Google Sheets does. Hopefully Excel will get that feature in the future. It makes creating checklists like this much easier. I hope that helps. Thanks again and have a nice day! 🙂
Great question Maheswari! I've worked with checklists that are much longer than 20 items too. We can automate this with a macro. I'll write one and add it to the accompanying blog post. I'll post back here when it's updated.
Here’s my feedback: 1. Is Excel the best tool for the task? If you have access to Microsoft Lists, then that would be a better solution. 2. If you need or want to use Excel: a) you can use conditional formatting for the input column so that a 1 (one) is formatted as a check mark (see Icon Sets as style), and add a rule covering all rows in the column for the tasks. b) use an Excel table with one column for the task (or whatever) and one for status and then define one conditional formatting rule for the table. This rule will apply to all new rows as well. Filter or sort the list? No problem. 2b would be my recommendation if using Excel.
I just added a section to the bottom of the post that includes two macros. One macro creates the cell link for existing checkboxes on a sheet. The other macro creates the checkboxes in each cell of the selected range and creates the cell links as well. This should save a lot of time with the setup process. Here is the link to the post. www.excelcampus.com/tips/checkbox-conditional-formatting/
ı have a question. we can bring a information from other file with vlookup according to one variable. but, whatıf ı want to use two variable to bring this information? which formula should ı use? thank you.
I want a whole line (several columns) to be shaded when a check box is ticked but can't seem to find the correct formula for this, can you help with this?
Can MS Excel create an interactive checklist where when a checklist is ticked, a new column will appear for the next checklist? it seems like a Yes or No. question. If the answer is No, a new column will appear for question No.
i just want to do a simple checklist where you can select an item and it highlights in green but i have 100+ items. With manage rule method i have to do it one by one. how do i solve it
You can size the check-box box perfectly to the cell by holding the ALT key while dragging the handles.
The edges will snap to the confines of the cell. Blew my mind when I learned this trick.
I have used a few before but did not understand what I was doing but now it is much clearer, thank you.
I love it! Thanks for sharing Mike! Mind blown 🤯
@@ExcelCampus Mine is not working - do i drag the handles of the cell the check box is in .... or the actual check box?
@@mmallay1 You need to drag the Check Box handles.
ALT+ Dragging the check box handles does not work ( for ME) for resizing the check box. It jus changes the size of the selection box around the check box without resizing the checkbox itself.
@@Catv47 Hmmm, it sounds like you are unable to select the check box. Try resizing the selection box to either smaller or larger, click away from the box and then retry selecting the check box for resizing.
I love your videos! I need to make a similar checklist for work and this video is exactly what I needed to accomplish this. Thank you.
You're welcome! 😀
I'm an Excel junkie, but Google Sheets' checkbox feature blows Excel out of the water. Excel is beyond laborious when it comes to checkboxes. It's so much easier to do all this in Sheets. I really hope Excel ups their game soon.
Wow, Jon you really know how to explain these things. Better than anyone I've seen. I am so impressed at how you present the material. I am now understanding how to do this.
This is really cool. Hadn't considered adding more formatting to checkboxes.
Thanks! 🙂
Great idea. I am going to add this to my instruction area for end users. Thanks Jon!
Thank you. Your explanation is so clearly described and at a really good pace that it was very easy to follow.
This is brilliant! Thank you for sharing. I find excel horribly confusing, and your instructions actually made sense to me.
As always John, very well presented and helpful.
Thanks Randy! I appreciate your support. 🙏
Superb explanation Jon. All is perfect. Thank you!!
Hi Jon. Great example! Thanks for sharing how to set this up. Very useful :)) Thumbs up!!
Really Cool Tutorial,Conditional Formatting Such A Great Tool...Thank You Jon :)
Thanks Darryl! 🙌
Hello and Good day,
Could you make a video on your first example (the text next to the checkbox when pressed turns gray) that links to a cell in which if you selected a certain range on a drop down list, it shows multiple checkboxes. Now if you selected another range on the same drop down list, it shows another set of checkboxes.
Thank you. However, if I wanted to make a checklist for me and my co workers like this, is there a way for the 'time' to be displayed by it so I know the time of completion?
I have used this technique on my guidelines on how to use my excel file. Additionally, I also link the guideline where when the user click on the next step it automatically skips to the right worksheet tab it needs to be.
Very helpful technique. Thank you. ❤️
Thanks Axycl! 🙂
Very good tips, thanks for sharing
Thanks Kebin! 🙂
This great video , its exactly what I was looking for . I have one question. Is it possible to keep the cells locked until the cell above is not ticked to create a step by step process. So when 1st cell is ticked , second gets unlocked , second is ticked , thirst get unlocked. Is that possible ? Could you please provide some guidance on that please?
Hi! New subscriber. What I'm looking the internet for is, how can I highlight a cell if the checkbox it's linked to is "False" after a certain date? If the context helps I have a table that has each month as a row, and each cell in that row has a review for an employee that's due in that month. (For example in the January row it may have 1 cell that has Sally, another cell that has John, etc.)
If Sally's review didn't get completed in January when it's due (the box is linked to a checkbox), then I want conditional formatting to highlight Sally's cell if the box isn't checked by February 1st.
I hope this makes sense!!
I feel so close it's annoying haha. This one I think feels the closest. I'm essentially saying If this cell ($AC4) is FALSE, meaning the checkbox it's correlated to is unchecked, AND it's past the month it's due, then the cell will turn red for me.
=IF($AC4)=FALSE"&">MONTH(TODAY())
Great tutorial 👌 amazing 👏
How do I change the check box to make it larger. When I insert a check box it is too small compared to the text
Great video Jon!
I am adding this to my project management Excel template right now and I am going to share it with my network!
Do you know why the conditional formatting dialog box will not keep a defined name selection from the F3 Paste Name dialog box?
Thanks Eric! I'm happy to hear you are putting this technique to use. 👍
Great question on the named range references for conditional formatting. Unfortunately, this is a limitation of Excel/conditional formatting. Here is a post on the Excel UserVoice site regarding the issue. You can see Microsoft's response on why it will be a challenge to fix.
Is it also possible when you check a checkbox that it dissapear to another sheet?
Hi! First of all, thanks for the very helpful tutorials :) I badly need your help :( I created a to do list and cannot figure out how to remove the checkmark (column A, referenced to column C) if the task (column B) is blank. What happens is when I delete a task, the checkbox remains checked and is counted in the total tasks done. Please help.
Great question! Since the checkbox is a control (shape) that floats on top of the grid, you will have to delete the checkbox for rows that it is not in use. You can hold the Ctrl key while clicking the checkbox to select the control, then press the Delete key to remove it.
Unfortunately, Excel doesn't have checkboxes within cells like Google Sheets does. Hopefully Excel will get that feature in the future. It makes creating checklists like this much easier.
I hope that helps. Thanks again and have a nice day! 🙂
Is this workbook available please? I cannot find it.
When I paste the check box to a new row, the conditional formatting does not carry over like it does in your video around 8:25. How do I fix this?
Jon, I must have a dif version of Excel. Can I get your help? I'm stepping away now but will be back later
Jon, Can I hire you to walk me through this? I'm a rookie and using a Mac with Excel.
Useful one sir.. But in case I have 20 to dos, do I link each check box to a cell manually? 20 times... Pls Suggest some easier ways sir
Great question Maheswari! I've worked with checklists that are much longer than 20 items too. We can automate this with a macro. I'll write one and add it to the accompanying blog post. I'll post back here when it's updated.
Here’s my feedback:
1. Is Excel the best tool for the task? If you have access to Microsoft Lists, then that would be a better solution.
2. If you need or want to use Excel:
a) you can use conditional formatting for the input column so that a 1 (one) is formatted as a check mark (see Icon Sets as style), and add a rule covering all rows in the column for the tasks.
b) use an Excel table with one column for the task (or whatever) and one for status and then define one conditional formatting rule for the table. This rule will apply to all new rows as well. Filter or sort the list? No problem.
2b would be my recommendation if using Excel.
I just added a section to the bottom of the post that includes two macros. One macro creates the cell link for existing checkboxes on a sheet. The other macro creates the checkboxes in each cell of the selected range and creates the cell links as well. This should save a lot of time with the setup process.
Here is the link to the post. www.excelcampus.com/tips/checkbox-conditional-formatting/
ı have a question. we can bring a information from other file with vlookup according to one variable. but, whatıf ı want to use two variable to bring this information? which formula should ı use?
thank you.
I want a whole line (several columns) to be shaded when a check box is ticked but can't seem to find the correct formula for this, can you help with this?
how to make it bigger size?
Hello this is awesome im one of your follower. Can i asked some help from you about my report?
Hey Hi - how can I resize the check box? Its tiny and will not resize with the cell...
Can MS Excel create an interactive checklist where when a checklist is ticked, a new column will appear for the next checklist? it seems like a Yes or No. question. If the answer is No, a new column will appear for question No.
very informative Sir (Viewed 05/13/2021...7:06p.m)
Thanks Mariel!
The additional formatting starting @10:40 seems like that formula could use a tad bit more explanation for the lost (me). Otherwise, great vid.
HOW CAN WE MAKE the cell read complete when all tasks are completed
i just want to do a simple checklist where you can select an item and it highlights in green but i have 100+ items. With manage rule method i have to do it one by one. how do i solve it