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:
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.
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.
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:
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:
4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells. See screenshot:
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:
6.Then click OK. And all of the formulas have been converted to values.
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:
Selection.FormulaR1C1 = "=R[-1]C"
3. Then click 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.
With the help of Kutools for Excel, you can fill the blank cells with value above quickly and conveniently.
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:
2. In the Fill Blank Cells dialog box, Click Based on values from Fill with, and check Down from Options. See screenshot:
3. Then click OK or Apply. And all of the blank cells have been filled with the value above. See screenshots:
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.