00:00This is how you create a progress bar chart using only conditional formatting and it looks
00:05something like this. Let me show you how this is done. First you're going to select all the cells
00:10under the progress bar column like this and then after that go home, style, conditional formatting.
00:16In here you're going to select manage rules. On this section here you're going to select new rules
00:22and you're going to select use a formula to determine which cell to format. And the formula
00:26you're going to use is a equal B3. B3 pertains to this cell here, the percent complete. If this B3
00:34is less than 100% like this, let's assign the color to be filled as green and click OK and OK one more
00:44time and you should have one entry or one rules in here. And next rule, we're going to say new rule,
00:49same thing, use the formula to determine which cell to format. You're going to say equal B3
00:54less than, let's do 60% now. And if it's less than 60%, you're going to click on format and fill
01:02and let's this time around color it in yellow like this and click OK and OK one more time. Now you've
01:09got two rules and let's create one more rule, new rule, use formula to determine which cell to format,
01:16equal B3. Now this time around let's do 30%. If it's less than 30%, we're going to color that as this
01:23this kind of red or any color that you like and click OK and OK. Now you should have three rules
01:29like this. Now if you click on OK, all your cells on your progress bar should be colored according to
01:35the percentage that you have completed shown here. Next, we're going to do the white color, which is
01:41going to be the inverse color of the bar chart. Make sure you are still selected all the cells on your
01:46progress bar here. Then you go home, style, conditional formatting. Next, you're going to select
01:52data bars and you're going to say more rules here. In this pop-up here, you're going to select
01:58this to number, the type to number. Leave this as 0. That's the minimum side. On the maximum side,
02:07you're going to select this as number again and we're going to set this to 1. The reason for that
02:11is because our percentage is actually from 0 to 1 but it has been formatted so that it's showing
02:160 to 100. So once that's done, we're going to color the bar chart as white, which is going to be inverse
02:23and then after that, bar direction, we're going to say right to left like this. After that, you're
02:31going to click OK. Nothing really happens. You can see this. It looks like nothing's going on but
02:36now to draw the white bar chart, you're going to say equal 1 minus B3 like this and you can see that
02:44the bar chart is being drawn here. It's showing as 58 percent but the 42 pertains to the color side.
02:51Now, you're going to apply the same set of formula to the rest of the row. Just double click on this
02:56fill handler like this and it applies to the rest of the rows. Now, to make it more pretty, we don't
03:03want to see these numbers here, you select all the progress bar like this and you're going to change
03:08this font color to white and basically align it to the right like this and after that, you're done.
Comments