Highlight Winning Lottery Numbers - Excel Conditional Formatting скачать в хорошем качестве

Highlight Winning Lottery Numbers - Excel Conditional Formatting

🔵 In Excel, you can use the conditional formatting to highlight one or more cells, based on rules. In this example, winning lottery numbers are highlighted on a worksheet. This formatting makes it easy to check a group of tickets, to see if there are any prizes! ✅ Get the sample Excel file to follow along: ► https://www.contextures.com/xlcondfor... ⏰ Video Timeline ⏰ 00:00 Worksheet Setup 00:21 Manually Check the Numbers 00:30 Start the Conditional Formatting 00:52 Set the Conditional Formatting Rule 02:14 Refer to active cell 02:48 Select the Formatting 03:04 See the highlighted winning numbers ✅ Excel resources I recommend ► https://myctx.link/xlpick 🔶 Instructor: Debra Dalgleish, Contextures Inc. 💡 Get Debra's monthly Excel tips ► https://myctx.link/CtxNews Subscribe to Contextures YouTube: https://www.youtube.com/user/contextu... #ContexturesExcelTips Video Transcript In this video, we'll see how to use conditional formatting to highlight any numbers on our lottery tickets that match the numbers that were drawn this week. So, I've entered the numbers at the top, and for each ticket (we have three tickets) and the numbers on those tickets are entered here. We could manually check them (put a color in the cell), but it's much quicker and more efficient to let Excel do that for us. I'm going to select all the cells that have the numbers and then I'll apply conditional formatting. So up on the ribbon on the home tab, click conditional formatting. None of these preset ones are going to do what we need, so I'm going to make a new rule. In here we've got different rule types and I'm going to create a formula. I can put the formula in here and we want to find numbers here that match the list. To do that we can use the count if function, so just like we would use it on the worksheet, we can use it in conditional formatting. We'll type equal, COUNTIF, open bracket (and we're going to check the numbers up here, so I'll select those cells and it automatically made them absolute references, which is what we want. Whether it's this cell or this one or the one that's active right now, we don't want them to look at a cell that's a couple of rows above them or to the right. We want them to look in a specific range, so we use an absolute reference here.), and then a comma. We want to count any numbers up here that match the number in our active cell, so this cell is white and the others are darker highlighting, so this is our active cell and up here we can see the cell number; it's C6. I'm going to use that as the reference here, so I'll type C6 and that's a relative reference because for this cell we want it to use D6 and E6 and so on. So we're going to check the count, and I'll close that bracket and we want to highlight any cells where there's a number found in the numbers drawn. We're going to make it greater than or equal to one, so it has to find this number up in this range of cells. If it does find that number, it's going to format it in a different colour, so we'll pick a fill colour here and we'll make those green, and I'll click okay. Click OK, click away so we can see better. All these green cells can be found in the numbers that were drawn this week and all the white cells, we weren't so lucky with those numbers. For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com.