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

2 years ago 234

Filling blanks isn't hard astatine each if you use 1 of these 3 tips. They're speedy and easy!

Blanks successful a Microsoft Excel spreadsheet aren't ever wrong, but they tin propulsion a monkey wrench into expressions, analyzing and reporting. I don't urge leaving blanks unless you person circumstantial crushed for doing so. Blanks tin beryllium troublesome and downright destructive due to the fact that immoderate functions measure blanks otherwise than values. In this article, I'll amusement you 3 speedy and casual ways to woody with blank cells:

using highlighting
entering a circumstantial value
copying the worth above

I'm utilizing Microsoft 365, but you tin usage earlier versions. Methods 2 and 3 won't enactment successful Excel Online due to the fact that there's nary Go To Special feature. For your convenience, you tin download the .xlsx and .xls objection files.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

1. How to item blank cells successful Excel

Blank cells are casual to miss if you're moving with a batch of data, truthful you mightiness privation to item them. The champion mode to bash truthful is with a conditional format. As you tin spot successful Figure A, astatine slightest 1 compartment successful each file is blank. In specified a tiny sheet, you'd rapidly spot them all, but successful a engaged expanse with galore rows and columns, blanks are casual to miss unless you bash thing to marque them basal out, which is what we'll bash now:

Select the information scope with blanks; successful this case, that's B3:E13. (You could prime a azygous column.)
On the Home tab, click the Conditional Formatting dropdown successful the Styles group.
Choose Highlight Cell Rules and past take More Rules from the resulting dropdown. (In Excel Online, take New Rule.)
Change the Cell Value mounting successful the Format Only Cells With dropdown to Blanks (Figure A).
Click the Format button.
In the resulting dialog, click the Fill tab and take yellowish for the capable colour and click OK doubly to spot the results successful Figure B.

Figure A

Figure B

While this ocular hint is helpful, you mightiness not privation blank cells astatine all. That means filling them with immoderate benignant of value.

2. How to capable with a circumstantial worth successful Excel

Sometimes you'll privation to capable blanks with a circumstantial value. For instance, the Accounting format enters a dash erstwhile you participate a 0, but that's not precisely the aforesaid happening arsenic filling a blank with a circumstantial value. When this is the case, usage the Go To dialog arsenic follows:

Select the information acceptable B3:E13.
Press [F5] to show the Go To dialog.
Click Special (in the bottom-left corner).
Click Blanks successful the resulting dialog shown successful Figure C and click OK. Doing truthful volition prime lone the blank cells successful the selected range.
Don't click anyplace other astatine this point. Instead, participate a dash ( - ) arsenic the capable value.
Instead of pressing Enter, property Ctrl + Enter to participate the dash awesome successful each of the selected blank cells, arsenic shown successful Figure D.

Figure C

Figure D

This isn't a dynamic solution. As you update information and adhd much blanks, you'll request to tally this speedy task again. In this example, you entered a circumstantial value, but that won't ever beryllium what you want. In the adjacent section, we'll usage aforesaid method to repetition the worth supra the blank cell.

3. Repeat the worth supra successful Excel

Depending connected wherever you person your data, you mightiness find that immoderate blank cells should incorporate the worth successful the compartment above. When this happens, you'll astir apt privation to capable successful those blanks close away. If you person lone a few, usage the capable handle. If you person several, the capable grip is excessively clip consuming. We'll proceed to enactment with the aforesaid data, adjacent thought it's a spot contrived wrong this context. Specifically, we'll usage the Go To diagnostic to capable the missing values, but this time, we'll participate a look alternatively of a circumstantial value:

Select B3:E13.
Press [F5] to show the Go To dialog.
Click Special.
Click Blanks successful the resulting dialog and click OK.
Don't click anyplace other astatine this point. Instead, participate a elemental look that references the compartment supra the blank anchor cell, oregon progressive cell. In this case, that's B5, truthful participate =B4 (Figure E).
Press Ctrl + Enter to participate the comparative notation successful each of the blank cells, arsenic shown successful Figure F.

Figure E

Figure F

At this point, you person a substance of literal values and expressions. If you sort, those expressions won't repetition the close data. You mightiness deliberation that won't ever happen, but I bash urge that you alteration those expressions to literal values, conscionable successful case. It's casual to hide that you're moving with a substance of literals and expressions, and you mightiness extremity up with erroneous data. Fortunately, this task is speedy and easy:

Select B3:E13.
Click Copy successful the Clipboard radical (on the Home tab).
Next, click Paste and take Values successful the Paste Values conception shown successful Figure G. Doing truthful volition regenerate each of the expressions with the returned value.

Figure G