Enhance Your Checkboxes & To-do Lists with Conditional Formatting in Excel
HTML-код
- Опубликовано: 24 июл 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video, I demonstrate how to improve your Excel checklists by using conditional formatting. When a box is checked, you can gray out or strikethrough the text and even highlight the next task on the list.
This technique uses the Form Control Checkboxes and conditional formatting. The video is packed with additional tips for creating and working with checkboxes on sheets, enabling the Developer tab in the Ribbon, setting the cell link for a checkbox, writing formulas for conditional formatting, and more.
If you’d like to read the accompanying blog post on my website, you can access it here: www.excelcampus.com/tips/chec...
Related Videos:
Conditional Formatting Rows Based on Another Cell Value: • Conditional Formatting...
Highlight Rows Between Two Dates with Conditional Formatting in Excel: • Highlight Rows Between...
How to Apply Conditional Formatting to Pivot Tables:
• How To Apply Condition...
Shortcut to Duplicate Shapes with Ctrl+Shift+Drag:
www.excelcampus.com/charts/co...
#MsExcel #ExcelCampus
00:00 Introduction
00:12 Enhance Checkboxes with Conditional Formatting
01:43 Inserting a Checkbox
04:00 Linking the Checkbox
04:51 Applying Conditional Formatting
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.
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.
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.
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! 😀
Superb explanation Jon. All is perfect. Thank you!!
Hi Jon. Great example! Thanks for sharing how to set this up. Very useful :)) Thumbs up!!
This is brilliant! Thank you for sharing. I find excel horribly confusing, and your instructions actually made sense to me.
This is really cool. Hadn't considered adding more formatting to checkboxes.
Thanks! 🙂
As always John, very well presented and helpful.
Thanks Randy! I appreciate your support. 🙏
Really Cool Tutorial,Conditional Formatting Such A Great Tool...Thank You Jon :)
Thanks Darryl! 🙌
Very helpful technique. Thank you. ❤️
Thanks Axycl! 🙂
Great tutorial 👌 amazing 👏
Very good tips, thanks for sharing
Thanks Kebin! 🙂
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.
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?
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?
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.
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.
Hello , thank you for the video very interesting , i have a question minute 4:29 , when we choose the cell link than minute 7:50 we copy the checkboxes, how can we modify the cell link 8:11 ok but if we have a to do of 100 or 200 task ? many thanks for your help
Great question! This could be automated with a macro. You can use VBA to create the controls (checkboxes), place them in a cell, and assign the cell link. However, this might not be the best solution for long lists like this. The controls could get difficult to manage if you are inserting and deleting rows.
Excel doesn't have the in-cell checkbox feature like Google Sheets has. At least not yet. Hopefully that will come in the future as it would make this process much easier.
I hope that helps. Thanks again and have a nice weekend! 🙂
very informative Sir (Viewed 05/13/2021...7:06p.m)
Thanks Mariel!
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())
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
Hello this is awesome im one of your follower. Can i asked some help from you about my report?
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?
Is this workbook available please? I cannot find it.
ı 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.
Hey Hi - how can I resize the check box? Its tiny and will not resize with the cell...
Is it also possible when you check a checkbox that it dissapear to another sheet?
how to make it bigger size?
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.
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.
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! 🙂
HOW CAN WE MAKE the cell read complete when all tasks are completed
The additional formatting starting @10:40 seems like that formula could use a tad bit more explanation for the lost (me). Otherwise, great vid.
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/
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