How to Grey Out Cells in Excel

- Written by Puneet

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.

grey-out-cells

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

  1. First, select cell A1 and use the keyboard shortcut Ctrl + A to select all the cells.
    select-a-cell
  2. Now, go to the home tab, click on the conditional formatting, and then select the new rule option.
    conditional-formatting-new-rule
  3. After that, from the new formatting rule dialog box, click on “Use a formula to determine which cell to format”.
    new-formatting-dialog-box
  4. From here, in the “Format values where this formula is true” enter the formula =IF(A1="",TRUE,FALSE).
    enter-the-formula
  5. Next, click on the “Format” button, go to the fill tab, and select the grey color.
    click-format-button
  6. Click OK to save the option.
    ok-to-save

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.

all-cells-grey-out

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.

grey-out-unused-areas

Select all the columns after column N, right-click from the mouse and click on the hide option.

select-hide-option

Now select all the rows after row 13, right-click, and select the hide option from there.

select-the-rows-and-hide

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.

rows-hidden

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.

page-break-view-to-grey-out-cells

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

decrease-the-zoom-level

Change the zoom level to 100%, and you will have a worksheet view just like the following.

change-zoom-level-to-100%