How to fill blank cells with value above in Excel?

When we set up data in Excel, we always leave a blank cell if the data for that cell is the same as the cell above. For example, I have a worksheet such as the following screenshot:

doc-fill-blank-cells-with-value-above1

This form looks neater and nicer if there is just one header row instead of lots of repeated rows. But this also can cause some problems, we can’t use such features as Excel SUBTOTLES or Excel Pivot Tables. In this case, we need to fill the blank cells. Here are some tricks to fill blank cells with value above in Excel.

Using a formula to fill blank cells with value above

Using VBA code to fill blank cells with value above

Kutools for Excel -- Quickly fill blank cells with value above

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubbleUsing a formula to fill blank cells with value above

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

With this method, please do as the following steps:

1. Select the range that contains blank cells you need to fill.

2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option. See screenshot:

doc-fill-blank-cells-with-value-above2

3.Click OK, and all of the blank cells have been selected. Then input the formula “=A2” into active cell A3 without changing the selection. This cell reference can be changed as you need. See screenshot:

doc-fill-blank-cells-with-value-above3

4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells. See screenshot:

doc-fill-blank-cells-with-value-above4

5. At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose Copy, and then press Ctrl + Alt + V to active the Paste Special… dialog box. And select Values option from Paste, and select None option from Operation. See screenshot:

doc-fill-blank-cells-with-value-above5

6.Then click OK. And all of the formulas have been converted to values.


arrow blue right bubbleUsing VBA code to fill blank cells with value above

The following brief VBA code also can help you to finish this task. Please do as follows:

1. Select the range you want to fill the blank cells.

2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

Sub Fill_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

3. Then click doc-multiply-calculation-3 button to run the code. And the blank cells will be filled with the value above.

Note: If there is no value above the blank cells, the blank cells will be filled with the number 0.


arrow blue right bubbleKutools for Excel -- Quickly fill blank cells with value above

With the help of Kutools for Excel, you can fill the blank cells with value above quickly and conveniently.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

After installing Kutools for Excel, please do as follows:

1. Select the range that you want to fill the blank cells. And click Kutools > Insert Tools > Fill Blank Cells. See screenshot:

doc-fill-blank-cells-with-value-above6

2. In the Fill Blank Cells dialog box, Click Based on values from Fill with, and check Down from Options. See screenshot:

doc-fill-blank-cells-with-value-above7

3. Then click OK or Apply. And all of the blank cells have been filled with the value above. See screenshots:

doc-fill-blank-cells-with-value-above8doc-merge-multiple-workbooks-arrow2doc-fill-blank-cells-with-value-above9

Notes: The Fill Blank Cell is a multifunctional tool not only can fill blank cells with value above, but also can fill blank cells with the following forms:

1. Fill blank cells based on values, you can fill down, fill up, fill right and fill left.

2. Fill blank cells based on linear values, you can fill linear value from top to bottom, or from left to right.

3. Fill blank cells with fixed value, such as 0.

You can know more about this Fill Blank Cells feature from here.


Related articles:

How to fill blank cells with linear values in Excel?

How to fill blank cells with 0 or other specific value in Excel?


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment


Security code
Refresh