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.
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.
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.
