Quickly fill Blank cells with the Value in the Cell Above

There are situations when you ask someone to punch data for you and they come up with one lazy idea to leave the cell blank if the same value is repeating in subsequent cells in the same column. Download the tutorial workbook or look at the picture below to understand the situation better.

This is definitely a daunting task as you have to manually drag fill the contents in the cells to the correct point in the range without distorting the data. And surely you need a steady hand with steady nerves to get through it.

Download Practice Excel Workbook

To best understand the techniques in this tutorial is to actually do the steps. To help you out, download this workbook and you can carry out the steps on the data to learn at best

But with few clicks this pain in the neck, wrist and fingers job can be done in the blink of an eye. Following are the steps to get it done.

Step 1: Select the whole range by hitting Ctrl+A. If the automatic range selection is not working as your requirements then you can manually select with the mouse.

Step 2: Go to Home tab > Editing group > Find and Select drop down > Go to Special.

Step 3: From the dialogue box select Blanks radio button and click OK. Excel will quickly reduce the selection only to blank cells within range. At this stage be very careful and do not make any inadvertent clicks or keystrokes as it will deselect the data and you will have to repeat the step.

Step 4: Carefully hit F2 key on the keyboard. It will put you in edit mode in the very first cell of selection. Type the cell address of the cell just above the active cell. Active cell in this case is the one in which you are editing currently. It will be colorless  with input cursor blinking. In active cells will be colored. In our case it is cell

Step 5: Now hit Ctrl+Enter NOT just Enter.

The moment you hit the combo the whole worksheet will be filled with correct content. Told ya! blink blink 😉

The following animation will walk you through the above five steps.

WTE (What the Excel) I just SAW!

The main concept behind this technique was to get the blanked cell getting filled with the cell values just above. To achieve this we have to have cell reference of the above cell in each cell. Now this appear confusing that how has that happened. Actually when you put the cell reference in one cell and the reference is relevant instead of an absolute reference, excel updates the reference each time it steps to the right, left, up and down. This changes the address automatically and thus we get the accurate result in each cell.