Highlight Winning Lottery Numbers

No, I’ve never won the lottery, but that’s probably because I don’t buy lottery 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 of your prize money, 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.

conditional formatting highlights winning lottery numbers
conditional formatting highlights winning lottery numbers

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.

  1. 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.
  2. On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
  3. Click “Use a Formula to Determine Which Cells to Format”
  4. For the formula, use the COUNTIF function:
    =COUNTIF($C$3:$H$3,C6)>=1
  5. Click the Format button.
  6. Select formatting options (green fill, in this example), click OK
  7. 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
conditional formatting rule to highlight lottery numbers
conditional formatting rule to highlight lottery numbers

Watch the Video

You can watch this short video, to see how the conditional formatting is set up, to highlight the winning numbers in each ticket.

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.

______________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.