Method 1 – Filling in Blank Cells with Previous Cell Values
Steps:
- Select the entire range where you have blank cells.
- Go to the Home ribbon >> click on Find & Select under the Editing group.
- Click on Go To… from the Find & Select context menu.
- Click on Special…
- Click Blanks under the Select options. Click on OK.
- All the blank cells are selected from the selected range.
- One of the cells is activated automatically. And the other cells are inactive as they are greyed out.
- Enter an equal (=) sign and the upper arrow(^), which will reference the upper cell as a formula, as shown below.
- Press Ctrl+Enter. The formula will be duplicated for every blank cell, which will consume the upper cell’s value.
Note: We have only entered the formula in cell D6, which refers to cell D5 – the “Printer”. However, as we used cell references, it read the value and formula. As a result, in the “Customer Name” column, under the value “Henry”, Excel has put the value “Henry” in the blank cell C6. It has put the formula using this command, and when you click on cell C6, you will see that it refers to cell C5 at the formula bar. If you click on any blank cell, you will see it has consumed its upper cell value.
Method 2 – Using One Unique Value and ‘Go to Special’
Steps:
- Select the entire range of the ‘Customer Name’ column with blank cells.
- Go to the Home ribbon >> click on Find & Select under Editing.
- Click on Go To… from the Find & Select context menu.
- You can also press Ctrl+G or F5 key to bring in the Go To window.
- Click on Special… from the Go To window. It will pop up in the Go To Special window.
- Click Blanks under Select. Click OK.
- All the blank cells are selected from the selected range.
- One of the cells is activated automatically. And the other cells are inactive as they are greyed out.
- Enter the value that you want in the blank cells.
- Press Ctrl+Enter. The value will be duplicated to every blank cell. The result will look like this.
Read More: How to Fill Empty Cells with Default Value in Excel
Method 3 – Applying ‘Go to Special’ to Fill Blank Cells with Different Individual Values
Steps:
- Follow the first 10 steps from the 2nd example.
- Part from the second example, instead of Ctrl+Enter, press Enter. This will fill only the active cell with the input value, and the active cell will shift to the next blank cell.
- Enter another individual value in this cell. Press Enter.
This will result in activating the next blank cell. You can fill every blank cell individually with different inputs. The result will look like this.
Practice Workbook
You can practice by downloading the workbook here.
Related Articles
- How to Find and Replace Blank Cells in Excel
- How to Fill Empty Cells with Last Value in Excel
- Fill Blank Cells with Text in Excel
- How to Fill Blank Cells with N/A in Excel
<< Go Back to Fill Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
