Apply Conditional Formatting Colors to ZIP Codes in Excel Lists
Question details
Apply different conditional-formatting colors to ZIP codes in a target range according to the regional ZIP-code list each code matches.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has several regional ZIP-code lists and wants matching ZIP codes in another list to display a distinct color for each region.
- Observed behavior
- Each region needs its own conditional-formatting rule and fill color because a cell can be evaluated against multiple regional lists.
Store ZIP codes in the same format in every range, preferably as text if leading zeroes are possible. Create a named range for each regional ZIP-code list before building the rules.
Create one conditional-formatting rule for each ZIP-code region
Use this method when each regional ZIP-code list needs a unique fill color in the same target range.
Conditional formatting applies one format per rule. Create a separate formula-based rule for every regional list, then assign the color that represents that region.
Select the ZIP codes for a region, then use the Name Box to the left of the formula bar or go to Formulas > Define Name. Enter a clear name such as HawaiiZIPs, WestCoastZIPs, or NortheastZIPs.
Select the cells you want to color, such as C2:C500. Start with the first data cell in the selection, such as C2, when writing the formula.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =ISNUMBER(XMATCH(C2,HawaiiZIPs)) or =COUNTIF(HawaiiZIPs,C2)>0, then select Format > Fill and choose the Hawaii color.
Repeat Home > Conditional Formatting > New Rule for every named ZIP-code list. For example, use =COUNTIF(WestCoastZIPs,C2)>0 and choose a different fill color for that region.
Open Home > Conditional Formatting > Manage Rules. Confirm that every rule applies to the same target range and move a rule higher if a ZIP code could appear in more than one regional list.
Use direct cell ranges when named ranges are not available
Use this option for a quick setup when the regional lists are stored in fixed worksheet ranges.
Color Regional ZIP-Code Matches with WPS Spreadsheet
WPS Spreadsheet lets you create formula-based conditional-formatting rules for a target ZIP-code range. Use a separate COUNTIF or XMATCH formula for each regional list and assign the matching fill color in the rule format settings.
- 1. Organize regional ZIP-code lists: Place each region's ZIP codes in its own worksheet range and, if needed, assign names to the ranges from the Name Box.
- 2. Select the cells to color: Highlight the target ZIP-code range in WPS Spreadsheet, such as C2:C500.
- 3. Add a formula-based rule: Choose Home > Conditional Formatting > New Rule, select formula-based formatting, and enter =COUNTIF(HawaiiZIPs,C2)>0.
- 4. Set the regional color: Open the rule's Format settings, choose Fill, select the color for that region, and confirm the rule.
- 5. Repeat and manage rules: Add one rule per region, then open Conditional Formatting > Manage Rules to verify the order and applied range.
Frequently Asked Questions
Why do I need a separate conditional-formatting rule for each ZIP-code color?
Each conditional-formatting rule has its own formula and format. To give Hawaii, West Coast, and Northeast ZIP codes different fills, create one rule for each list and assign a different Fill color.
What formula can I use if XMATCH is unavailable in my spreadsheet version?
Use =COUNTIF(HawaiiZIPs,C2)>0. Replace HawaiiZIPs with the named range or fixed cell range containing the regional ZIP codes, and replace C2 with the first cell of the selected target range.
How do I keep ZIP codes with leading zeroes from failing to match?
Format both the target cells and the regional lists as Text before entering or importing ZIP codes. A text ZIP code such as 02108 will then match another text value exactly.
What happens if the same ZIP code appears in two regional lists?
Both rules may evaluate as true. In Home > Conditional Formatting > Manage Rules, place the preferred region's rule above the other rule, or remove duplicate ZIP codes from the lists.
