Highlight Winning Lottery Numbers
No, I’ve never won the lottery, but that’s probably because I don’t buy tickets! Your odds of winning improve (slightly) if you actually have a ticket for the draw.
However, there are many workplaces where someone has organized a weekly lottery pool, and they have a batch of ticket numbers to check.
Set Up the Worksheet
In this example, the winning numbers have been entered at the top of a worksheet, in cells C3:H3.
Below that, all the tickets numbers have been entered, in cells C6:H8.
Highlight the Winning Numbers
After all the numbers are entered, you can use conditional formatting to highlight the ticket numbers that were selected in the weekly draw. We’ll use the COUNTIF function in the conditional formatting rule, just as it could be used on the worksheet.
- Select cells C6:H8 – cell C6 is the active cell in the selection, so it will be used as a reference in the conditional formatting rule.
- On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
- Click “Use a Formula to Determine Which Cells to Format”
- For the formula, use the COUNTIF function:
- Click the Format button.
- Select formatting options (green fill, in this example), click OK
- Click OK
The COUNTIF formula checks the cells with the weekly draw numbers (C3:H3), and counts how many times the value in cell C6 is in that range.
- The reference to $C$3:$H3 is absolute, because all the cells should check that range for winning numbers.
- The reference to C6 is relative, because it should adjust, for each cell in the ticket number range – C6:H8
Download the Sample File
To see how the conditional formatting works, you can download the sample file from my website, on the Conditional Formatting Examples page.
Watch the Video
You can watch this short video, to see how the conditional formatting is set up.
Highlight Winning Lottery Numbers With Excel Conditional Formatting
Use conditional formatting in Excel to highlight the winning numbers in a list of lottery tickets. The Excel COUNTIF function checks each number against the week’s winning numbers. If the number is in the list of drawn numbers, the cell is coloured.
The Secret Technique that I been using to win Lottery Consistently!
The Secret Technique that I been using to win Lottery Consistently! Part 2