# Data clean-up techniques in Excel: Filling blank cells

Sometimes you will end up with a worksheet that will look something like the one shown in the following Figure.

Filling Blank Cells in Excel. This data contains gaps in the Sales Representative column.

Read More: Data clean-up techniques in Excel: Randomizing the rows

Filling blank cells with data is common. In our example, an entry in column A applies to several rows of data. This type of data arrangement is easy to understand. For example, you can find easily Jane’s Jan, Feb and March months’ sales. But if you sort this type of list, the blank cells will mess things up, and you will not be able to tell who sold what. See this figure sorted according to Amount.

When data is sorted, you can’t find out the sales representative as there were gaps in this column.

We shall explain here x ways to solve this problem.

### Solution 1:

If your data is small, you can enter the missing cell values manually or by using this commands: Home ➪ Editing ➪ Fill ➪ Down (or the Ctrl+D shortcut).

For example, select cell A6 and choose Home ➪ Editing ➪ Fill ➪ Down or press CTRL+ D, A6 will show “Jane”. Then select cell A7 and press CTRL+ D or choose Home ➪ Editing ➪ Fill ➪ Down. Cell A7 will be filled with “Jane”. Fill the rest blank cells in this way.

Filling Blank cells manually. Press CTRL+ D or choose Home ➪ Editing ➪ Fill ➪ Down

### Solution 2

If your data is large, it is not possible to fill blanks manually. Here’s a better way:

Step 1: Select the range that has the blank cells (A5:A16, in our example).

Step 2: Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special. The “Go to Special” dialog box will appear.

Step 3: Select the “Blanks” option and click OK. This action selects the blank cells in the cell range A5:A16.

Go to Special Dialog Box. Choose “Blanks” and then press OK.

Step 4: In the formula bar, type an equal sign (=) and then the cell address of the first cell with data in the column (=A5, in our example), and press Ctrl+Enter. You will find the following result.

The Blank cells are filled with data as you expected.

Step 5: If you select the recent filled blank cells, they will show “Formula” in the formula bar rather than data. But we want data in those cells. See the following figure.

If you click the recently filled cells, they will show formula rather than data. We need data in those cells.

Step 6: Reselect the original range and press CTRL+ C to copy the selection.

Step 7: Choose Home ➪ Clipboard ➪ Paste ➪ Paste Values to convert the formulas to values.

After you complete these steps, the gaps are filled in with the correct information.

Happy Excelling 🙂