How to add only Format to blank rows


How to add only Format to blank rows in excel without adding data, so that when we add the data into the rows , they should be updated with Format 



Press f5 -> goto special option-> select blanks And do formatting
beepetark Oct 10, '19 at 4:22 am
Add to Discussion



You want to use Conditional Formatting for this.

Select the desired cells or the entire column and go to: Home tab > Conditional Formatting > Highlight Cells Rules > More Rules... > change Format only cells with: to "No Blanks" > click Format and choose the desired format and then hit OK in both pop-up windows and you should be good to go.

Test it out by inputting data into one fo the cells that you just applied conditional formatting to.


The task you describe doesn't come up in Excel, except when you use VBA. The more common task, and the one I presume you mean in your question, is to format a blank row identical to another row that already exists, usually on the same worksheet but it might also be another. That is a copying task for which Excel provides many solutions.

The easiest one, perhaps, is to simply copy a row, paste it in another and immediately press Delete. The Delete action will leave all cell formats in place. Unfortunately,it will delete any formulas that you might wish to preserve. More about that in a moment.

The alternative to copy/paste formats is in the Format Painter. Select the area you wish to copy the formats from and click on the paint brush in the Ribbon's Home tab. That copies the formats to the clipboard. Now select the area you want to paste to and click the brush again. The formats are transferred but no formulas. For selecting the target area the rules for Copy/Paste applies, meaning the selected target area must be of identical size to the source or a multiple thereof.

As you see, this is all about Copy/Paste. So, you can copy the source area (Ctl+C or the Copy button in the Ribbon) and then choose one of the many Paste options available from the Paste menu you get to see when you press the Paste button in the Ribbon. I draw your particular attention to the sequence Paste > Paste special > [Paste] Formulas and Number formats.

Paste > Other Paste Options > [First button] has the same effect as the Format Painter.


Press f5 -> goto special option-> select blanks

And do formatting


Answer the Question

You must create an account to use the forum. Create an Account or Login