Apparently you have to buy a ticket if you want to win the lottery, so I’m out of luck. However, if you’re in an office lottery pool, or buy your own tickets for the lottery, Excel can let you know if you have a winning ticket. It just takes a bit of conditional formatting, and the COUNTIF function.
You could use this technique to highlight other things too, based on a list of items to check. For example, you could create a list of bad accounts or obsolete products, and highlight those on an Excel worksheet with product order requests.
Highlight Lottery Numbers
In this example the ticket numbers are in cells B2:G4, and the winning numbers drawn for the current lottery are entered in cells B6:G6.
Follow these steps to highlight the winning numbers in the list of tickets:
- Select the cells were the conditional formatting will be applied, B2:G4
- On the Excel Ribbon’s Home tab, click Conditional Formatting
- Click New Rule
- In the New Formatting Rule window, click Use a Formula to determine which cells to format
- For the formula, use the COUNTIF function, and refer to the active cell:
- Click the Format button.
- Select the formatting options that you want (blue fill colour, in this example), then click OK.
- Click OK
The cells with winning numbers are highlighted in blue, and other cells have no fill colour.
There are more examples of Excel conditional formatting on the Contextures website.
Watch the Video
To see the steps for highlighting the winning lottery numbers with Excel conditional formatting, please watch this short Excel tutorial video.
Excel Tweet of the Day
Is resistance futile? Will this tweeter succumb to the temptation to tidy up? Would you?
- another excel VBA morning. Delete works now:) ….. But can I resist the temptation to tidy up all the code now even though it works fine?
For more entertaining and enlightening Excel tweets, culled from the thousands posted every day, see the Excel Theatre Blog.