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

Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

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

Hint


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?


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase