3 Ways to Gray Out Unused Cells in Google Sheets

Looking for an easy way to gray out unused cells in your spreadsheet? Well, look no more.

Graying out unused cells is an effective technique to direct your attention to important data and de-emphasize irrelevant data. Luckily, Google Sheets offers some easy ways to blur out empty cells and improve clarity in a few clicks only.

We have explained everything you need to know about grayed out cells in Google Sheets below. So without further ado, let’s get into it right away.

What is a Grayed Out Cell in Google Sheets?

Graying out a cell in Google Sheets means that you want to make it visually distinct from other cells that contain data in your spreadsheet.

This feature allows you to highlight cells with significant information and indicate that the cells grayed out are not important. This way, you can focus on the key information in your sheet and work with data in a more understandable and appealing way.

📝 Note: Graying out cells does not affect the data or formula in a cell in any way. It only changes the visual appearance of the cells and subdues their presence on the sheet.

Gray Out Unused Cells with Conditional Formatting

Graying out empty cells in Google Sheets is an effortless task, and the easiest way to do it is by using conditional formatting.

Want to know how? Take a look below.

We have the following sample data that contains information in columns A and B of the spreadsheet.

Sample data for greying out unused cells

All other cells in the worksheet are empty and irrelevant. Suppose you want to gray them out so we can focus on the important portion.

Here’s how to do this,

Select the top left corner of the sheet to select all cells
  1. Click the top left corner of the screen that looks like a rectangle – this will select all the cells in the worksheet.
Go to Format > Conditional Formatting
  1. Go to the Format tab in the menu bar.
  2. Select Conditional Formatting from the dropdown menu.
The Conditional formatting dialog box appears

The Formatting window pane appears on the right side of the worksheet.

Select Is empty option from the dropdown
  1. Under Format Rules, click the box and select Is empty from the options.
Select a color from the conditional formatting palette
  1. Select your desired gray color from the Fill Color options under Formatting Style.
All blank cells turn grey

All unused cells have been grayed out. Pretty simple!

But do you know there’s another way to do the gray out cells using conditional formatting?

I bet you don’t – but worry not, I’ll explain everything below.

  1. On the Format Rules dropdown menu, scroll down and select Custom function is from the options. A small Value or formula box will appear beneath it.
Apply the ISBLANK function in value or formula box
=ISBLANK(A1) 
  1. Enter the above formula.

The function will check all blank cells starting from cell A1. Any cell that is empty will be applied the selected color, while cells containing data will remain intact.

  1. Go to Fill Color options under Formatting Style and select a shade of gray.
All blank cells turn grey

All unused cells in your worksheet will be grayed out in no time. Try it now!

Gray Out Unused Cells with an Apps Script

You can also gray out unused cells in Google Sheets using Apps Script. It is a scripting application that lets you automate tasks by writing a customized code.

You can then create add-ons, function and applications, etc. For graying out cells in Google Sheets, all you need to do is paste a code, run it, and execute the script. All empty cells will be grayed out.

Curious to know more? Read on.

Go to Extensions > Apps script
  1. Go to the Extensions tab to open the Apps Script editor.
  2. Click Apps Script.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Color Cells')
    .addItem('Blank cells to Gray', 'colorBlankCells')
    .addToUi();
}

function colorBlankCells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var data = range.getValues();
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == "") {
        range.getCell(i + 1, j + 1).setBackground("lightgray"); }
    }
  }
}
  1. Paste this code to the editor.
  2. Save the file and then Run it.

Once you’ve executed the file, go back to your worksheet and refresh it.

  1. Click the top left corner of your sheet to select all cells on your screen.
Select Color Cells > Blank cells to Gray

In the menu bar, a new tab named Color Cells will appear after you refresh the spreadsheet.

  1. Click the Color Cells tab.
  2. Select the Blank cells to Gray option.
All unused cells in the sheet turn gray

The script will take a while to check the entire spreadsheet for blank cells. Once done, it will color all empty cells gray while keeping cells containing data uncolored.

Isn’t it fun? Try this method yourself.

Gray Out Unused Cells Manually with Fill Color

Graying out empty cells using Fill color has to be the easiest method of all. Just select all unused cells and paint them gray in a swoosh.

To gray out unused cells,

  1. Select all cells by clicking the left corner of your screen.
  2. Press and hold Ctrl.
  3. Select each used cell to deselect the cell.
Deselect the cells containing data

This will deselect all the cells that contain data in our sheet while empty cells remain selected.

Select color from the conditional formatting palette
  1. Go to the Fill Color option in the menu bar.
  2. Select the shade of gray you want to apply.
All empty cells turn gray

And voila! It’s done. All remaining cells have been colored gray. How cool is that?

Conclusion

Graying out unused cells is not only a great way of de-emphasizing irrelevant cells, but the process is fun too. By graying out unused cells, you direct attention toward the cells that contain data and are important.

Moreover, this formatting also improves the clarity and visual appeal of the worksheet which makes working with large data sets slightly bearable. You can use any of the above-mentioned methods for graying out blank cells – our favorite method is the one using Apps Script.

Try these techniques today to gray out unused cells and make your spreadsheet stand out!

About the Author

Bareera Ahmed

Bareera Ahmed

Meet Bareera, a cybersecurity analyst and Google Sheets and Excel virtuoso. She has a knack for maximizing the potential of Excel, from creating intricate spreadsheets to automating workflows and everything in between. When not immersed in the world of cybersecurity or conquering Excel challenges, she finds solace in the art of pasta.

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!