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 left screenshot shown:
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.
Fill Blank Cells with value above, specific value or linear values:
With Kutools for Excel’s Fill Blank Cells feature, you can quickly fill blank cells with value above, linear value or fixed value as you need.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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, copy it, and then press Ctrl + Alt + V to active the Paste Special… dialog box. Then 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.
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 60 days.|
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:
The Fill Blank Cells is a multifunctional tool not only can fill blank cells with value above, but also can fill blank cells with specific value or 0 as you need.
1. Select the range that you want to fill the blank cells with a specific value.
2. Apply this feature by clicking Kutools > Insert > Fill Blank Cells, in the Fill Blank Cells dialog box, select Fixed value under the Fill with section, and then enter the value that you want to fill the blanks into the Filled value text box, see screenshot:
3. And then click Ok or Apply button, the specific value will be filled into the blank cells at once. See screenshots:
Note:This utility also can fill blank cells based on linear values, from top to bottom, or left to right.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- 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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoNever mind. i figured out my mistake. This does not work -->Then input the formula “=A2 ” into active cell A3 without changing the selection If you click in an "active cell" you lose your special selection. Instead highlight the cells to be included starting with the first cell you wish to change, then follow the above procedures to find and select Special and BLANKS, then type the formula in the Excel formula box NOT in the cell itself. This worked for me. Great tip! Using it to prepare report for our annual accounting audit.
To post as a guest, your comment is unpublished.· 2 months agoI tried the formula method and it did NOT work for me. I selected Special and Blanks, I entered the formula and hit ctrl enter and only the cell I was in changed. What am I doing wrong?
To post as a guest, your comment is unpublished.· 5 months agoThanks, this saved me about 3 hours. This is so good, I'm still reeling from the awesomeness.
To post as a guest, your comment is unpublished.· 8 months agoThank you for the tutorial. Your Excel quick tips saved the day!
To post as a guest, your comment is unpublished.· 9 months agoAwesome... you saved my day :) I cannot thank you enough!
Maybe the only thing to add is the following watch out:
- when the data set comes from merged cells, when you click unmerge, office will create cells that are not fully blanks. The Go To > Special > Blanks will not work.
- to overcome that:
1. Unmerge cells
2. Select columns and click data > text to columns
3. Proceed with Go To > Special > Blanks
Hope this helps!
- ← Previous
- Next →