How to Highlight Empty Cells in Excel (Easiest Way in 2024)
In this article, we will show you how to highlight empty cells in Excel. Simply follow the steps below.
Excel Highlight Empty Cells
Follow the steps below on how to highlight empty cells in Excel.
1. Select Range of Cells you want to Check
Choose the range of cells you want to check for empty cells. Click and drag your mouse over the desired cells, or click the first cell, hold down the “Shift” key, and click the last cell in the range. In our example below, we will select cells B2:B6.
2. Open Conditional Formatting Menu
Navigate to the “Home” tab on the ribbon at the top of Excel. In the “Styles” group, find and click on “Conditional Formatting.” This will open a dropdown menu with various formatting options.
3. Create a New Formatting Rule
In the Conditional Formatting dropdown menu, click on “New Rule.” This will open the “New Formatting Rule” dialog box where you can define a new formatting condition for your selected cells.
4. Choose Formula to Determine Format
In the “New Formatting Rule” dialog box, choose the option “Use a formula to determine which cells to format.” This allows you to use a custom formula to identify which cells should be highlighted based on your condition.
5. Input Formula for Empty Cells
In the formula box, type `=ISBLANK(B2)`. Replace “B2” with the reference to the first cell in your selected range. This formula checks if each cell in the range is empty. Make sure the cell reference is relative (without $ symbols) so it applies correctly to the entire range.
6. Define Formatting Style for Empty Cells
Click the “Format” button to open the “Format Cells” dialog box. Choose the formatting style you want to apply to the empty cells, such as a specific fill color, border, or font style. Click “OK” to confirm your formatting choices.
7. Apply the Formatting Rule
After setting your desired format, click “OK” in the “Format Cells” dialog box. Then, click “OK” again in the “New Formatting Rule” dialog box to apply the rule to your selected range of cells.
8. Verify Highlighted Empty Cells
Look over your spreadsheet to see the empty cells highlighted with the formatting you selected. If any adjustments are needed, you can modify the range or the formatting rule as necessary.
In addition, you can apply this “New Rule” to the rest of the range of cells to highlight empty cells.
We hope you now have a better understanding of how to highlight empty cells in Excel. If you enjoy this article, you might also like our article on ways to highlight changes in Excel or our article on how to highlight unique values in Excel.
