PrizeSelect Raffle Program - downloadable

Download: Free raffle ticket, prize drawing program with no registration or trials; a simple .exe. This replaces and dramatically improves on an earlier program written in Excel (PrizeOrama).


 
PrizeSelect is a Windows raffle program that randomly selects winners from a pool of contestants and displays the results in real-time on the screen.  Fully configurable, with graphics, wallpapers, prize-lists, and numerous options.

Free to download.

No registration, no nagware, no advertisements, no installation.
Simple EXE copy for installation.

Running Prize Select, about to give away a car.  Click for larger image.

PrizeSelect understands how a raffle operates

- especially in front of a live audience

It is designed to be used in front of a live audience and can handle either raffle-tickets (numbers) or people names.  The presenter has great flexibility in changing which prizes are awarded, in what order, and you can control repeat-winners and other aspects of a raffle.  This program knows how raffles should be run.

2022.05  Minor update:  Program re-compiled using Visual Studio 2022 and .dotNet 4.8


Small Program, Many Features:

• Free for all personal and commercial use.
  No registration or licensing. 
  No Adware
  No bull
   
• No hassle installation:
  Download/Copy the EXE along with a couple of graphics for the wallpaper.
  Run the exe.  An ad-hoc raffle is ready to begin.
   
• Supports instant ad-hoc (on-the-fly) raffles in either a numbered-ticket or "Person's named" mode.  Or, with a little setup, the raffle can be configured, with all contestants and prizes pre-loaded via a simple ascii-text configuration file.  
  
  Multiple configuration files can be used, each with separate prize-lists and other criteria.
  Once the raffle starts, contestants can be added on-the-fly

• Winners can optionally be restricted to one (1) win per contest. 
  Changeable on a prize-by-prize basis, or at the whim of the Prize Master.
  
  For example, when giving away minor prizes, only allow one win per person, but for the Grand Prize, let all contestants be eligible. One mouse-click changes this mode.
   
• As names/tickets are drawn, you choose whether to "accept" them as a winner (for a 'Must be present to win' raffle) or if the winner declines the prize, simply draw a new name. Declined names are eligible to win the next prize.
  
  
• Contestants can be unobtrusively removed during an in-progress raffle (when they may have already won another prize).
   
• Jazz-up the program with user-defined wallpapers.  In addition to the default blue wallpaper, illustrated above, you can make your own, company-branded wallpapers.  Several are illustrated in this article. 
   
• Fully documented with a 50-page PDF   

Ticket Sale Features:

• Contestants can be entered into the raffle either by selling "raffle-tickets," typically from a roll of numbered tickets, or more by "Name," as in "John Smith."
  
  
• Numbered Raffle tickets can be entered en-mass, as one transaction, moments before the raffle starts. For example, you could enter tickets "1000-1546" and the raffle would choose between these numbers.
  
  Tickets can be entered from multiple, non-intersecting dis-contiguous rolls. Additional tickets can be added after the raffle begins.

• Of particular interest, numbered tickets can (and should be) sold by "Name."  (using the "Names" method).  For example, John Smith may have purchased 15 numbered tickets. You don't have to record the numbers!
  
  When entering his purchase, type "John Smith x15" -- giving Mr. Smith 15 chances (15 copies of his name in the contestant list).  Or, if multiple John Smiths, "John Smith of Accounting x15"
  
  Use this method even if you are using numbered tickets.  With this, there is no need to track the actual ticket numbers. This is a popular way to use the program with medium to small groups.

• If Mr. Smith later returns to buy 5 more tickets, enter the second transaction ("John Smith x5").  No need to look up the previous purchase. Mr. Smith would now have 20 chances to win.
  
  
• Or, tickets can be entered by Number (tickets 1200-1476).
  
  
• Contestants can also be entered via a previously built configuration file.
  
  For example, for a company party, you could import all employee names and the prizes before the gathering.  Then, after the party starts, other names can be added ad-hoc during the raffle. The program warns if duplicate names are found.
  
  All contestants (or tickets) can be edited and viewed from within the raffle program.
  
  
• As names are drawn, the chosen name rolls like a slot machine.  The actual effect looks neat.  Here is a GIF animation that lamely tries to simulate the event (this GIF animation is too slow and does not progress the way the real effect does.... )
  
  
  
  
  
• A winning numbered-ticket (#2319), and a winning ticket-by-name (Nancy) looks like this:
  
  
  

Prize-List Features:

• Optional Prize lists can be built ahead of time with pictures acting as advertisements, independently of the contestant lists. 
  
  Custom backgrounds, with your company's livery or other graphics is supported.
  
  Each prize can have a graphic, or it can default to a text-version.  Here is a different wallpaper, along with a now-dated Playstation prize:
  
  

  

  Different Wallpaper, drawing for a Game Console.  Winner not yet awarded.

  
  
• Prizes can be with and without illustrations.  Those without illustrations are displayed as text in the center of the screen.
   
• Prizes can be raffled in a preset order or prizes can be selected at-will from a list, at the whim of the Prizemaster.
   
• Unannounced, unplanned, ad-hoc prizes can be awarded via an "unspecified prize." The Prize-master can switch between the original prize-list and ad-hoc prizes with a mouse-click.
  
  
• Prizes can optionally and automatically move to the next "available prize" as winners are chosen -- or the next prize can remain hidden until the Prize-master chooses to expose it.
  
  
• Awarded prizes are automatically removed from the Prize List so there is no risk of accidentally awarding twice.
  
  
• Accepted winners are recorded to a transaction file for later reporting. In the event of a power failure, results are safely recorded. The original and modified contestant list is backed up with each new ticket transaction and can be recovered up to the point of failure within moments.
  
  
• If a predetermined prize-list is not available, or if all "known" prizes are exhausted, the program switches to an "unspecified prize mode" without interruption. This allows the Prize Master to give away ad-hoc prizes, as Prize-masters and MC's often like to do.

Downloading:

To install this program, download the .exe and place in any folder on your hard disk.  Double-click the .exe to run - no installation required.

 

Since keyliner cannot afford a signing certificate, you will be prompted that the file is not safe (being downloaded from the internet).  Click "more information" and allow the program to run.

Follow these steps for a more professional installation:

Using the .exe from a download folder, or copying to a (my Documents) folder is a quick workaround for various Windows 10 and 11 security concerns.  Some vendors recommend this, but these folders are inappropriate for executable software.  Instead, the program should be copied to Program Files so it gains the protection of other Windows security features. Total time: about a minute.

A.  Download the .exe to a Download or Temp folder:

From Keyliner's public GDrive, click this link and download to a local temp or download directory.  Do not download directly into ProgramFiles. 

Download Link: Version 1.08
Folder link:  Folder Link - contains standalone .exe and other downloads.

File Link: AllFilesInOne.zip - includes sample wallpaper, etc.  Recommended.

prizeselect.exe MD5 check:
01-c1-81-be-8d-23-f6-a1-f4-a8-71-9e-a2-0d-a1-c5

SHA-256:
fcc8b435d84220a9dfb3ea6f24de85dc84a7141685de61ad4407c5e0fa843a78

When downloading, different browsers behave differently.
You will be prompted the file cannot be scanned.  Click "Download anyway"

Microsoft Edge:

Prompts "DirectoryPulse.exe" was blocked because it could harm your device. 

"Click See More" and allow the download.  With Edge, the file will appear in your Downloads directory with a random name, "Unconfirmed 780359.crdownloaded" (name varies). 

Rename the file to "DirectoryPulse.exe".

This link opens a directory, which includes the executable, wallpapers and instruction guide.  Each can be downloaded separately or download the all-inclusive .zip file.  To download, other-mouse click the file's name. 

B.  Mark the program as safe-to-run:

(This step may not be needed if downloaded by Edge and you clicked "More / Download Anyway")

Using File Explorer, right-mouse-click the downloaded (and re-named) .exe 
Select "Properties"
Check [x] Unblock.  This removes the "mark of the web." 

                 Click for larger view

* Only do this if you trust keyliner *and* only if downloaded from keyliner's public GDrive. 

If "Unblock" is not visible, it has already been unlocked (by Microsoft Edge).
Once [x] Unblocked is clicked, this security menu disappears.

 C.  Create a Program folder to hold the program:

Using File Explorer, open folder C:\Program Files,
Create a folder  C:\Program Files\PrizeSelect

D.  Copy the .exe to ProgramFiles\PrizeSelect:

Using File Explorer,
Copy/paste the .exe from the temporary/download folder
    to C:\Program Files\PrizeSelect

Do this copy as a two-step, copying from the download folder into Program Files.  Windows security will not let you download directly into Program Files (technically, you will not be able to remove the "mark of the web" if downloaded directly into Program Files).

E.  Create a Start Menu Tile:

Using File Explorer, in C:\Program Files\PrizeSelect
Right-mouse-click the executable and "Pin to Start"

 
The program is ready to run.  See icon on Start Menu.

I recommend downloading the Installation/User-guide (PDF) and wallpaper files -- especially the default wallpaper.  The "All-in-one zip" contains the executable, several wallpapers and complete documentation and sample files.  It is a small download. 

Most of the features in this program are somewhat hidden
 in order to give the program good "curb-appeal"

The PDF demonstrates various scenarios on how to best use the program,

along with a list of helpful tips and tricks

Try it out:

You can literally download the EXE; double-click it and start using it now, for a quick test.  It will prompt for a ticket-number range and will assume an 'unspecified prize list.'  Begin drawing samples within 20 seconds.

 
The remainder of this post highlights some of the most commonly-used features.  The installation PDF has full details, including great examples and best practices. 


Feature Details:

Contestant names can be copied and pasted into a configuration file, typically from Excel. Names can also be imported from one or more external ASCII files, allowing automated population. Typically, links are made to external lists, such as club-memberships, school rosters, etc.

Individual names can also be added on-the-fly, as in this screen shot (note the holiday themed wallpaper):

Click image for larger view, "Back" to return

Once names / raffle-numbers are added, the contestant list can be modified with rename, delete and find-duplicates. This is a screen shot showing a contestant list, along with a different wallpaper:

Standard Raffle Ticket Drawings:

Prize Selections can be started in an ad-hoc mode (on the fly, without configuration). This is particularly useful for traditional raffle-ticket drawings where you are using rolled-tickets. Data-entry for the tickets is easy, where you enter all the tickets in one transaction.

Illustrated, tickets #13456 through 13521 are being added in one step:

Multiple ticket ranges are supported, as are multiple rolls, and dis-contiguous numbers. Sold tickets can be managed from an inventory screen, in this case, showing numbered tickets instead of names:

Tickets can be entered by name, e.g.,  John Smith, or John Smith x15 (purchased 15 tickets).  You cannot mix the two different types of data-entry.

From the download library, be sure to download the 50-page PDF User's Guide. This explains all features, including how to use graphics with the prizes, and how to pre-load the contestant lists.

Version History:
Full details in the Installation PDF guide.



2022.0528
1.08 Recompiled with Visual Studio 2022 Dot Net 4.8.  No other changes.

2015.1220
1.07 Recompiled with Visual Studio 2015. 
Added a self-signed certificate, but it will still show as an unknown author.
Everyone clamored for user-defined wallpaper, which was finally added. 
Added cool support for changing "One win per contestant" switch, on the fly, controlled by inventory -- this means no more screw-ups by your Prize Master/Master of Ceremonies. 
Fixed bug by adding "x5" support in both INI files and in Files=import.text. 
Improved font sizes in several areas. 
Expanded and enhanced documentation; included new screenshot illustrations.   

1.05 Added Slot-machine effect while names are being drawn.
Changed the "List All" page, adding more options.
Improvements in documentation.

1.04 Added support for copying Winner list to clipboard.
Modified "Autoadvance to next Prize" to behave more smoothly when on an "unspecified prize"; it automatically advances to a new Unspecified prize even if auto-advance is disabled. In practice, this feels more natural during an impromptu prize-give-away.

1.03 Added support for "Discard All".
Added support for non-ranged, single-raffle-ticket data entry.
Fixed bug when loading backup.ini files which contained non-ranged numbers.

1.02 Initial Release

I have had fun writing this program.  

If you use it, drop me a note and tell me how it went.  

Learn how to write a program like this.
Search Amazon for "War and Peace Programming" - Programming in C#.  These are keyliner's books, written for beginning programmers. 


Related articles and links:
 

Download Link:
Folder link:  Folder Link    This includes the all-in-one .zip

File Link: AllFilesInOne.zip
PrizeOrama - Raffle-ticket selection in Excel

PrizeOrama - Raffle Ticket Selection in Excel

Excel How-to: How to write a Raffle-ticket prize selection spreadsheet. This is the "PrizeOrama" sheet which you can also download. See also this more improved program: Keyliner PrizeSelect.

My sister called with this problem: Her son's grade school has 300 students and each are eligible to win prizes based on school and volunteer work. Students can earn 1 or more chances (much like a raffle ticket). She went on to say last year they wrote each student's name on a slip of paper and tossed them in a hat -- but with many students earning 15 or more chances, she said she'd never do that again.

Having raffle-like drawings can be easily automated in Excel with macros. If you have never written an Excel macro, this will be fun project and the instructions below should take less than an hour to write. When you are done, you will have a random name selector.

You can also download a completed version using this link.

Click on this Keyliner Public GDrive link:
PrizeOrama Excel Spreadsheet Download

Author's Note: Since this article was written, I developed a much more sophisticated prize selection / prize drawing program, which can be found at this link: Keyliner's PrizeSelect. This program is also free.


The completed data-entry sheet looks like this:

Winners are calculated in a result-sheet that can be displayed in a variety of different ways:



Constructing the Initial Sheets

1. In Excel, open a new workbook and name two of the sheets as "Names" and "NameResults". These names will be required by the macros. (Other-mouse-click the default sheet, "Sheet1" and choose Rename.)



2. As illustrated at the top of this article, type column headings on row 4:

A4 = "Name"
B4 = "Tickets"

Cell A5 (e.g. "Bobbie" contains the first test record)
Cell B5 contains the count of the number of tickets Bobby earned.

Continue to build a half-dozen test records.

(Optionally, if you are using numbered Raffle-tickets, type the lowest-numbered raffle-ticket in the first two cells (A5, and A6), then build a simple addition formula and fill-down to the largest-number; No need to put in a ticket-count, "1" is assumed.)

e.g.
65444 (in cell A5)
65445 (in cell A6 - to get past the Range-name in cell A5)
=A6+1 (in cell A7)
2a. Highlight cell A5 and "Range-name" the cell as "NameAnchor".

Select Formulas, Define Name
Name the cell "NameAnchor"

3. Expose Excel's Macro Development Tools:

a. Click the Microsoft Office Orb (the old File Menu), choose the "Excel Options" button at the bottom of the screen. In the "Popular" section, check "[x] Show Developer tab in the Ribbon"


4. Build an initial Macro by following these steps:

a. From Excel's top menu "Developer," click "Record Macro"

b. For the Macro Name, type "SelectWinners" and "store macro in 'This Workbook'". No need for a shortcut key. Press Enter and the macro will begin recording, noting the small blue square in the lower-left of the screen.

c. Click the mouse in Cell A5 ("Bobbie") - just so the macro can record something.

d. On the toolbar, click "Stop Recording"


5. Open the Visual Basic editor (Macro Editor):

a. While still in the sheet, press Alt-F11 (Visual Basic VBA editor).

b. On the tree-side, tunnel to "Modules, Module 1". This is where the code that drives this program will live. You will see a Range-Select statement in the macro; this is what you recorded in the previous step.

At any time you can close the macro-editor (clicking the "X"); changes are automatically saved and you are returned to the sheet. Press Alt-F11 to return to the editor.


c. Above the "Sub SelectWinners()" line, insert a few blank lines and type these three statements:

Option Explicit
Public iRecordCount as Integer
Public iEntryCount as Integer

It will look like this:


For technical reasons, all macros should have an "option explicit" line -- even though Microsoft does not default it. The other two statements declare variables.


6. Write the main "SelectWinners" Routine:

This routine is the longest routine in the program and it will take a few minutes to type. As you type, indent with spaces and make the code look pretty. Comments (green) begin with tic-marks and long-lines can have line-continuation characters:

(click image for larger view; click right-x to return)

Still within the same sub-routine, complete the code with these remaining statements. Both the code above and this code lives within the Sub/End Sub statements:


7. Write the Private Sub A100_ProcessRecords() routine:

a. Below the "SelectWinners" 'End Sub' line, press Enter a few times to insert blank lines, then literally type these commands in order to build a new routine:



The details within the "Do Until / Loop" will be filled in a moment. For now, just stub-in the routine.

b. Now fill in the Loop details for the A100 routine (this code lives within the Until/loop statements):

(Click for larger image; click "right-x" to return)

8. Write the A200_SortNameResults routine:

Continue with the next routine, which is typed below A100's "End Sub". You are almost done:



9. Write the ClearPreviousResults routine:


Building a "Select Winners" Macro Button

As illustrated at the top of this article, it is handy to have a button that kicks off the macro. Build a button and attach it to the "SelectWinners" macro with these steps:

Close the macro editor and return to the original spreadsheet.
Click on the "Names" tab and follow these last steps:

1. Using the top-menu, "Developer"

a. Click the "Insert" tool and then the "button" Forms Control Tool. Be sure you do not select the ActiveX version.


b. Once the button is clicked, move the mouse into the sheet and click-and-drag an outline of a button somewhere on the form where you want a "Select Winner" push-button. Let go once the outline is in place.

c. When prompted for a macro name, choose "SelectWinners", then "OK". Note: You can Control-Click the button to resize, move and change the button's properties.

Alternately, you can run the macro manually by clicking the top "View" menu, Macros, Run Macro. Choose "SelectWinner" from the list.

Testing

To test the macros, confirm you have test (student) names in cells A5 and down. For a few of the records, type a "Ticket" count of 5.

Click the "Select Winner" button.

Open the "NameResults" tab (bottom tab) to see the randomly-selected winners. Note that those people with more than one ticket are represented multiple times in the list. Each has a random number, but all numbers were sorted randomly. If you have three prizes to give away, choose the top-three names. Remember, the results list shows all eligible people in one list -- with every ticket represented.

Click "Select Winners" a second time to re-randomize the list.

Saving the Sheet

If you are using Excel 2007/2010, sheets with Macros must be saved in a special way. Select File (orb), Save-As. Change the filetype from "Excel Workbook (*.xlsx)" to "Excel Macro-enabled Workbook (*.xlsm)" - you must do this or the macros will be stripped out of the sheet.

When you next load the sheet, you will be prompted to "enable macros". See Microsoft Help for additional information on how to always allow macros on this sheet.

Description of Steps:

This article is not intended to teach macro programming, but here is a brief description of the program's logic.

The main module, "SelectWinners" calls A100_ProcessRecords.

A100_ProcessRecords starts in cell A5 (the first name) and moves one record at-a-time down the list. With each found record, it copies the (student's) name to the second "NameResult" sheet. If the student has more than one ticket, their name is copied multiple times.

As each name is copied, it is assigned a random number.

Once all the names are copied, the second sheet is sorted numerically by the random number. Note how the sort routine knows how many records were loaded and it uses this count to highlight the sort records.

Once sorted, the top name should be awarded prize #1, second name prize #2, etc. Usually, the grand-prize is awarded third. The downloaded version of this sheet (see below), contains other interesting features.

Disqualify a person by putting a negative number for the count (-e.g. -1). With this, their name stays in the list, but they do generate a random number and in effect become ineligible for the raffle.

If you need to have the person's name and phone number, type both in column A.

Downloadable Version:

This spreadsheet is available on Keyliner's Public GDrive site. You can download a complete sheet, with all macros, free of charge and you are welcome to use this routine as you please. If you use the sheet, drop me a note; I'd like to hear from you.

PrizeOrama Excel Spreadsheet download


The downloadable version contains several other interesting features, not documented here, along with full instructions on the features and use. My apologies for not having cut-and-paste code in this article; I've never figured out how to make those types of textboxes....

Other comments:

• Remember, only click the Select Winners button one time -- all winners are randomized and calculated at the same time.
• If a person has multiple tickets or the person is listed multiple times in the list, they have multiple chances to win and can win multiple prizes.
• At this time, the sheet does not allow a person to win "only once" - even if they have multiple chances; this is a future enhancement.
• The downloaded version has many more features than this article discusses.

Related Links:
PrizeOrama Excel Sheet Download
PrizeSelect - a more complete raffle ticket program

Using VBA to send Email

VBA Code Example: How to send an email from VBA, passing through the current user's Outlook session.

Using Visual Basic for Applications (VBA), you can send an email from within your MSOffice application via a locally installed copy of Outlook. This code example is from an MSAccess button event. Since it took me nearly a day of research to get this working properly, I decided to upload the code here.

1. In your VBA code, add this recommended statement at the top of the code, above all other procedures:
Option Explicit

2. In the VBA code, select the Top Menu: Tools, References
In the list, scroll until you find "Microsoft Outlook 12.0 Object Library"
Select the library; click OK (Your version may differ)

3. Create a button in your Form (e.g. btnSendEmail)

4. In the button's CLICK event, add this code:

Important Note: I have tried and given up! I cannot get the copy-and-paste to work correctly with Internet Explorer -- When it copies this text, it looses track of the carriage-returns. With Firefox, this works properly. See below for work-around. Suggestions are welcome. Email me if you would like a copy sent directly to you.

Private Sub btnSendEmail_Click()
Dim strmsg as string
Dim OutlookObj As Outlook.Application
Dim OutlookMsg As Outlook.MailItem
Dim OutlookRecip As Outlook.Recipient
'Dim OutlookAttach as Outlook.Attachment

Set OutlookObj = CreateObject("Outlook.Application")
Set OutlookMsg = OutlookObj.CreateItem(olMailItem)

With OutlookMsg
Set OutlookRecip = .Recipients.Add("tim wolf")
'OutlookRecip.Type = otTo

.Subject = "TEST MESSAGE"
.Body = strmsg & vbCrLf
.Importance = olImportanceNormal

'If using Attachments, uncomment this and the line near the top of this routine
'If Not missing(AttachmentPath) Then
' Set OutlookAttach = .Attachments.Add(AttachmentPath)
'End If

'Resolve each recipent's name:
For Each OutlookRecip In .Recipients
OutlookRecip.Resolve
Next

.Display
'or use .Save and .Send to automate the process
End With

Set OutlookObj = Nothing
End Sub

Internet Explorer Users: Follow these steps:

• Copy the text
• Open Microsoft Word; paste text
• Re-highlight text in Word
• Paste into your code-editor (Excel)

Comments:

• In the source-code, you must use the keyword "SET", as illustrated. This seems redundant, but the word is required. Without it you will see "Run-time error '91': Object variable or With block variable not set".
  
  
• If you have troubles with the Class or Library not defined, see step 2.
  
  
• Comment-out the .Display line and uncomment .SEND and .SAVE if you want the email to send automatically.

Related Keyliner Articles:
Excel UDF (User Defined Functions)
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama

Excel - Import Leading Zeroes

HowTo: Import numbers into Excel without stripping, truncating or loosing leading zeros. A classic example are zipcodes being imported from an external source. Codes, such as "08401" can import as "8401".

When Excel imports numbers, it assumes the "General" format for each column. Fields, such as part numbers, zipcodes, SSN, and other non-numeric numbers will have leading zeroes stripped because Excel thinks they are real numbers. Prevent this by forcing the import as TEXT.

In order to do this properly, you must run Excel's Import Wizard (steps 1 through 3). For this article, assume a simple tab-delimited data file with City names, State codes, and ZipCodes (file columns may not line up this pretty in your actual data file):


Launch Excel and start at a blank sheet

1. Select File,Open: Tunnel to your test data file. Be sure the file extension is .txt, .tab, or .csv in order to trigger the Import Wizard

2. On the 'Text Import Wizard', step 1 of 3, choose "Delimited"

3. On step 2 of 3, choose ([x] Tab)

4. Here is the key to the problem:

On step 3 of 3, click the first column, then Shift-Click the last column (highlighting all columns). Select Option "Text" and complete the import. (Most files should import all columns as text. Optionally, you could select only the ZipCode column.)


Results in Excel will look like this:


Related Keyliner Articles:

Excel UDF (User Defined Functions)
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama

Excel: Coloring Alternate Rows

Excel How To Automatically Color Alternating Rows in Excel using Conditional Formatting. How to highlight every 5th row. (Keywords: Greenbar; every other row)

Follow these steps to "conditionally" format cells so they have alternating colors on every other row (or other number). Doing this can make a sheet easier to read and follow. The formatting automatically adjusts when new rows and columns are inserted or deleted.


Steps for Excel 2003 and Older
(Steps for Excel 2007, below)

1. Highlight the cell range where you want the alternating rows or highlight the entire rows.

2. Menu: Format, Conditional Formatting

3. In the 'Condition 1' pull-down, select "Formula Is"

• In the Formula bar, type this formula, including the equal-sign:

=Mod(Row(),2)

• Click the Format Button.
• Along the top row of tabs, choose [Patterns]. See below for an illustration.
• Change the Background color to a light tan or soft-green
• Okay, Okay

Steps for Excel 2007(with the Ribbon Bar Menu; see above for older versions of Excel)

1. Highlight the range of cells where you want the alternating rows to be applied or select the entire rows.

2. On the "Home" Ribbon, select "Conditional Formatting".
Click image for a larger view; click Back to return.


3. Select New Rule

• In the Rule Type Dialog, choose "Use a formula to determine which cells to format"
• In the formula bar type this formula:

=Mod(Row(),2)

• If copying and pasting, be sure you don't end up with any quotes, as in ="Mod(Row(), 2)"
• Click the "Format..." button; then choose the [Fill] tab.
• Choose your favorite color; click OK, OK

Variations - Every 5th row:

The formula:  =Mod(Row(),5) highlights all rows except those divisible by 5 -- in other words, each row, except those evenly divisible by 5, would get a "1,2,3, or 4" in the remainder.  This inverts the highlights, where every row is highlighted but the fifth ones.  This is probably not what you want.

To highlight every 5th row, use a slightly more complicated formula that resolves to a True or False:

=IF(MOD(ROW(),5)<>0,FALSE,TRUE)

Change the word "Row" to "Column" to highlight alternating columns.



Comments:

The "row()" function returns the current row-number. As an aside, =row(C15) returns row-15.

The Mod function divides the current row number by (2) and records the "remainder" (remember grade-school division?). With this division, the rows are either odd or even (a remainder of zero or one).

Notice the Conditional format is set to trigger when the formula is "true". Here is the trick: In Excel-lingo, a "1" is true and a zero (0) is false (actually, in this case, any number greater than zero is considered True). Because the MOD formula always resolves to a 1 or zero (T/F), this is enough to set the conditional and trigger the formatting. A simplified example:



Notice how the row you highlighted can be either shaded or not -- the calculation is based on the physical row number, not on where you started the process.


Performance Considerations:

Applying a conditional format to individual cells introduces extra calculations with each cell.

If applied to tens of thousands of cells, this has the potential to impact performance. Although I have not tested this, I suspect conditional formatting applied to the Rows (rather than the cells) is more efficient because the rows would inherit a default formatting and each cell within would not have to be re-calculated.

On large sheets, I recommend applying conditional formatting to the rows instead of individual cells.



Modifying Existing Rules:
To change an already-built rule, click "Conditional Formatting", Manage Rules. Select the specific rule to change; then "Edit Rules".

Click "Conditional Formatting", "Clear Rules" to remove the rule.

Related Keyliner Articles:
Excel UDF (User Defined Functions)
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama

Excel - VLookup How To

How To: Use Excel's powerful VLookup feature to make cross-table / cross-lookups. This is a comprehensive tutorial, showing best practices on all aspects of a VLookup. These instructions work with all versions of Excel.

Synopsis:
 =Vlookup(what you are looking for, where, which column, exact-match=False)
or
=IfError(Vlookup(what, where, column, false),"Not Found")

Summary of steps:
1. In a separate sheet, create table of values to lookup
2. Range-name the lookup table (minus header row)
3. Build VLookup, selecting base-1 column name

Contents:

• VLookup - Name the Range
• Parameters
• Selecting the Range - the "trick"
• Improved VLookup using IfError
• Fixing Range Names
• Performance Concerns
• Dirty (Numeric) Data
• Non-exact searches (within a range of values)

VLookup Introduction:

In Excel, a VLookup allows your sheet to lookup another value from another sheet, cross-referencing catalog information into the current sheet.

For example, a Master Account number table could show a description, budget numbers and the person in-charge of that account.  Then, in any number of other sheets, you can lookup the account number and return those centralized values into the current workbook:

Click for a larger view

In other words, your sheet could reference an Account number (illustrated, in Cell B5), and multiple other values from the lookup table, such as the description and budget-amounts, can be returned in a cross-lookup.

If data in the lookup table changes, for example, assume the dollar'd budget amount changes, the new sheet picks up the new value automatically. With this, one group of people can maintain the account list, and all other linked sheets will see the most current values.


VLookup Setup:

---

Build an Account Number Master list.

In a (usually) separate sheet, make a list of values to lookup, with the Account number as the first column.  This can be account codes, employee numbers, part-numbers, people names, etc.


1. Open a new, blank spreadsheet.
2. Load or type account information, where:

• The first column must contain a unique value (the Key) for each record.
  This is the Account Number
• No duplicate entries are allowed in the first column.
• Any number of columns can follow the first (Description, budget, etc.)
• Column headings are recommended, but not required.
• The list *must* be sorted alphabetically/Numerically by the first column. (Technically, it 'should be' sorted if you are using "exact-matching"; it 'must' be sorted if not exact-matching. Sorting is always recommended.)

Your list can contain hundreds to thousands of entries and the keys do not have to be numeric.


Name the Range:

Although optional, I recommend naming the data-range. Follow these steps:

3. Once the lookup values are typed, highlight the data (all rows, all columns, *not including* the Header row)

a. 'Other-mouse-click' the highlighted cells, choose "Name a Range"; name the range a meaningful name, such as "MyAccounts".  No spaces.

a2. (Alternately: Click top Menu Formulas, Define Name and type the range, using dollar-signs to lock the rows and columns.  For example:  $A$3:$D$13)


a3.  (Another alternate method):  Highlight the data, minus the column headers, then click in the area directly above column A.  Type the range-name there.



The Range-name makes future work easier, see below for more information.

b. Save the entire sheet to disk, giving it a meaningful name such as "Accounts.xlsx" or "FY17Accounts", etc.

Once saved, leave the sheet open while building the Vlookup formulas. In the future, this sheet can be opened or closed and it will still work properly -- but if already opened, lookups are faster.


Using VLookups in your Sheet

---

4. In a new sheet (File, New, Blank workbook), where you want the results to appear, move to an arbitrary cell (B5).

5. Literally, type an account-number to look up: For example, "2356"

Click for a larger view

6. In another cell, say C5, begin the lookup formula by typing "=VLookup(" (open parenthesis).

Note the popup help:

Click illustration for larger view; click right-x to return.

a. For the first parameter, after the parenthesis, click cell B5 (2356). This is the value you are trying to look up.

* Type a Comma (,) to move to the next parameter.

b. While still in the midst of the formula's second parameter, take the mouse and click anywhere in the Account look up sheet. Click one time to activate the sheet, a second time to select a cell; any cell.

If the sheet is covered or otherwise not visible, click the top Excel menu View, Switch Windows.)

(Because you are using a range-name, you do not need to highlight all the data, just click anywhere in the sheet, randomly.  This sets the sheet's name. )

In my example, the Account Lookup sheet's bottom tab was renamed to "MasterAccounts"; if you did not rename the sheet, it will show as "Sheet1", either is acceptable.

Once selected, the second parameter in the Vlookup formula will look like one of these, where the "$B$7" (the random cell clicked) is unimportant:

=Vlookup(B5,[Accounts.xlsx]MasterAccounts!$B$7 or
=Vlookup(B5, [Accounts.xlsx]Sheet1!$B$7


c. Here is the trick:

While still building the second parameter, backspace over the newly-selected address, deleting the "$B$7" (or what ever cell clicked) -- backspace up until, but not including the exclamation (also called a "bang").

d. Replace the $B$7 (or what ever) with the Ranged-name "MyAccounts":

After backspacing and typing the RangeName "MyAccounts":

=Vlookup
(B5,[Accounts.xlsx]MasterAccounts!MyAccounts

or

=Vlookup(B5, [Accounts.xlsx]Sheet1!MyAccounts


e. Type a comma (,), after "MyAccounts," to continue the formula.

The next parameter is column-data you want returned into the sheet. For the current VLookup cell, this will be the description (column 2). Type a "2", followed by another comma:


f. Finally, type a comma-",False", which tells the formula it must find an Exact-match in the lookup table; this is the most common option.

g. Close the parenthesis) and press Enter. The completed formula:

=Vlookup
(B5,[Accounts.xlsx]MasterAccounts!MyAccounts,2,False)

=Vlookup(B5, [Accounts.xlsx]Sheet1! MyAccounts,2,False)

Results: The Description for account code 2356 ("Networking") is returned.


Lookup another Column in the same Table

In cell D5, one cell to the right, build another VLookup formula, returning the budget amount:

=VLookup (B5,Accounts.xlsx!MyAccounts,3,False)

where:

• Accounts.xlsx is the name of the sheet
• "MyAccounts" is the name of the named-range
• "3" returns the budget from the third column
• "False" means find an exact match

Cells C5 and D5 (the Vlookup formulas) can be copied and filled down as you would any other Excel formula.


Improved VLookup Formula:

---

There is a possibility the Vlookup may not find a given account-code; usually because of a mis-keyed value or the account does not exist. Test this now by doing the following:

a. In the original cell B5 ("2356"), change the account code to a non-existent account ("2000"). Results: The Vlookup formulas return "#N/A" (Value not found).

Improve the Formula using IFError:

b. Cosmetically, this can be improved with this formula in Cell C5 (all on one line):

IfError(VLookup(B5,Accounts.xlsx! MyAccounts,2,False),"Not Found")

c. In D5, use this formula (note the double-quotes ""):

IfError(VLookup(B5,Accounts.xlsx! MyAccounts,3,False),"")

(Click illustration for larger view, Back to return)

where:

• =ifError says if "any part of the included formula generates any kind of error (such as a #N/A"), put a different value in the cell. Otherwise, use the original value.
   
• Do not use an equal-sign in front of the VLookup clause
   
• If an error was found, the formula returns the literal text "Not Found".
  
  Cell D5's formula, the second VLookup was changed to return a null (empty) string (quote-quote) if there was an error.
   
• Depending on your needs, the formulas could also return a zero (0) instead of the text "Not Found".

Possible Errors:

• #N/A (#NA) - An exact match was not found in the lookup table.
• #N/A The lookup cell address may have shifted or changed, confirm the first parameter is pointing at the value you want to lookup.
• #Name? - An incorrect or misspelled RangeName (aka "MyAccounts") or the range-name does not exist.

Range-Name Issues:

---

If you choose not to use a range name in the VLookup, you will have to use a cell-range.

=Vlookup
(B5,[Accounts.xlsx]MasterAccounts!$A3:D13 ...

where $A3:D13 are all the values typed in the lookup table.

These types of ranges are inherently fragile. If new values are inserted into the lookup table while your original sheet is closed, the lookups will not see the change and the wrong range will be selected, often resulting in "#NA" values.

With explicit addresses (A3:D13), the range may or may not expand when new rows are inserted into the table, depending on which linked sheets were opened at the time. Compare this to a range-named table, where inserted records automatically expand the range-name. In other words, explicit addresses (A3:D13) are risky.

However, with either the RangeName or explicit-address examples, newly-inserted records at the top-most or bottom-most records will not expand the range-names correctly and VLookups will have sporadic failures when they are near these areas.  When inserting new values in the lookup on the first or last row, check the range-name to make sure it is still accurate.


Confirming an existing Range-Name:

When inserting records on the top-most or bottom-most rows, confirm the range with one of these methods:

Method 1:

Click the down-doodad right above column A, choose the Range-name "MyAccounts".

Excel will highlight the cells.
Confirm the highlight encompasses all rows and columns.
See below if you need to change.


Method 2:

Use this method to check and, if needed, to change the selected range in the Range-name:

In Excel 2007's ribbon menu, choose "Formulas", Name Manager or press Control-F3.
Confirm the range-name cell address (e.g. $A$3:$D$13). The dollar-signs are important for reasons not described here. To change the range-values, click the red-arrow and re-highlight the table. This example shows a sheet with other range-names:

(Click for a larger view, Back to return)

As an aside, you can see all linked sheets (from your main sheet, not from the Accounts Sheet) by selecting the top-menu Data, Edit Links.  "Accounts.xlsx" will show as a linked sheet.  


Other Range Names

In the lookup-table sheet, where the MyAccounts range lives, you can have other sections devoted to other lookup values. For example, there might be a Department Lookup table, CategoryCodes, Employee lookup, etc. All can live in the same sheet and all will work well if you use Range Names.

Performance Concerns

Vlookups are somewhat expensive in CPU processing. If your sheet has thousands of lookups, the performance of the sheet could be slow. Consider the following to improve the speed:

• Open the lookup sheet *before* opening your main sheet.
• On really large sheets, set manual re-calculation: Menu: Formulas, Calculation Options, Manual. Understand the implications before doing this.
• The lookup sheet can be closed and the formulas will still work; you will be prompted for establishing the link when you open your main sheet.

Dirty Data:

---

With numeric lookups, occasionally, the lookup key values may look like numbers, but are really textual. This can happen if the table is populated from a mainframe download or other ASCII import.

The symptoms are this: Both the lookup table and the lookup value might show '2356' -- but one is numeric and the other is character. When run through the VLookup, you will get an unexpected "Not Found" or NA.

To test the textual-data, make a dummy formula in another section of the lookup-sheet. Add +1 to your suspected numeric value. If it is text, the answer will be "1"; if numeric it will be '2357'. Do the same for the test value in the vlookup sheet.

If the 'numeric' values in the lookup sheet are really character, correct the issue by converting the lookup column to numeric data. Do this with these steps in the lookup columns:

a. Insert a new dummy column.
b. Use this formula: "=Value()"
c. Fill the formula down the column.
d. Copy the formulas; use Paste-Special, Values-only, over the top of the original "numbers".
e. Delete the dummy column.

You may also find the original data is polluted with non-printable characters -- usually a tab. Consider using similar steps with a =Clean() formula to spiff the data.


Non-Exact Searches

---

The last parameter in the VLookup (,"false"), tells Excel that an exact match must be found. This is the normal setting for the formula. However, you can specify a non-exact match.

For example, if the dollar figures were between $1,000 and $2,000 dollars, use such-and-such General-Ledger number. But if the number were between $2,000 and $10,000, use a different number.

Consider this table, which shows purchase-order approval ledger information:

Within your own sheet, a lookup of various items purchased, might look like this, where different ledger-numbers and approving people are displayed, depending on the dollar-amount spent:


The formula in cell D4 ($33.56 purchase) would look like this:

=VLOOKUP(B4,Accounts.xlsx!LedgerAccounts,2,TRUE)

where:

• "LedgerAccounts" is a new RangeName in the lookup sheet.
• "2" returns the second column (Ledger-account number)
• "true" says allow inexact matches

With this type of lookup, you do not need the IfError logic used in previous examples -- there is no need unless you expect negative numbers to arrive.

In the lookups, Bob (Ledger account ABC) gets to approve all dollar purchases less than $1,000. If the purchase were $1,000.01, Mary gets the right to approve (not Bodine).

All purchases $30,000 or larger belong to Margret. The rule is this: If no exact match is found, find the next larger value that is less-than the looked-up value.

This concludes the VLookup tutorial. Your comments are welcome.

Related Keyliner Articles:
Excel UDF (User Defined Functions)Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama