3 ways to quickly deal with blank cells in an Excel sheet

Filling blanks is not tough in any respect in case you apply one among these three suggestions. They’re fast and straightforward!


Picture: PixieMe/Shutterstock

Blanks in a Microsoft Excel spreadsheet aren’t at all times incorrect, however they will throw a monkey wrench into expressions, analyzing and reporting. I do not suggest leaving blanks until you’ve gotten particular motive for doing so. Blanks will be troublesome and downright harmful as a result of some capabilities consider blanks in a different way than values. On this article, I will present you three fast and straightforward methods to take care of clean cells:

  1. utilizing highlighting
  2. getting into a particular worth
  3. copying the worth above

I am utilizing Microsoft 365, however you should use earlier variations. Strategies two and three will not work in Excel On-line as a result of there isn’t any Go To Particular function. On your comfort, you may obtain the .xlsx and .xls demonstration recordsdata.

SEE: 83 Excel suggestions each consumer ought to grasp (TechRepublic)

1. Learn how to spotlight clean cells in Excel

Clean cells are simple to overlook in case you’re working with loads of knowledge, so that you may need to spotlight them. The easiest way to take action is with a conditional format. As you may see in Determine A, no less than one cell in every column is clean. In such a small sheet, you’d shortly see all of them, however in a busy sheet with many rows and columns, blanks are simple to overlook until you do one thing to make them stand out, which is what we’ll do now:

  1. Choose the info vary with blanks; on this case, that is B3:E13. (You could possibly choose a single column.)
  2. On the Dwelling tab, click on the Conditional Formatting dropdown within the Kinds group.
  3. Select Spotlight Cell Guidelines after which select Extra Guidelines from the ensuing dropdown. (In Excel On-line, select New Rule.)
  4. Change the Cell Worth setting within the Format Solely Cells With dropdown to Blanks (Determine A).
  5. Click on the Format button.
  6. Within the ensuing dialog, click on the Fill tab and select yellow for the fill colour and click on OK twice to see the ends in Determine B.

Determine A


  Set a rule to format all blanks within the choice.

Determine B


  The conditional formatting rule fills all of the clean cells.

Whereas this visible clue is useful, you may not need clean cells in any respect. Which means filling them with some sort of worth.

2. Learn how to fill with a particular worth in Excel

Typically you may need to fill blanks with a particular worth. As an illustration, the Accounting format enters a touch while you enter a 0, however that is not precisely the identical factor as filling a clean with a particular worth. When that is the case, use the Go To dialog as follows:

  1. Choose the info set B3:E13.
  2. Press [F5] to show the Go To dialog.
  3. Click on Particular (within the bottom-left nook).
  4. Click on Blanks within the ensuing dialog proven in Determine C and click on OK. Doing so will choose solely the clean cells within the chosen vary.
  5. Do not click on anyplace else at this level. As an alternative, enter a touch ( – ) because the fill worth.
  6. As an alternative of urgent Enter, press Ctrl + Enter to enter the sprint image in all the chosen clean cells, as proven in Determine D.

Determine C


  Select the Blanks choice to pick all blanks.

Determine D


  Enter a touch in all clean cells.

This is not a dynamic resolution. As you replace knowledge and add extra blanks, you may have to run this fast process once more. On this instance, you entered a particular worth, however that will not at all times be what you need. Within the subsequent part, we’ll use similar technique to repeat the worth above the clean cell.

3. Repeat the worth above in Excel

Relying on the place you obtain your knowledge, you may discover that some clean cells ought to comprise the worth within the cell above. When this occurs, you may most likely need to fill in these blanks instantly. When you’ve got just a few, use the fill deal with. When you’ve got a number of, the fill deal with is just too time consuming. We’ll proceed to work with the identical knowledge, even thought it’s kind of contrived inside this context. Particularly, we’ll use the Go To function to fill the lacking values, however this time, we’ll enter a system as a substitute of a particular worth:

  1. Choose B3:E13.
  2. Press [F5] to show the Go To dialog.
  3. Click on Particular.
  4. Click on Blanks within the ensuing dialog and click on OK. 
  5. Do not click on anyplace else at this level. As an alternative, enter a easy expression that references the cell above the clean anchor cell, or lively cell. On this case, that is B5, so enter =B4 (Determine E).
  6. Press Ctrl + Enter to enter the relative reference in every of the clean cells, as proven in Determine F.  

Determine E


  Enter the primary expression.

Determine F


  The relative reference copies the worth from above.

At this level, you’ve gotten a combination of literal values and expressions. When you type, these expressions will not repeat the best knowledge. You may assume that will not ever occur, however I do suggest that you simply change these expressions to literal values, simply in case. It is simple to neglect that you simply’re working with a combination of literals and expressions, and also you may find yourself with misguided knowledge. Happily, this process is fast and straightforward:

  1. Choose B3:E13.
  2. Click on Copy within the Clipboard group (on the Dwelling tab).
  3. Subsequent, click on Paste and select Values within the Paste Values part proven in Determine G. Doing so will change every of the expressions with the returned worth.

Determine G


  Paste solely the values.

These three strategies aren’t interchangeable; the way in which you are utilizing your knowledge will decide which technique you select. 

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox