To grey out cells based on a condition, select the range (say A2:F100), go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format, enter a formula such as =$F2="Closed", and choose a grey fill and grey font under Format.
To grey out every empty cell, click the Select All corner above row 1 and use =A1="" in the same kind of rule. Pressing Ctrl + A once inside your data selects only the data block, so press it twice or use the corner button.
To show only the area you use, hide everything around it. Click the header of the first empty column, press Ctrl + Shift + →, right-click and choose Hide. Do the same for rows with Ctrl + Shift + ↓. The space beyond your data then shows as plain grey.
For a quick view that changes nothing, use View → Page Break Preview. Cells outside the print area turn grey, so set the print area first with Page Layout → Print Area → Set Print Area.
Greyed-out cells can still be edited. To stop people typing in them, unlock the cells they should use (Ctrl + 1 → Protection, untick Locked), then turn on Review → Protect Sheet.
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.
