New time added here at the same time. See what's happening up here under "completed tasks"? We have now four out of nine tasks done. Once we learn how to do conditional formatting, we have five out of nine done. Also, keep your eye on this; this is the list of not completed, i.e., our "look forward to" bucket, and once we place a check mark here, that task is removed from this bucket. Okay, so that's what we're going to learn how to do. Let's set this up from scratch..
I have my learning list here and added this little cute icon in front of my learning list. I want to add checkboxes. In Office 365, you are going to find them under the Insert tab, all the way here. There's the checkbox. Just select it, and it inserts one inside the cell. When you hover over it, you're going to see the hand icon, and when you click on it, you are going to get the tick mark in there. Notice what happens in our formula bar. When we have a check mark, we get a TRUE;.
When it's unchecked, we get FALSE. Now, this is great because it means that we can write formulas that behave differently depending on whether our checkbox has a tick mark in it or not. If you want to change the color of your checkbox, you just go and change the font color. So if I change this to ORANGE, I get an orange checkbox now. Instead of clicking on it, you can also use the space bar to toggle it on or off. If you want to remove a checkbox completely, you can try to delete it,.
Right? So if I just press the back arrow or the delete button and then press Enter, the checkbox is gone. If I go to the cell, I can see I don't have any value in the cell, but I get this light-colored checkbox there. So it's still there because checkboxes are a mixture of formatting and cell values. So the formatting of the checkbox is there, and this can be a good thing in case someone unintentionally removes your checkboxes. You can just go back, click here,.
We know that whenever a checkbox has a check mark in it, we get a value of TRUE in the cell. So we are going to be using that in our formula. So first thing I'm going to do is write the formula on this side, and then later I'm going to show you how you can bring it inside a shape. So there are different ways you can set this up. I'm just going to use the COUNTIF function. So we COUNTIF, we need a range. That's my range. If you're using table references, you just have to reference.
The table column. The criteria is simple; it's TRUE. Close the bracket, press Enter, and I get two because I have two TRUE values here, two checkboxes are marked. If this one is marked as well, we get a three. So that's the number of completed tasks. Now on this side, I'm also going to get the total number of checkboxes that I have. Again, different ways of doing this. I'm just going to use the COUNTA function. So COUNTA counts everything. It counts numbers, and it also.
Counts text. It counts Boolean values as well. So when I close the bracket and I press Enter, I get nine. So far so good. Now I want to combine all of these together. I'm just going to combine them in a single cell. So we're just going to reference this one. We're going to use the Ampersand because we're combining values from different cells. Then I want to combine it with this one, Ampersand. I want to get the division operator; let's put that inside quotation marks, then Ampersand. And we're.
Going to select this cell here. When I press Enter, I get the whole thing stuck together. I could add a space between the two, but what I'd optimally want to do is to put the task, the three out of nine, below "completed tasks." So I want to add a line break there. I can do that using the CHAR or C function. So I'm going to use the ENT again, put in CHAR open bracket 10. That's the one that you need. Then when you press Enter, you are going to get it on the next line. You just.
Can't see it yet because you have to activate the WP text if you are looking at that value in the cell, right? So now we can see it nicely separated. This doesn't really matter if you are bringing this value inside a shape. So let me just show you to bring it inside this little box that we had here. First thing we need to do is insert this shape. So I'm going to go to Illustrations, Shapes, and I'm just going to go with this one. Let's draw it out quickly, adjust the shape fill.
- Python Part 13 - Error-trapping Office
- VBA to filter using clipboard data. Simply copy data and execute the utility. Time saving
- Design A Single Page Static Website (Custom Blogger Homepage)
And the shape outline. Just want to get a thin weight there. So now with our shape selected, - ID Card Make
We're going to go directly to the formula bar, type in equals, and select this cell. Now when I press Enter, I get my completed tasks shown inside the box, and it's all dynamic, right? If I complete pivot tables, I get four out of nine, five out of nine, and so on. Now let's just make it bold as well so everything stands out nicely. Now all we need to do is to hide this bit from.View. There are different ways you can do this, right? You can just put it somewhere really far on your worksheet, or you can group the columns together, or you can just hide these columns. Now let me show you how you can combine the value of a checkbox with an Excel function. So for example, I want to create my not completed list, my "look forward to" bucket list here. Basically, I want to include any items that don't have a check mark. I am going to use the FILTER function. First thing I.
Need is the array; this is what I want returned. I'm going to select my learning list items. Then what I want included is anything from this column that equals FALSE. If empty, so if there are no matches, I want nothing returned. I'm just going to put quotation, quotation. Close the bracket, press Enter, and that's my list of outstanding items. Now let's say I finished learning Power Query. I'm going to place a check mark here, and Power Query disappears from my list. If I have.
A check mark everywhere, I have nothing to look forward to. If you want to add a box around this, all you have to do is go and insert a shape. So for example, let's go with this one. I'm just going to draw it out here. Let's quickly adjust the shape color. Now, obviously, this is covering our value in the cells, so I'm going to go back to shape fill under more fill colors. We're going to adjust the transparency to be a lot higher, so I'm just going to go with this number,.
Click on OK, and now I can see the values through. The good thing is that when you click on this, it selects the shape, so it's kind of a way of making sure no one messes with your formulas too. Now we're going to take a look at how we can combine checkboxes with conditional formatting. So let's say every time I have a check mark here, I want to get my item to have a strike through, right? I want it to be crossed off. What I'm going to do first is select the range that.
I want to conditional format, then go to Home, Conditional Formatting, and add a new rule. I'm going to use a formula for this, and for the formula, notice it says "format values where this formula is true." The formula is going to say equals this cell. All of these cells are looking at C5 because notice I have a dollar sign for the column for C and also for the row. I don't want them to all look at cell C5; I want them to all look at column C,.
But the row should be different, right? It should be relevant to where that cell is, so I have to remove that dollar sign here. Now you can say equals TRUE, but you don't really need to do that because the default check is whether it's true or not. So basically, that's all you need to do here. Then you go to Format and you decide on the format that you want. So I want them to have a strikethrough, right? I want it to look like this. Click on OK, OK, and that's that. The.
Moment I have a check mark anywhere here, I get my strikethrough formatting to show up. Now the next thing I want to do is to change the color of the entire row to be green as well. So I want the font color to be green again. You have to select the area that you want to conditionally format, which in this case is this area. We're going to go ahead and add another rule, so let's go back to Conditional Formatting, New Rule. Again, I'm going to use a formula. The formula is going to.
Be the same; we're going to have C5 here, and again, remove the dollar sign for the row. Now the formatting that we want to apply here is for the font, but I want the color to be different, so I want it to be a green. Let's just go with this one. Click on OK and OK, and now we have two conditional formatting rules in place, right? So every time we place a check mark, we get the strikethrough effect here, and we also get the entire row in this green font..
Okay, I've saved the best for last. Let's see how we can add a timestamp every time we check
Off an item. So the way we're going to do this is to use the IFs function. This one is going to check whether one or more conditions are true. Our first logical test is to check whether the value of this box equals FALSE. Basically, if it's not checked, what do we want to return? We want to return nothing, right? We don't want a timestamp if there is no check mark. Now, here's.Where things are going to get weird, but stay with me. So our next check is to check whether the cell that we're writing the formula in, if this cell equals nothing, what we want to return is the NOW function. So NOW is going to return the current date and time. Now, I know this is strange because we're referencing the cell that we're writing it in, and this is not correct, but stay with me, and I will explain. Our last logical test is going to be the catchall. So if none of these conditions.
Is met and we get to the end, we are going to put a TRUE. This is acting as our catchall, and what we want to happen is to return the value that we have in the current cell. Now when I close the bracket and I press Enter, I get a value. I'm supposed to not get a value because I don't have a check mark here. Even when I press a check mark, I still get the same value, right? So this is not correct, and yes, it is not correct in this way because a cell in Excel by default can't be.
An input and an output at the same time. Excel is going to go in a loop, and I'm just going to get a circular reference. Here's the thing that you need to tweak: you have to go to File, Options, under Formulas, for the calculation options for this workbook, you need to turn on "Enable iterative calculation" and then click on OK, and everything will work like magic. Now why is this so? Well, iterative calculations allow Excel to handle such situations by repeatedly calculating the value.
Of the cell until it reaches a stable answer or until it reaches the maximum number of iterations that are allowed. So in my case, default was 100. This can have unintended consequences, so whenever you're using it, you have to use it with care. Okay, so having said that, we have completed our tutorial on how to use checkboxes. You've learned how you can add a total count of your completed tasks, how you can use conditional formatting together with the checkboxes,.
