Excel: Highlight the Bottom 10 Values in Red

To highlight the 10 lowest values in red in Excel, select the cells containing your numbers, then choose Home > Conditional Formatting > Top/Bottom Rules > Bottom 10 Items. Set the count to 10 and choose a red format, or use Custom Format to pick your preferred red fill. The rule updates automatically when values in the selected range change.

What You’ll Need

A Microsoft Excel workbook in Excel for Microsoft 365 on Windows. The same conditional-formatting feature is available in current desktop versions of Excel for Mac; the ribbon layout may look slightly different.
A range of cells containing the values you want to compare. For example, if the numbers are in B2:B31, select B2:B31 and leave the heading in B1 out of the selection.
A decision about how you want the lowest values to look, such as a red fill, red font, or a combination. You can choose this while creating the rule.

Step-by-Step

Identify the range to compare. Find the column or block of cells that contains the numbers. If the worksheet has a heading in the first row, do not include it. For instance, if B1 says “Score” and the scores are in B2:B31, the range to format is B2:B31. The rule ranks the values in the selected range together, so select only the data you want compared.
Select the cells. Click the first numeric cell, then drag to the last cell in the range. You can also click the Name Box to the left of the formula bar, type a range such as B2:B31, and press Enter. For a large, continuous list, this can be quicker and helps avoid accidentally selecting the heading or an adjacent column.
Open the Bottom 10 Items rule. On the Home tab, choose Conditional Formatting > Top/Bottom Rules > Bottom 10 Items. Excel opens a small dialog for the rule. This is a ranking rule: it identifies the lowest entries in your selection rather than changing the order of the worksheet.
Confirm or change the number of items. The dialog normally starts with 10. Leave that number in place to highlight the bottom 10, or replace it if you want a different count. The setting is a number of cells, not a percentage. For example, entering 5 asks Excel to highlight the five lowest items in the selected range.
Choose a red appearance. Open the formatting list in the dialog and select a red option, such as a light red fill with dark red text. If you want a different shade or only red text, choose Custom Format, set the options on the Format Cells tabs, and confirm. A fill is often easier to spot than font color alone, particularly in a long list.
Apply the rule. Click OK. Excel immediately formats the lowest values it finds in the selected range. If B2:B31 contains 30 distinct numbers, the 10 lowest cells should show the red formatting. The original values remain unchanged; conditional formatting only changes how qualifying cells look.
Check the result against the data. Look at the highlighted cells and verify that they are the smallest numbers in the selected range. Negative numbers are lower than positive numbers, and zero is lower than any positive number. If the result seems surprising, check that you selected the intended cells and that the values are actually stored as numbers.
Keep the rule pointed at the right range. If you add more values later, check whether the new cells are included in the rule. To review it, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rule manager, inspect Applies to. Extend the range if necessary, then confirm the change. In a formatted Excel table, adding rows may extend the formatting, but verify the rule if new entries are not highlighted.

For a quick example, suppose B2:B31 contains 30 test scores, with a low of 42 and a high of 98. Applying Bottom 10 Items to B2:B31 highlights the ten lowest score cells, including 42 and the next nine lowest entries. It does not put those scores together or move any rows. If two or more cells share the value at the cutoff, Excel can highlight every tied cell, so you may see more than ten red cells.

Common Mistakes

Selecting the heading with the data: A text heading is usually ignored when Excel ranks numeric values, but including it makes the selected range less clear and can cause confusion when you later edit the rule. Select the values only, or check the Applies to range after creating the rule.

Confusing “Bottom 10 Items” with “Bottom 10%.” These are separate conditional-formatting choices. Bottom 10 Items means ten cells, while Bottom 10% means the lowest tenth of the eligible cells. If you select 200 numbers, a 10% rule can highlight about 20 entries, not 10.

Expecting exactly ten red cells when values are tied. The rule compares values, and ties at the cutoff can make more than ten cells qualify. For example, if the tenth-lowest position is shared by three cells with the same value, Excel may format all three tied cells. That is useful when equal results should receive equal treatment, but it can surprise anyone counting the red cells.

Including the wrong column or too much of the sheet. If you select a range that includes prices, dates, IDs, or unrelated figures, Excel ranks those numeric cells together. A small ID may become one of the lowest values even though it is not the measure you intended to assess. Compare one relevant measure at a time.

Assuming a red appearance changes the underlying value. Conditional formatting affects display only. It does not replace a number with a red version, remove it, or sort it. If another person opens the workbook with different display settings, the value is still the same, and the rule remains attached to the cells.

Tips

Use a custom format that is easy to read with the worksheet’s existing colors. A pale red fill with dark text tends to preserve legibility better than a saturated fill with low-contrast text. If your workbook already uses red for warnings or errors, consider a different shade or a red font so readers do not mistake low values for data-entry problems.

Choose the comparison range based on the question you are asking. If each row is a different product and column B contains sales, select only the sales cells in column B. If you select both sales and quantities, Excel will rank all selected numeric cells as one group, which does not give a meaningful “bottom 10 sales” result. If you want a separate bottom 10 for each category, apply a separate rule to each category’s range.

For a list that grows regularly, consider converting the data into an Excel table. Select the data and choose Insert > Table, making sure the header setting matches your layout. Tables are designed to expand as you add records, although conditional-formatting rules can still need checking. After adding rows, use Manage Rules to confirm that the Applies to range includes them.

You can change or remove the rule without clearing the values. Select a cell in the affected range and open Home > Conditional Formatting > Manage Rules. Select the relevant rule to edit it, or use the delete option in the manager to remove it. If several rules appear, check the Applies to column so you edit the rule for the intended range.

When sharing the workbook, explain what the red formatting means if it is not obvious from a nearby heading or note. Color alone may not be accessible to every reader, and a printed copy may reproduce colors poorly. A short label such as “Red indicates the 10 lowest scores” can make the result clearer without changing the rule.

Troubleshooting

No cells turn red: Check that the range includes numeric values and that the rule is applied to those cells. Select a formatted cell and open Home > Conditional Formatting > Manage Rules to inspect Applies to. If the range is correct, look for other conditional-formatting rules that may affect the appearance, and confirm that the chosen format is visibly red.

Values that look like numbers are ignored: Some entries may be stored as text, often after being imported or pasted from another system. A number stored as text may not rank like a numeric value. Check a few cells by selecting them and examining how Excel treats their contents; a warning indicator may offer a conversion option. Convert text-formatted numbers to real numbers, then check whether the rule updates.

The wrong cells are highlighted: Open Manage Rules and inspect the Applies to range. The rule may include an adjacent column, omit new rows, or start on the wrong row. Correct the range and confirm. Also check whether the values are genuinely comparable—for example, a date is stored as a number in Excel, so a date in the selected range can be ranked alongside ordinary numbers.

More than ten cells are red: Check for tied values at the cutoff first. If ties explain the extra cells, this is expected behavior. If you need exactly ten cells regardless of ties, the Bottom 10 Items rule may not match that requirement; you would need a different method that defines how to break ties, such as using a secondary ranking criterion.

The red formatting disappears or looks different: Confirm that the cells still fall within the rule’s Applies to range and that another rule is not overriding the format. If the workbook is opened in Excel for the web or another spreadsheet program, some dialog controls may differ, so review the rule in desktop Excel for Microsoft 365 if you need to adjust its custom format precisely.

Frequently Asked Questions

Does “Bottom 10 Items” mean the ten smallest unique numbers?
No. It ranks cells, not distinct values. If the same low number appears in several cells, each cell is an item and can be highlighted. A tie at the cutoff can also result in more than ten highlighted cells.

Can I highlight the bottom 10 values with red text but no fill?
Yes. Open the Bottom 10 Items dialog, choose Custom Format, and set the font color to red while leaving the fill unchanged. The exact tabs in the Format Cells dialog may differ slightly by Excel version, but the font and fill settings are separate.

Will the highlight update when I edit a value?
Yes, provided the edited cell is inside the rule’s Applies to range. Excel recalculates the ranking and moves the formatting to the cells that now meet the bottom-10 condition. Values added outside that range are not included until you extend the rule.

Can I use this rule for negative numbers?
Yes. Excel ranks numeric values by size, so a more negative number is lower than a less negative number. For example, -20 is lower than -5, and both are lower than 0.

How do I highlight the bottom 10 in each group separately?
Create a separate Bottom 10 Items rule for each group’s range. If you apply one rule to a combined range, Excel compares the cells in that range together rather than automatically restarting the ranking for each group.

Can I use Bottom 10 Items on a row instead of a column?
Yes. Select the numeric cells across the row and apply the same rule. Excel ranks the selected cells together regardless of whether the range runs vertically or horizontally.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.