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.

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.

lotterywinners02

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

lotterywinners03

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

______________________

You may also like...

1 Response

  1. Ali Alganaby says:

    This is not working for me can somebody help?

Leave a Reply

Your email address will not be published. Required fields are marked *