Some spreadsheets contain blank cells to make headers and titles easier to read, but blank cells can be problematic when sorting and filtering data. This article will show you, how to quickly fill blank cells in Excel with values above.
Example below shows sales report, and as you can see, in column A names of cities have been entered once, despite of multiple records for each city:
In such situation, filtering or sorting data in column A would not work properly, because after choosing specific city, filter would find only one record. To correct that, we need to fill empty cells by copying values from cells above.
To do that, select data range which includes empty cells to be filled:
Then, select Go To Special which can be found in Home tab of the Ribbon, in command group Editing:
Instead of step above, you can use key shortcut Ctrl + G or F5 and select Special in opened window:
In next step, choose Blanks and confirm by clicking OK:
At this stage, only blank cells from selected data range are highlighted and ready for further steps.
To fill the blanks with the value from the first filled cell above, you need to enter a simple formula into one of the blank cells. With only the blank cells still highlighted, type the formula “= A2” in cell A3:
Confirm by clicking Enter key – in this way, in A3 you get value from cell above (A2). To copy this formula to other blank cells use combination Ctrl + Enter: