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.
Instead of checking those numbers manually (and missing one or two!), you can use Excel to check them for you. It won’t even ask for a percentage, if you are lucky enough to win a prize.
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.
Or watch on YouTube: Highlight Winning Lottery Numbers