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

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team