Microsoft Excel: Quickly bring blank cells to your attention

By Kelly L. Williams, CPA, Ph.D.

Q. I have a spreadsheet that my employee is supposed to complete each week. Every week, some of the cells are left blank, reportedly because they were just overlooked. Is there a way to make it blatantly obvious that a cell has been left blank so that this will no longer be an issue?

A. This can be done easily and quickly by using a form of conditional formatting. It is a great way to ensure that you, or others, do not accidentally leave a cell blank or to just bring attention to the fact that some cells have been left blank. I have created a simplified workbook to illustrate. Click here to download the workbook and watch the accompanying video walkthrough at the bottom of this page.

An easy way to bring attention to blank cells is to have them appear as a different color than the other cells on the worksheet. To do this, highlight all of the cells that should be filled in (including cells with data, if you already have data in the file). Then choose Conditional Formatting from the Styles group on the Home tab. Select New Rule to open the New Formatting Rule window.

Select Format only cells that contain under Select a Rule Type:. Then choose Blanks under Format only cells with: using the dropdown arrow. Click Format and go to the Fill tab. Alternatively, you can format your blank cells using options from the other tab choices listed, but for this example, we are going to fill the blank cells in with color. From the Fill tab, choose the color you want blank cells to contain. Click OK. The New Formatting Rule window should look similar to the screenshot below. Click OK again.

techqa-3


All blank cells should now contain the color you chose. Try filling in cells with data and see the color disappear. This is a great way to keep you and others from accidentally leaving cells blank.

The image below contains two screenshots. The one on the left shows a spreadsheet with a few blank cells without the formatting; the one on the right shows the same spreadsheet with the formatting. The spreadsheet with the formatting makes it much more obvious which cells were left blank.

techqa-4


About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

SPONSORED REPORT

Implementing a global statutory reporting maturity model

Assess your organization's capabilities and progress toward an ideal state of global statutory reporting. Sponsored by Workiva.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.