How to highlight odd or even numbers in Excel?
Highlighting odd or even numbers in Excel is a practical technique that helps users visually analyze data, immediately spot patterns, and make information much more accessible—especially within large worksheets. For instance, you might want to differentiate test scores, list IDs, or alternating numeric groups. Below, we outline several effective methods to accomplish this task: using Conditional Formatting, leveraging the Kutools AI Aide, and using a VBA macro for automatic highlighting of odd or even numbers. These solutions not only make your data more presentable but also reduce the risk of missing important trends or anomalies.
➤ Highlight odd or even numbers with Conditional Formatting
➤ Highlight odd or even numbers with Kutools AI Aide
➤ Highlight odd or even numbers with VBA Code
Highlight odd or even numbers with Conditional Formatting
Conditional Formatting lets you automatically apply formatting (such as a fill color) based on rules. It’s perfect for highlighting odd or even numbers without manual searching or sorting. Follow the steps below:
1. Select the range that contains the values you want to evaluate (a single column, multiple columns, or the entire sheet—but only include cells you want formatted).
2. On the Home tab, click Conditional Formatting > New Rule.
3. In New Formatting Rule, choose Use a formula to determine which cells to format. In the box Format values where this formula is true, enter one of these formulas (for odds):
=MOD(A1,2)=1=ISODD(A1)Tips:
- A1 must be the top-left cell of your selected range. Adjust it if your selection starts elsewhere (e.g., B2).
- Use a relative reference (e.g., A1, not $A$1) so the rule applies correctly across the whole range.
4. Click Format > Fill and choose a highlight color, then click OK.
5. Click OK again to apply. Odd numbers will be highlighted immediately. To highlight evens, repeat Step 3 with an even formula.
Notes:
- To highlight even numbers, use:
=MOD(A1,2)=0=ISEVEN(A1) - Conditional Formatting is dynamic—if a value changes from odd to even (or vice versa), highlighting updates automatically.
- Non-numeric cells (text, errors) are ignored by
ISODD/ISEVEN. If numbers are stored as text, convert them to numbers first. - To use different colors for odd and even values, create two separate rules (one for each formula).
- If nothing is highlighted, recheck the selected range, ensure the formula’s starting reference matches the top-left cell, and confirm there are no leading/trailing spaces in the numbers.
Highlight odd or even numbers with Kutools AI Aide
Kutools for Excel is an add-in designed to enhance and streamline Excel’s functionality, particularly for users seeking quick, automated solutions for repetitive or complex operations. The Kutools AI Aide feature enables you to highlight odd or even numbers across any range using natural language instructions, saving you from configuring formulas or manual rules.
After installing Kutools for Excel, open Excel and click Kutools > AI Aide to display the “Kutools AI Aide” pane on the right side of the worksheet.
- Select the data range in which you want to highlight odd or even numbers.
- Type your requirement in the chat box using everyday language. For example:
Highlight odd numbers:
Highlight odd numbers in the selection with light blue color.
Highlight even numbers:
Highlight even numbers in the selection with light blue color. - Then, either press Enter or click the Send button. Kutools AI will process your instruction, and once ready, simply click the Execute button to apply the highlighting.
This AI-powered method is straightforward and accessible—even for beginners—since it does not require you to set up any formulas or conditional formatting rules yourself. The AI interprets your intent and handles the technical steps in the background, including choosing an appropriate color and ensuring that only the relevant numbers are formatted.
Advantages: Speed, flexibility, and the ability to use natural descriptions. Kutools for Excel must be installed and activated. If the AI cannot recognize your request precisely, try rephrasing or providing more specific instructions for improved results.
Both Conditional Formatting and Kutools AI Aide provide effective ways to visually differentiate odd or even numbers. Conditional Formatting is ideal for those who prefer built-in Excel capabilities and direct manual control, while Kutools AI Aide provides a faster, hands-free experience. Choose the solution best aligned with your routine and preferences. For more useful Excel strategies, our website offers thousands of tutorials covering diverse scenarios.
Highlight odd or even numbers with VBA Code
For users comfortable with automation or working with larger or frequently changing datasets, a VBA macro can be a highly flexible method. Using VBA, you can create a script to automatically highlight all odd or even numbers in a selected range. This approach is particularly valuable if you need to reapply the formatting repeatedly, or want to incorporate the logic into custom Excel solutions.
Unlike Conditional Formatting, which relies on preset Excel features, VBA scripting offers deeper customization, such as cycling through different color schemes or including message prompts. However, it does require access to and understanding of the Visual Basic Editor in Excel.
How to use VBA to highlight odd or even numbers:
1. Click Developer tab > Visual Basic. In the window that appears, select Insert > Module, and paste one of the following codes into the blank module window. If you don't see the Developer tab, enable it via File > Options > Customize Ribbon.
To highlight odd numbers:
Sub HighlightOddNumbers()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
Dim HighlightColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to highlight odd numbers:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
HighlightColor = vbCyan
For Each Rng In WorkRng
If IsNumeric(Rng.Value) And Rng.Value Mod 2 = 1 Then
Rng.Interior.Color = HighlightColor
End If
Next
End SubTo highlight even numbers:
Sub HighlightEvenNumbers()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
Dim HighlightColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to highlight even numbers:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
HighlightColor = vbYellow
For Each Rng In WorkRng
If IsNumeric(Rng.Value) And Rng.Value Mod 2 = 0 Then
Rng.Interior.Color = HighlightColor
End If
Next
End Sub2. To run the macro, click anywhere within the code, then press F5 or click the Run button (the green arrow at the top toolbar). A prompt will appear for you to select the range to process. After confirming your selection, the macro will automatically highlight all odd (or even) numbers in the specified color in the chosen range.
You can adjust the HighlightColor variable to any color you prefer (e.g., vbGreen, RGB(135,206,250)). If you need to remove the highlight, select the range and use Excel’s Clear Formats function, or adapt the VBA code to reset the cell color.
Tips and considerations:
- This method works on static values: formatting remains unless you run the macro again after changing your numbers.
- Macros require saving the workbook as a macro-enabled file (
*.xlsm). - If you encounter a security warning, ensure macros are enabled in Excel’s Trust Center settings.
- If neither option in the prompt highlights your data, make sure you have selected only numeric cells.
If you experience issues running the macro, check your range selection for non-numeric cells, or review for syntax errors in the pasted code. For more advanced automation or batch processing, you can combine these macros and further parameterize them as needed.
Related article:
How to count / sum odd / even numbers or rows in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in