How to Highlight a Full Row in Excel Automatically

To highlight a full row in Excel once, select its row number and choose Home > Fill Color. To highlight rows automatically when they meet a condition, select your data range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter a rule, and choose a fill color. The manual method colors a row until you change it; conditional formatting updates as the cell values change.

What You’ll Need

An Excel workbook open in Microsoft Excel for Microsoft 365 on Windows. The main steps also work in recent desktop versions of Excel.
For automatic highlighting, a clear data range and a condition to check, such as a status in one column or a number above a threshold.
For the example below, a table with headers in row 1, data in A2:H100, and a status in column D. Adjust the range and column references to match your sheet.

Step-by-Step

Choose between a one-time color and an automatic rule. Use a one-time fill when you want to mark a row yourself, such as a row you are reviewing. Use conditional formatting when the row should be colored because a value meets a condition, and the color should change if that value changes. You can use either method on a worksheet; conditional formatting is usually more practical for lists that are updated regularly.
For a one-time highlight, select the row. Click the row number at the far left of the worksheet to select the entire worksheet row. To select a row while working in its cells, click a cell in that row and press Shift+Space on Windows. On Mac, Shift+Space also selects the row. If you only want to color the part of the row containing your table, drag across those cells instead of selecting the row number.
Apply a fill color. On the Home tab, open the arrow beside Fill Color (the paint-bucket icon) and choose a color. The selected cells should take that color. If you chose the row number, the fill applies across the entire worksheet row, including columns outside your data. To remove a manual fill later, select the same cells and choose Home > Fill Color > No Fill.
For automatic highlighting, identify the range you want formatted. Decide which columns the color should cover and which rows contain data. For example, to highlight a table from A2 through H100, select A2:H100. You can type A2:H100 in the Name Box to the left of the formula bar and press Enter. Starting with row 2 keeps the header out of the rule. If your list will grow, consider applying the rule to a larger sensible range or converting the data to an Excel table.
Create a formula-based conditional formatting rule. With the target range selected, choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format. The formula must return TRUE for a row that should be highlighted. The dollar sign before the column letter keeps the check in the status column; leaving the row number relative lets Excel check each row in turn.
Enter the condition and choose the appearance. Assuming headers are in row 1, records are in A2:H100, and each record’s status is in column D, use this formula to highlight rows whose status is Complete:
=$D2="Complete"
With this layout, a row such as A7:H7 is highlighted when D7 contains Complete; a row with another status is not. Click Format, open the Fill tab, choose a background color, and click OK. Click OK again to create the rule. Text comparisons in Excel are generally not case-sensitive, so Complete and COMPLETE both match.
Check the Applies to range. Conditional formatting colors the cells covered by the rule, not automatically every cell in the worksheet row. To review or change the range, go to Home > Conditional Formatting > Manage Rules. Select the rule and check Applies to. For the example, it should cover =$A$2:$H$100. If it only covers column D, only the status cells will change color. Expand it to the columns you want highlighted, then click Apply or OK.
Test the rule with a real value. Change a status in column D to Complete and confirm that the corresponding cells across A:H change color. Change it to a different status and check that the highlight disappears. If you used a manual fill, test by selecting another row and applying a different color; it will not change automatically when a cell value changes.

Common Mistakes

Locking the row number in the formula. A formula such as =$D$2="Complete" checks D2 for every row in the selected range. That can make all rows appear highlighted or none of them. For a rule beginning on row 2, use =$D2="Complete": lock the column, not the row.
Starting the selection and formula on different rows. If the Applies to range begins at row 3, the formula should normally refer to row 3, such as =$D3="Complete". Excel evaluates the formula relative to the first cell in the range. A mismatch can shift the condition by one row.
Applying the rule only to the condition column. A formula can check D while the formatting applies to A:H. Confirm both the formula reference and the Applies to range; they serve different purposes.
Including the header unintentionally. If the formula starts with row 2 but the Applies to range includes row 1, the header can be evaluated against the wrong record. Begin both the data range and formula reference on the first data row, or create a separate rule for the header.
Expecting a manual fill to react to changes. A fill color applied from the ribbon is just formatting. Use conditional formatting for color that should respond to edits, rather than recoloring rows by hand.

Tips

Use a restrained color. A light fill keeps text readable and makes the row noticeable without obscuring other formatting. Check the result with your usual font color and any borders already in the table.
Keep the rule limited to the useful columns. If your worksheet has many columns, set Applies to to the table width rather than coloring the entire sheet. This makes the highlight easier to scan and avoids coloring unrelated notes.
Make the rule easier to maintain. If new records are added often, format the data as a table with Home > Format as Table. Table ranges commonly expand when you add rows, but check the conditional formatting rule after adding data to make sure its Applies to range includes the new records.
Use distinct rules for distinct conditions. You can create additional rules for values such as Pending or Overdue and give each a different fill. Keep the rules understandable, and inspect their order in Conditional Formatting > Manage Rules if more than one rule could apply to the same row.
On Mac or Excel for the web, look for the equivalent controls. In Excel for Mac, the Home tab includes Conditional Formatting and New Rule, though dialog layouts can differ from Windows. Excel for the web also provides conditional formatting controls on the Home tab; the available options and dialog presentation may vary. The core idea remains the same: select the cells to color, write a condition relative to the first data row, and set the range to cover the row’s columns.

Troubleshooting

Only one cell changes color: The rule may apply only to the cell you initially selected. Open Home > Conditional Formatting > Manage Rules, select the rule, and expand Applies to so it covers all intended columns and data rows.

The wrong row is highlighted: Compare the first row of Applies to with the row number in the formula. If Applies to starts at A2, a condition for column D should usually refer to D2. Remove an unnecessary dollar sign before the row number so the reference can move down for each record.

No rows change color: Check that the value in the condition column actually matches the text in the formula. Extra spaces, a different spelling, or checking the wrong column can prevent a match. Temporarily type the expected word directly into a test status cell. Also confirm the selected fill color is visible and that the rule is enabled.

Every row changes color: Check whether the formula uses a fixed reference such as =$D$2="Complete". If D2 contains Complete, the same TRUE result may be applied throughout the range. Use a relative row reference, such as =$D2="Complete", so Excel evaluates each record separately.

The color does not disappear after editing a value: Confirm that the highlighted cells are controlled by conditional formatting, not a manual fill. If a manual fill was applied first, remove it with Home > Fill Color > No Fill and then retest the rule. Also check Manage Rules for another rule that might still apply a fill.

New rows are not highlighted: Open Manage Rules and check whether the Applies to range stops above the new rows. Extend that range, or use an Excel table and verify that the rule follows the table as it grows. Avoid applying a rule to an unnecessarily huge range if the workbook becomes slow.

Frequently Asked Questions

How do I highlight a whole row based on one cell?
Select the columns and rows you want colored, then create a formula-based conditional formatting rule. For a status in D and a range beginning in row 2, use a relative row reference such as =$D2="Complete". Set Applies to to the full row width of your data.

Can I highlight a row without using a formula?
Yes. Click the row number, then choose a color from Home > Fill Color. That creates a permanent manual fill until you change or clear it. It does not respond automatically to values.

How can I highlight an entire worksheet row?
Click the row number at the left edge of the sheet, or select a cell in the row and press Shift+Space, then choose a fill color. Conditional formatting usually works better on a defined table range; set Applies to to the columns you want rather than the entire worksheet unless you truly need every column colored.

Can I highlight rows based on a number instead of text?
Yes. Create a formula-based rule that tests the relevant numeric column, and keep the row reference relative so each record is checked separately. Make sure the rule applies to the full set of columns you want to color. The formula must match your data layout and the condition you intend to use.

Will the highlight remain if I sort the table?
Conditional formatting normally moves with the records when you sort a properly selected data range, because the condition is evaluated for each row. A manual fill is formatting rather than a value-based rule, so check the results after sorting. Include the full table when sorting to keep each record’s cells together.

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.