How to Easily Apply Conditional Formatting Between Two Values

Setting Up Your Dataset for Analysis

Before initiating the application of any conditional formatting rules, it is absolutely essential to ensure that your target dataset is clean, consistent, and properly formatted. Specifically, for conditional formatting rules involving numerical ranges, the column targeted for analysis must contain pure numerical data. The presence of mixed data types, such as numbers stored inadvertently as text strings or cells containing non-numeric characters, will fundamentally interfere with the rule’s ability to correctly evaluate the “between” criteria, often resulting in errors or incorrect highlighting.

The initial and most critical step in the procedure is accurately defining the scope of the rule—that is, selecting the specific range of cells you intend to format. Failing to select the correct range will inevitably lead to formatting being applied incorrectly to irrelevant cells or not appearing on the intended data at all. Careful selection ensures the rule is executed precisely where it is meaningful for your analysis.

Consider a practical scenario involving performance tracking for various sports teams. We have a table detailing team names and their average points per game (PPG). Our goal is to quickly identify teams whose PPG falls into a specific target range. This visual technique is invaluable for coaches or analysts who need to monitor consistency or identify mid-range performers without the burdensome task of manually scanning potentially hundreds of entries. Suppose we have the following dataset containing information about various basketball teams. We will focus our formatting efforts exclusively on the numerical values within the Points column, specifically range B2:B14, as illustrated below:

Step-by-Step Guide: Utilizing the Highlight Cells Rules

The most efficient and user-friendly method for applying a ‘Between’ rule is through the dedicated submenu available within the Excel ribbon interface. This specialized feature is optimized for common conditional requirements and skillfully bypasses the necessity for manually constructing complex formulas, thus greatly simplifying the user experience and reducing the potential for error. The application process begins by accurately selecting the contiguous range of cells you intend to analyze and format. In our basketball example, this crucial range is defined as B2:B14, encompassing all the numerical point totals to be evaluated.

Once the target cells are highlighted and active, attention must be directed towards the Home tab, prominently located on the main application ribbon. Within the ‘Styles’ functional group, you will locate and click the Conditional Formatting button. This action triggers a detailed dropdown menu containing several established categories of rules. For standard range-based highlighting, you must proceed to select the Highlight Cells Rules option, which serves as the primary repository for rules based on fundamental comparisons such as ‘greater than,’ ‘less than,’ ‘equal to,’ and, critically, ‘Between.’

The final selection in this initial procedural phase involves clicking the Between option. This immediate action prompts the appearance of a dedicated dialog box, specifically engineered for the precise input of the lower and upper bounds of your desired numerical range. This highly intuitive, guided process ensures that even users new to conditional formatting can successfully and swiftly implement this powerful analytical tool into their workflow.

Defining the Boundary Values (Lower and Upper Limits)

The ‘Between’ dialog box mandates the precise definition of the range boundaries, a step that is absolutely paramount as it determines which specific cells will ultimately receive the specified formatting style. The dialog interface requires two distinct numerical inputs: the lower value (Minimum threshold) and the upper value (Maximum threshold). In our ongoing analysis example, we aim to highlight every value in the points column where the performance metric falls between 10 and 20, inclusively. Consequently, the value 10 must be accurately entered as the lower boundary, and 20 must be entered as the upper boundary into the respective input fields.

It is fundamentally important to understand the default behavior of the standard ‘Between’ rule concerning these boundary definitions. When utilizing the built-in ‘Between’ rule in Excel, the boundary values themselves are typically included in the scope of the formatting. This means that if a cell value is found to be exactly 10 or exactly 20, it will be successfully highlighted. This inclusive nature is often suitable for broad performance analysis.

However, if your analytical requirements demand strict exclusivity (meaning you only want values strictly greater than 10 and strictly less than 20), the standard rule will not suffice, and you would instead be required to utilize a custom formula rule, a more advanced technique discussed later. Once the desired numerical criteria—10 and 20—are entered, you must also select the visual formatting style to be applied. The application provides helpful default options, such as ‘Light Red Fill with Dark Red Text,’ which offers excellent immediate visual contrast. Once you are fully satisfied with both the numerical criteria and the preliminary formatting choice, you must click OK to execute the rule.

Reviewing the Application and Validation of the Rule

Immediately upon confirming the conditional rule, the software initiates an evaluation of all selected cells within the range B2:B14 against the established criteria: Is the cell value greater than or equal to 10 AND less than or equal to 20? Any cell whose value satisfies this combined logical criteria will instantly adopt the defined formatting style. This immediate visual feedback serves as crucial validation of the successful implementation of the conditional rule, providing instantaneous, digestible insight into the underlying dataset and saving significant time compared to manual auditing.

For the specific parameters applied to our basketball dataset, team point totals such as 15, 12, 18, and 20 (assuming 20 is present in the data and included by the rule) should be clearly highlighted, while outlier values like 5, 25, or 30 will remain entirely unformatted. This automated process drastically reduces the cognitive load required to scan and interpret large tables, efficiently transforming raw numerical information into an easily interpretable visual map of performance.

The resulting visualization effectively demarcates the mid-range performers based on the criteria we meticulously established. As compellingly demonstrated in the resulting image, the values falling between 10 and 20 now feature a distinctive light red background with contrasting dark red text, thus fully realizing our initial analytical objective: to visually flag teams within that specific performance tier.

Customizing the Visual Output of Formatting

While the standard formatting presets (e.g., light red, yellow, or green fills) are undeniably useful for rapid, preliminary application, conditional formatting provides extensive, detailed options for visual customization. It is fundamentally important to select formatting that not only effectively highlights the relevant data but also adheres rigorously to organizational branding standards or necessary accessibility requirements. Users are by no means limited to the predetermined styles; they possess the capability to define custom formats tailored specifically to achieve unique visual effects that communicate intent clearly.

When defining the rule, rather than simply accepting the default styling, users can access comprehensive formatting options to select robust alternatives. For instance, if the numerical range between 10 and 20 represents an optimal or desired performance bracket, you might logically opt for a positive color scheme, such as selecting a vibrant green background paired with dark green text. This immediate and intuitive association of green with positive performance metrics significantly enhances the overall speed and accuracy of data interpretation.

To achieve this level of customization, users select the appropriate visual option from the dropdown menu found within the ‘Between’ dialog box. Excel provides several sophisticated built-in options designed for quick, visually effective results. Moreover, the Custom Format option grants highly granular control over every measurable attribute of the cell’s appearance, including specific number formatting, detailed font effects (such as strikethrough, bolding, or italics), defined borders, and precise fill colors. This high level of control ensures that the visual output perfectly and accurately aligns with the specific analytical purpose of the conditional rule being implemented.

Advanced Techniques: Using Formulas for Complex ‘Between’ Logic

While the standard ‘Highlight Cells Rules’ functionality competently handles the vast majority of inclusive ‘Between’ scenarios with high efficiency, there are crucial instances where more complex or analytically precise criteria are absolutely necessary. For example, if your objective requires applying the rule exclusively (meaning values must be strictly greater than 10 AND strictly less than 20, thereby excluding the boundaries 10 and 20), or if the boundary values themselves are required to be dynamic—derived from referenced cells rather than fixed numbers—a specialized custom formula rule is required. This advanced technique effectively leverages the full power of logical functions within the conditional formatting framework.

To implement a custom rule, the user must navigate back to the Conditional Formatting menu, select New Rule, and then choose the advanced option titled Use a formula to determine which cells to format. The foundational requirement for this formula is that it must yield a final outcome of either TRUE or FALSE for each individual cell evaluation. For an exclusive ‘Between’ scenario where the value in cell B2 must be strictly greater than 10 AND strictly less than 20, the formula must effectively utilize the powerful AND logical operator to combine the two necessary conditions.

The precise structural syntax for such a custom rule, assuming we are consistently applying the rule across the range B2:B14, would utilize the relative reference B2, focusing the formula on the top-left cell of the selected range, which the system automatically adjusts for every other cell:

=AND(B2>10, B2<20)

The utilization of a custom formula provides unparalleled flexibility, permitting users to incorporate variable inputs, establish boundary definitions by referencing external cells, or efficiently combine the ‘Between’ logic with other disparate conditions, such as checking if the corresponding team name begins with a specific character. This sophisticated technique is indispensable for advanced analysts requiring highly specialized visual reports that inherently transcend the functional limitations of the basic, built-in conditional rules.

Best Practices for Effective Data Visualization

The effective and strategic use of conditional formatting extends significantly beyond mere technical application; it mandates careful, strategic consideration of visual communication principles. When defining ‘Between’ ranges, it is critically important to ensure that the chosen colors and styles possess high visibility yet do not conflict or clash visually with other existing formatting rules present within the worksheet. For datasets that inherently utilize multiple competing conditional formatting rules simultaneously, carefully prioritizing the rules within the Rules Manager is vital, as the application processes the list sequentially and applies only the first rule that successfully evaluates to TRUE for a given cell.

A key best practice involves maintaining strict consistency in color palettes across various reports and visualizations. If, for instance, the color green is established to signify “Optimal” performance in one table, it must consistently signify “Optimal” performance across all related tables and dashboards to prevent analytical confusion. Similarly, when establishing boundaries, always thoroughly document the underlying rationale behind the chosen minimum and maximum values. Are 10 and 20 derived from historical averages, industry-standard benchmarks, or specific departmental targets? Clear, concise documentation dramatically enhances the longevity and utility of the visual data representation for all stakeholders.

Finally, always rigorously check your formatting application against all potential edge cases. This includes testing values that are exactly equal to the lower bound (e.g., 10) and the upper bound (e.g., 20), as well as values positioned just marginally outside the range (e.g., 9.99 and 20.01). Performing this crucial verification step confirms whether your specific rule (whether it is the standard ‘Between’ rule or a sophisticated custom formula) is operating as inclusively or exclusively as originally intended, thereby definitively preventing the potential misinterpretation of critical performance metrics and data points.

How to Apply Conditional Formatting if Cell Contains Text in Excel