Easy checkmarks in Excel from Jack Whipple
Rather than use P in Wingdings 2 font, you can use Icon Sets
1 = Green Checkmark: Done
0 = Empty Circle: Still to Do
-1 = Yellow Dash: Not required this year, but keep it on the list for next year
To start, Home, Conditional Formatting, Icon Sets, 3 Symbols (Uncircled)
Home, Conditional Formatting, Manage Rules. Change second symbol to a round circle. 3rd symbol to dash
Bonus tip from Debra Dalgleish at Contextures.com (subscribe to her weekly newsletter!)
Conditional Formatting in A: Use Formula. If B = 1, then strikethrough
Why is Ctrl+5 strikethrough? Because when people used to take inventory with hash marks |||| the fifth would cross out the first four.
Rather than use P in Wingdings 2 font, you can use Icon Sets
1 = Green Checkmark: Done
0 = Empty Circle: Still to Do
-1 = Yellow Dash: Not required this year, but keep it on the list for next year
To start, Home, Conditional Formatting, Icon Sets, 3 Symbols (Uncircled)
Home, Conditional Formatting, Manage Rules. Change second symbol to a round circle. 3rd symbol to dash
Bonus tip from Debra Dalgleish at Contextures.com (subscribe to her weekly newsletter!)
Contextures Excel Newsletter Article Index
Find articles from the Contextures Excel Newsletter. Search for a topic, filter the list, or sort by any column
www.contextures.com
Why is Ctrl+5 strikethrough? Because when people used to take inventory with hash marks |||| the fifth would cross out the first four.
Transcript of the video:
Learn Excel for MrExcel Podcast Episode 2225: Easy Checkmarks in Excel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen, and today's idea, from Jack Whipple.
I met Jack in Atlanta, the Gulf Coast Council of the IMA seminar.
He creates an Accounting Closing Checklist, and he wants to put checkmarks next to the things that are done.
Now, typically, to do checkmarks, you have to put a capital letter P, and then it's one of four fonts-- I can never remember which one it is, is it Webdings, Wingdings?
Wingdings 2?
I think Wingdings 2 gets us a little check mark there.
Alright, but that's hard to remember, so here's what Jack does.
Jack has all of his list of accounts that, if something's done, he puts a 1, if it's not done, he puts a 0, if it doesn't have to be done this year, but should stay on for next year, he puts a negative -1.
So we have those three values, select that data, we go to Conditional Formatting, Icon Sets, use this one down here-- the 3 Symbols (Uncircled)-- that gets the green checkmark in.
Alright, so, now anything with a 1 gets a green checkmark.
Go to Conditional Formatting, Manage Rules, Edit that rule and, you know, just to be sure, I always like to change both of these to numbers, and we're going to say >= 1, and then >= 0.
Green checkmark is perfect.
For the things that aren't done, Jack uses an empty circle-- that way, he says, if I print it out he actually has a spot where you can add a check mark with a mechanical pencil-- and then here, a yellow dash to mean that this item doesn't have to be done this year but we're keeping it on the list for next year-- like that, like that.
Alright, and then as you go through and do things, we can, very quickly, just put a 1, mark it off, this one does have to be done this year so -1-- oh wait, this one wasn't done, let's change it back to a 0.
Alright.
Now, the check marks are great, but the ones and zeros aren't so great, so I'm going to go back in to Conditional Formatting, Manage Rules, Edit the rule, and there's a checkbox here for Show Icon Only.
So as long as you remember the 1, 0, and -1, click OK click OK.
Alright, now you get all that wonderful goodness just by using the numbers 1 or -1 or 0 to change between them.
Now, this next tip is from Debra Dalgleish at contextures.com.
Check out Debra's great weekly newsletter.
This was in our newsletter a couple of months ago and I'm like, "That's brilliant," because I use strikethrough all the time to mark things off-- so Ctrl+5 is strikethrough, but Debra has a better way to do that.
Conditional Formatting, create a New Rule, Use a formula determine which cells to format-- and notice I'm in A3, so I'll write a formula that works for A3-- so we're going to say =B3=1, so in other words, the cell immediately to the right of the active cell is equal to 1.
Then we're going to Format, and we will choose Strikethrough, click OK, click OK, and then anything that gets a 1-- the green checkmark-- the strikethrough is applied.
So I'll put a 1 here, I'll go to the next task, that 1 changes to a green checkmark, the item to the left is automatically struck through.
What a cool trick.
Check out my book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
"I" in the top right hand corner there.
Alright, wrap up for today's episode, Easy Checkmarks in Excel, from Jack Whipple.
Rather than use a P in Wingdings font-- Wingdings 2 font-- to create a check mark, you can use icon sets.
So we use 1 for a green check mark, 0 for an empty circle, - 1 for a yellow, and then take those numbers, 0, 1, and -1, Home, Conditional Formatting, Icon Sets, 3 Symbol (Uncircled), Manage those Rules, change the second symbol to a round circle, third symbol to a dash.
And, then, a bonus tip from Debra at Contextures-- make sure to subscribe to her weekly newsletter-- Conditional Formatting in A: Use Formula, if B=1 then Strikethrough the value in A.
To download the workbook from today's video, visit the URL in the YouTube description.
I want to thank Jack for that awesome tip, Debra for her Strikethrough tip, and I want thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, here's a bonus outtake for you: Why is Ctrl+5 Strikethrough?
Think about back in the old days, when you used to take inventory with a mechanical pencil making hash marks-- that's 1, that's 2, that's 3, that's 4-- it's the fifth one, Ctrl+5, that strikes out the other ones.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen, and today's idea, from Jack Whipple.
I met Jack in Atlanta, the Gulf Coast Council of the IMA seminar.
He creates an Accounting Closing Checklist, and he wants to put checkmarks next to the things that are done.
Now, typically, to do checkmarks, you have to put a capital letter P, and then it's one of four fonts-- I can never remember which one it is, is it Webdings, Wingdings?
Wingdings 2?
I think Wingdings 2 gets us a little check mark there.
Alright, but that's hard to remember, so here's what Jack does.
Jack has all of his list of accounts that, if something's done, he puts a 1, if it's not done, he puts a 0, if it doesn't have to be done this year, but should stay on for next year, he puts a negative -1.
So we have those three values, select that data, we go to Conditional Formatting, Icon Sets, use this one down here-- the 3 Symbols (Uncircled)-- that gets the green checkmark in.
Alright, so, now anything with a 1 gets a green checkmark.
Go to Conditional Formatting, Manage Rules, Edit that rule and, you know, just to be sure, I always like to change both of these to numbers, and we're going to say >= 1, and then >= 0.
Green checkmark is perfect.
For the things that aren't done, Jack uses an empty circle-- that way, he says, if I print it out he actually has a spot where you can add a check mark with a mechanical pencil-- and then here, a yellow dash to mean that this item doesn't have to be done this year but we're keeping it on the list for next year-- like that, like that.
Alright, and then as you go through and do things, we can, very quickly, just put a 1, mark it off, this one does have to be done this year so -1-- oh wait, this one wasn't done, let's change it back to a 0.
Alright.
Now, the check marks are great, but the ones and zeros aren't so great, so I'm going to go back in to Conditional Formatting, Manage Rules, Edit the rule, and there's a checkbox here for Show Icon Only.
So as long as you remember the 1, 0, and -1, click OK click OK.
Alright, now you get all that wonderful goodness just by using the numbers 1 or -1 or 0 to change between them.
Now, this next tip is from Debra Dalgleish at contextures.com.
Check out Debra's great weekly newsletter.
This was in our newsletter a couple of months ago and I'm like, "That's brilliant," because I use strikethrough all the time to mark things off-- so Ctrl+5 is strikethrough, but Debra has a better way to do that.
Conditional Formatting, create a New Rule, Use a formula determine which cells to format-- and notice I'm in A3, so I'll write a formula that works for A3-- so we're going to say =B3=1, so in other words, the cell immediately to the right of the active cell is equal to 1.
Then we're going to Format, and we will choose Strikethrough, click OK, click OK, and then anything that gets a 1-- the green checkmark-- the strikethrough is applied.
So I'll put a 1 here, I'll go to the next task, that 1 changes to a green checkmark, the item to the left is automatically struck through.
What a cool trick.
Check out my book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
"I" in the top right hand corner there.
Alright, wrap up for today's episode, Easy Checkmarks in Excel, from Jack Whipple.
Rather than use a P in Wingdings font-- Wingdings 2 font-- to create a check mark, you can use icon sets.
So we use 1 for a green check mark, 0 for an empty circle, - 1 for a yellow, and then take those numbers, 0, 1, and -1, Home, Conditional Formatting, Icon Sets, 3 Symbol (Uncircled), Manage those Rules, change the second symbol to a round circle, third symbol to a dash.
And, then, a bonus tip from Debra at Contextures-- make sure to subscribe to her weekly newsletter-- Conditional Formatting in A: Use Formula, if B=1 then Strikethrough the value in A.
To download the workbook from today's video, visit the URL in the YouTube description.
I want to thank Jack for that awesome tip, Debra for her Strikethrough tip, and I want thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, here's a bonus outtake for you: Why is Ctrl+5 Strikethrough?
Think about back in the old days, when you used to take inventory with a mechanical pencil making hash marks-- that's 1, that's 2, that's 3, that's 4-- it's the fifth one, Ctrl+5, that strikes out the other ones.
