Excel - Find & replace blank cells in Excel

Ask a question
Microsoft Office provides Microsoft Excel, one of the most widely used software programs. The Excel application allows users to generate spreadsheets and charts for tracking, analysing and sorting data. To maximize the efficiency, Excel provides excellent formulas when working with it. For example, the IF formula returns one value if the condition mentioned evaluates to True and another value of the condition mentioned evaluates to False. The IsBlank formula is used to check if a specific cell is empty or not. If the cell is empty, a True value is returned by the formula. If the data is added to an empty cell later, the formula will update automatically and return False value. The Replace formula is used to replace a sequence of characters in a string with another set of characters.


I am looking for a formula/method of finding a blank cell in a column and replacing it with the text in the previous cell. Example: cell A1:A20 all have data, cell A21 is blank, cell A22:A50 have data. I want to identify that cell A21 is blank and replace its contents with the content located in cell A20, then move on to find the next blank cell in the column and replace it with its previous cell's contents. I want to be able to repeat this for thousands of rows, but only replace any blank cell in a specific column with the contents of its previous cell. Can this be done?


It is possible with macro or if you can have this formula in the B column and once done, you can copy and paste special values to remove the formula.

In B2 you can have something like this:

=if (isblank(a2), a1, a2)

Fill this formula right down to the last row.


Thanks to rizvisa1 for this tip on the forum.
Jean-François Pillou

Jean-François Pillou - Founder of CCM
Better known as Jeff, Jean-François Pillou is the founder of CommentCaMarche.net. He is also CEO of CCM Benchmark and digital director at the Figaro Group.

Learn more about the CCM team