When you are sharing a report or data with someone, you can grey out all the unused cells. That helps the user to stay focused on only that part of the worksheet where we have data.
In this tutorial, we will look at a few different ways to add grey color to the cells which are not used.
Use Conditional Formatting to Grey Out Unused Cells
- First, select cell A1 and use the keyboard shortcut Ctrl + A to select all the cells.
- Now, go to the home tab, click on the conditional formatting, and then select the new rule option.
- After that, from the new formatting rule dialog box, click on “Use a formula to determine which cell to format”.
- From here, in the “Format values where this formula is true” enter the formula
=IF(A1="",TRUE,FALSE).
- Next, click on the “Format” button, go to the fill tab, and select the grey color.
- Click OK to save the option.
It greys all the cells in your worksheet and the moment you enter a value in a cell, it will have the white cell color automatically.
Hide Rows and Columns to Grey Out Unused Areas
Apart from the above method, you simply hide all sections of the worksheet where you don’t have any data. In the following example, you have data from cells A1 to N13.
Select all the columns after column N, right-click from the mouse and click on the hide option.
Now select all the rows after row 13, right-click, and select the hide option from there.
The moment you do this, all the rows below row 13 will be hidden and all the areas greyed out where you don’t have the data.
Use Page Break View to Grey Out Cells
If you don’t want to change your Excel environment, you can use the page break view to show all cells greyed out that are unused in the worksheet.
Go to the View Tab → Workbook Views → Page Break Preview.
After that, you need to change the zoom level to 100 because when switching to the page break view, Excel changes the zoom level to 60%.
Change the zoom level to 100%, and you will have a worksheet view just like the following.
