Select the Special button in the dialog box to open the Go To Special dialog box.Press the Ctrl + G keys or the F5 key to launch the Go To dialog box.Below are the steps to highlight blank cells by selecting them using Go To Special: After that, all you have to do is highlight the cells using color fill. For the mechanical selection, the Go To Special feature will automatically select the blank cells from the dataset. This method utilizes a mechanical selection of the blank cells so they can be highlighted together. Works for cells that are not blank but appear blank? No. All the data will snap back into place, leaving the blank cells highlighted:
Press the Ctrl + Shift + L keys again to clear the filters. Now it should look something like this:. Select all the blank cells that have been filtered and highlight them with a color of choice from the Fill Color. All the blank cells in the column will group up like so: Uncheck the Select All checkbox and select only the Blanks checkbox. Click on the filter of the column containing the blank cells. Here, the filters have been added to the dataset as shown from the little arrows in the headers:. Select any cell in the dataset and then go to Home tab > Editing section > Sort & Filter button > Filter command or press the Ctrl + Shift + L keys to add columnar filters to the dataset. In the example below, you can see from the formula bar that the cells are not truly blank as the formula is returning an empty text string. 
If more than one column contains blank cells, you can take turns with the columns, filtering and highlighting the blank cells but if you do have more columns, the other methods of highlighting blank cells will be quicker for you.Īs mentioned, this method works for cells that appear blank. The filter will bunch the blank cells from one column together which can then be selected and highlighted. We will use sorting filters on the dataset. This method of filtering to highlight blank cells works best for tabular data. Likewise, if you delete a value, the cell will become blank and highlighted.Ĭons: While Conditional Formatting is dynamic, it is also volatile used on large amounts of data, it will slow the file down. if you add a value to a blank cell, it would not remain highlighted. Pros: Being a dynamic feature, Conditional Formatting will automatically adjust with changes to the data. Here are the highlighted blank cells using Conditional Formatting:
Click on the OK button in the New Formatting Rule. The preview shows the selected color fill. In the new Format Cells window, go to the Fill tab and select the color for highlighting the blank cells. This will lead to a separate window for choosing the format. Click on the Format button to set the color fill for the highlighted cells. The dialog box options will lessen to only formatting options. Click on the drop-down arrow of the field reading Cell Value and select Blanks from the menu. The New Formatting Rule dialog box will open. From the menu, hover over Highlight Cells Rules to further expand the menu and select More Rules… In the Home tab, select the Conditional Formatting button in the Styles section. Select the range containing the blank cells. Find the complete steps below to highlight blank cells using Conditional Formatting: We will go with the latter route and specify our own condition to highlight the blank cells from the selected range. There are various in-built conditions that you can make use of and there is also the alternative to set a custom condition. Works for cells that are not blank but appear blank? Yes.Ĭonditional Formatting is a feature that formats cells according to cell values and as per the provided condition. Highlight Blank Cells Using Conditional Formatting The first two methods of highlighting cells in this guide will also work for cells that appear blank while the other two methods will not. For our case, if the cells are not treated as blank, they won’t be highlighted. The problem with cells that appear blank is that some formulas and features will not treat them as blank cells. Cells that contain a formula to return empty text (denoted by double-double quotes "" in formulas) or cells that contain a space character may appear blank but are not really blank. 
Highlight Blank Cells Using Conditional Formattingĭifference between Blank Cells and Cells that Appear Blankīlank cells are cells that don’t contain any data.Difference between Blank Cells and Cells that Appear Blank.
0 kommentar(er)
