# Build a Self-Updating Checklist & Progress Bar (No VBA!)

## Метаданные

- **Канал:** MyOnlineTrainingHub
- **YouTube:** https://www.youtube.com/watch?v=9HmYFqEumM8
- **Источник:** https://ekstraktznaniy.ru/video/44788

## Транскрипт

### Segment 1 (00:00 - 05:00) []

In this video, we're going to make a checklist in Excel that automatically prioritizes tasks, highlights what's urgent versus completed, and shows your overall progress. As you check things off, it automatically updates. There's no VBA coding required, and it works for any project, product launches, content calendars, event planning, everyday life, whatever you're tracking. You can download the completed template from the description and pin comment. Plus, this video includes a sponsored segment from Scribe later on where I'll show you a simple way to document how this template works if you need to hand it off to your team. All right, let's start building this from scratch. I need headers for task status, priority, and due date. Feel free to modify the headers to suit your needs. I'll add some example tasks and their due dates. Next, I'm going to convert this into an Excel table, and you'll see why we do this at the end of the video. To convert it to a table, select any cell and press CtrlT. I'll leave the box checked that says my table has headers and click okay. And with that, we get a new contextual tab on the ribbon called table design. And here I can rename the table tasks. Press enter. And I can give it a different style and even create my own custom style. I'm going to go with this dark teal style. Simply select it and the formatting's updated. Now in the status column, I want checkboxes. To select the column, simply hover your mouse above the column label until it turns into the down arrow and then left click once to select it. Then on the insert tab, checkbox. Now notice in the formula bar that checkboxes have an underlying true value if it's checked and false value if it's unchecked. And we're going to use those in our formulas shortly. Next, let's auto assign priority based on the due date. You can set your own priorities, but for this example, I'm going to make tasks due in a week or less high priority. Tasks due within the next 2 weeks medium priority, and anything further out is low priority. And of course, if the status has a check mark, then it's complete. I'm going to use the ifs function, but you can also use nested if formulas. Ifs requires a series of logical tests and then the result you want returned. Now the first test is simply to check if the status column is checked or not. Notice the reference to the status column uses the column name instead of a cell reference. This is called a structured reference and it's a feature of tables that makes your formulas easier to read and write. Now remember if the checkbox is checked the cell contains true in which case I want to return the text complete. If the status is unchecked, then we need to perform our next logical test, which is to take the due date. And to select it, I just need to arrow underneath my formula minus today's date. And for today's date, I can simply use the today function and then test if that result is less than or equal to 7. If that's true, then it's high priority. If that's false, then we'll take the due date again minus today's date and check if it's less than or equal to 14 days, in which case it's medium priority. And if all those are false, then the last option must simply be true. So I don't need to perform another logical test here. Just enter true and then return the text low. Close parenthesis on ifs. Press enter. And as you can see, the formula copies down automatically, which is another benefit of Excel tables. Next, let's use conditional formatting to make this checklist easier to read so that completed tasks take a back seat and pending tasks get your attention. First, select the priority column. And remember, hover your mouse above the column label until it turns into the down arrow. Left click once to select the column and then home tab conditional formatting highlight cells rules and here we want text that contains and the first text is high. If it's high then this light red fill with dark red text will be perfect. Click okay. Next we just need to repeat for medium. And here I can choose this yellow fill with dark yellow text. Click okay and repeat for low text that contains. Now this time I don't like any of these defaults. So I'm going to go in and choose a customized format. And we'll give it a light blue fill. dark blue font. Click okay. We get a preview. Looks good. And okay again. Now for the completed tasks, I want a fill color across the row. So I'm going to select the whole table. Just hover your mouse in the top left until it turns into the diagonal down arrow. Left click once to select the table. Conditional formatting. This time I want new rule. And here I want to use a

### Segment 2 (05:00 - 10:00) [5:00]

formula to determine which cells to format. Now the formula is simply to check the status column. Remember the check mark returns true if it's checked. So all we need to do is reference that cell. If it contains true, the format will be applied and if it contains false, it won't. However, I want to check each row individually. So, I need to remove the dollar sign before the row number to allow this reference to be relative to the row. And then for the formatting, I want to set gray fill color. And let's make the font also gray. And click okay. And then to test it out, let's check some of the tasks. And you can see they're being highlighted accordingly. At this point, our checklist already makes smart decisions. Tasks fade out when they're done. Priority updates automatically based on due dates. Now, let's answer the big picture question. How far along is this project really? We'll do that with a simple progress tracker that updates automatically as tasks are completed. First, we need to know how many tasks exist in total. And for that, I can use the count a function and simply count the number of tasks. Again, it uses the table structured reference. This time, because it's outside the table, we get the table name first, followed by the column name. Close parenthesis. And you can see we have eight tasks. And if I add any tasks to this table, the formula will automatically include them because it's referencing the whole table column. Next, I need the completed tasks. And for that, I'm going to use the sum function to sum the status column. But here's the trick. The status column contains true and false values. So I can't directly sum them. However, true and false have numeric equivalents of one for true and zero for false. And they convert to their numeric equivalents when you perform a math operation on them. So an easy way to coersse them into their numeric equivalents is to use the double uny or two minus signs in front of the reference. And with that, sum can now add up any true values in the status column. If this trick impressed you, imagine what other hidden formula tricks there are. I have a full advanced formulas course that'll change how you work in Excel, and you can check the link in the description or pinned comment. Now, let's calculate the percentage complete, which is simply the completed tasks divided by the total tasks. Let's format that as a percentage. Next, let's make it visual with a conditional formatting data bar. So, with the cell selected, conditional formatting data bars, and we'll go with this solid fill. By default, the bar fills the cell. So, let's edit the rule via the manage rules dialog box. Double click the rule to open it. And here under type, I want a number. The value is zero. That's the minimum. And under maximum, I also want a number. And here the maximum is 1 or 100%. Let's also change the color in keeping with the theme which is dark teal. Click okay and okay. And now we correctly see the length of the bar in line with the percentage. Let's add a cell fill color. And let's make the font white. I'll make it a bit bigger. And I'll vertically align it centered. As a finishing touch, I'm going to add a shape with rounded corners. So, insert shapes. This rectangle with rounded corners. Holding alt, I'm going to draw the shape over that cell. That just snaps it to the cell size. Makes it quick and easy to insert it. We need to change the shape fill to no fill so we can see the data bar. And we'll change the outline to the same shade of gray. And I might actually make the weight slightly thicker. And now we have the appearance of rounded corners on the data bar. Let's give the product tracker a title, product launch checklist. And I'm just going to merge and center this. And I'm going to align it right and then bump it off a little bit. Now, if you know me, you know I don't like merge and center. But unfortunately, there's no alternative for vertical merging. So, we have to use it. As a finishing touch, let's insert an icon that represents a checklist. This one here looks good. Click insert. Let's change the outline to the same teal. And we'll just make it a bit smaller and rotate it slightly. All right, that looks good. One other touch. I'm going to add some borders to this cell. Ctrl 1. Let's make them the same teal. Make them quite thick. and just the left and right borders just to separate our total tasks from our completed tasks. All right, let's test it. As I complete tasks, they're grayed

### Segment 3 (10:00 - 12:00) [10:00]

out and the completed count and progress bar updates automatically. If I change dates, for example, let's make this 24th of January, the priority automatically updates and the conditional formatting draws our attention to it. This is way more reliable than manually tagging priorities because well, dates don't lie. Urgency updates automatically and your checklist stays accurate without any extra effort. I can also add new tasks as my project evolves and the table expands to include it. Let's give it a date and notice the total tasks and the progress bar update. Now, building the checklist is one thing, but handing it over to a team is where implementation often becomes the bigger task. Documenting processes with screenshots, step-by-step notes, and constant updates can take longer than building the template itself. And that's where Scribe comes in. You do the process once while it records, and it automatically builds a complete step-by-step guide with screenshots and instructions. Let me show you how it works. I just hit record in the extension and go through using the checklist like I normally would. Checking items, add new items, enter the due date, delete completed items, etc. Once I'm done, Scribe has already created this guide, all these steps with screenshots, and I can edit the text, delete steps I don't need. Creating a guide this way literally is 15 times faster than doing it manually. Now I can share this however I want. Can send a link, download it as a PDF, invite team members, and if I need to change something later, it updates automatically for everyone. No resending. There's also this guide me feature that gives people an interactive walkthrough right in their browser. It shows them exactly where to click for each step. If you regularly need to hand spreadsheets or systems over to other people, Scribe removes the tedious work of creating step-by-step documentation. If you want to try it out, go to scribe. how/moth or click the link in the description. You can use it for free or get Scribe Pro for additional features. Now, if you love the self-updating checklist, you're going to want to see my video on five Excel tricks for self-updating spreadsheets. I show you how to make drop-own lists that auto expand, formulas that never break when you add data, and dynamic charts that update themselves. Basically, how to make Excel do the boring stuff for you. You can watch it here and I'll see you