Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to fill blank cells with value above / below / left / right in Excel?

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

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 with formula

Fill blank cells with value above with Kutools for Excel

Fill blank cells with specific value or 0 with Kutools for 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.

doc-repeat-values-x-times-14

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!


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


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, 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:

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

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


arrow blue right bubbleFill blank cells with value above with Kutools for Excel

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

Kutools for Excel : with more than 300 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:

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-above8 doc-merge-multiple-workbooks-arrow2 doc-fill-blank-cells-with-value-above9

Free Download Kutools for Excel Now!


arrow blue right bubbleFill blank cells with specific value or 0 with Kutools for Excel

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:

doc-fill-blank-cells-with-value-above9

3. And then click Ok or Apply button, the specific value will be filled into the blank cells at once. See screenshots:

doc-fill-blank-cells-with-value-above8 doc-merge-multiple-workbooks-arrow2 doc-fill-blank-cells-with-value-above9

Note:This utility also can fill blank cells based on linear values, from top to bottom, or left to right.

Free Download Kutools for Excel Now!


arrow blue right bubbleFill blank cells with value above / linear values / specific value

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!


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 Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Elle L · 2 months ago
    Excellent instructions, great illustration and best of all, easy to follow.
  • To post as a guest, your comment is unpublished.
    Roman Kultajev · 7 months ago
    Great! Thank you!
  • To post as a guest, your comment is unpublished.
    Paul Schultz · 1 years ago
    Never 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.
    Paul Schultz · 1 years ago
    I 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.
    Raymond · 1 years ago
    Thanks, 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.
    Jordan · 1 years ago
    Thank you for the tutorial. Your Excel quick tips saved the day!
  • To post as a guest, your comment is unpublished.
    Alberto · 1 years ago
    Awesome... 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!

    Thanks!!
  • To post as a guest, your comment is unpublished.
    Gultasab · 1 years ago
    Thanks brother, for an Estimator you saved a 50% of time by sharing this brillient trick.
  • To post as a guest, your comment is unpublished.
    Cameron · 2 years ago
    thanks so much! Saved me an hour at least....
  • To post as a guest, your comment is unpublished.
    nahid · 2 years ago
    How can copy below cell value to upper cell. like ctrl d works..
  • To post as a guest, your comment is unpublished.
    Paula · 2 years ago
    Thank you! Was really helpful!!
  • To post as a guest, your comment is unpublished.
    Chan Myae Htau · 2 years ago
    This trick is help me. And I think it will be help on problem of other excel users.
    Thanks
    Chan Myae Htay
  • To post as a guest, your comment is unpublished.
    Elisa López · 2 years ago
    Best excel tip ever! Thank you so much!
  • To post as a guest, your comment is unpublished.
    Daniel · 2 years ago
    Can I save my drop down list formula? When I close my excel and open again I do not get the drop down list i created. Thanks
  • To post as a guest, your comment is unpublished.
    sreeja · 2 years ago
    Thank you!! saved my time!!!
  • To post as a guest, your comment is unpublished.
    James · 2 years ago
    A real time saver - Thank you!!
  • To post as a guest, your comment is unpublished.
    Maaz Ahmad · 2 years ago
    It was extremely helpful, saved my hours. i was compelled to comment to appreciate your efforts.
  • To post as a guest, your comment is unpublished.
    ALper · 2 years ago
    You just saved my life. I mean a few more months from my life time =D thank a lot
  • To post as a guest, your comment is unpublished.
    sanket wazalwar · 3 years ago
    Fill blank cells with value above with formula :
    4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells. See screenshot:
    Can somebody help me on step no 4
    i am unable to copy the formula for other blank row......
  • To post as a guest, your comment is unpublished.
    Wendy P · 3 years ago
    I have used this macro and it is very good - Insert blank rows when value changes with VBA code. I need to have 2 blank rows inserted (instead of the one) and in the 2nd blank row I need the name of the value that was used in the separation.

    i am a novice at this so please can you help me
  • To post as a guest, your comment is unpublished.
    vipul · 3 years ago
    Thank you so much. I can not describe in the word that how much grateful to you. The solution saved my labor of at least 5 hrs. Thanks once again.
  • To post as a guest, your comment is unpublished.
    suresh · 3 years ago
    Thank you so much, for this shortcuts
  • To post as a guest, your comment is unpublished.
    Anuj kumar · 3 years ago
    use indirect function: indirect("column_name"&row()-1) like column is A then indirect("A"&row()-1)
  • To post as a guest, your comment is unpublished.
    anusri · 3 years ago
    Eyeopening!! thanks a lot, thats such a relief
  • To post as a guest, your comment is unpublished.
    JB · 3 years ago
    This is awesome and helped me a lot. Thank you.
  • To post as a guest, your comment is unpublished.
    Vnay · 3 years ago
    Awesome! Worked for me to update close to 100K rows and saved lot of time...thanks much for the tip!
  • To post as a guest, your comment is unpublished.
    Ana · 3 years ago
    Im trying to use the KUTOOLS fill blank space based on value and it is not working, do the nunbers or values have to be en a specific format?
  • To post as a guest, your comment is unpublished.
    MJ · 3 years ago
    Hello,
    I used this formula and works wonders,but I need to change the font color to be transparent for the data that was copied below. Could you please advise on how to do that?

    Thank you for your help!
  • To post as a guest, your comment is unpublished.
    Indramani · 3 years ago
    Amazing trick.
    This trick will save my lot of time.
    Thanks.
  • To post as a guest, your comment is unpublished.
    v s raju · 3 years ago
    This is really helpful and I saved lot of time
  • To post as a guest, your comment is unpublished.
    Ani · 3 years ago
    Saved ages of time. Works great!!! Thanks for sharing
  • To post as a guest, your comment is unpublished.
    sunitha · 3 years ago
    thanku so much , it saved my time
  • To post as a guest, your comment is unpublished.
    praveen · 3 years ago
    thank u so much.it is real time saving. i really wasted my time before i got to konw
  • To post as a guest, your comment is unpublished.
    Lucky · 4 years ago
    Thank you very much! A real time saver.
  • To post as a guest, your comment is unpublished.
    berdict · 4 years ago
    How to revert it to the blank cell after I done that?
    Thanks =)
  • To post as a guest, your comment is unpublished.
    Frank Medoff · 4 years ago
    Close but not exactly what I am looking for.
    Example:
    Column A with random placed values. Changes every time download form server.
    To setup a table or pivot table I need to fill in the blanks as mentioned by users above.
    However, there are thousands of rows in the download, with data appearing randomly down the column. I need to copy the first cells data down to the last empty cell before the next cell with data. Then copy the new data down to the last empty cell above the next cell with data.
    The routine ends at a specified row.
    Each time the file is downloaded from the server the cells with data in column A change.
    Next step, to execute this vba macro for any selected column.
    The suggestions above work as long as I manually repeat the process at every time a cell with new data is encountered.
    With over twenty thousand rows of data randomly interspaced the above ideas require the same time as copy and paste by hand.
    Any ideas on how to automate using a vba macro?
    • To post as a guest, your comment is unpublished.
      Sue · 2 years ago
      Hi Frank,

      This is exactly the situation I have! I can't find anything to work. Did you ever get an answer to your question?

      Sue
  • To post as a guest, your comment is unpublished.
    EliD · 4 years ago
    Great help! very performant macro. I've found other macro but very heavy when cell to fill are more then 1500. this one finish everything within a minute... 8) Thanks
  • To post as a guest, your comment is unpublished.
    Sheryl · 4 years ago
    Thank you very much! A real time saver.
  • To post as a guest, your comment is unpublished.
    Justin · 4 years ago
    Is there a formula that can populate column D without filling the blank cells in column A so it looks like the result below?

    Name......Order....Date.....Introduce
    QUE.......10......10/20.....A handy tool
    *blank*...31......11/2......A handy tool
    *blank*...22......11/16.....A handy tool
    WEN.......23......11/25.....with this utility
    *blank*...34......12/1......with this utility

    Thanks for your help.
  • To post as a guest, your comment is unpublished.
    curvygirlme · 4 years ago
    thank you - keep forgetting this formula!
  • To post as a guest, your comment is unpublished.
    Nadia · 4 years ago
    Thank you so much it's help me a lot
  • To post as a guest, your comment is unpublished.
    Faisal · 4 years ago
    thanks this is very helpful - one question i used the VB code and merged it with another part of my code which unmerges cells. the issue is that in the blank cells now it shows R[-1]C instead of the value in the cell above. any idea how i can fix this? I am using office 2013.

    thanks
  • To post as a guest, your comment is unpublished.
    Andy · 4 years ago
    I would probably depress myself with how much time i've wasted in the past doing this kind of thing manually, but i'm in a positive mood so i'll think about all the time it will save me in the future!

    Thanks for taking the time to publish the tips.
  • To post as a guest, your comment is unpublished.
    Stan · 4 years ago
    what are the limitation? I'm testing on one column with 95k rows and about 30k unique lines (each has about five blanks under that need to be filled). I get a #REF error. Works on about 10k lines.

    I guess will have to do a loop macro.
  • To post as a guest, your comment is unpublished.
    Jen · 4 years ago
    THANK you!!! save me hours of work.
  • To post as a guest, your comment is unpublished.
    RAMAKRISHNA · 4 years ago
    thank this is so helpful
  • To post as a guest, your comment is unpublished.
    Eric- · 4 years ago
    This is so helpful!!! Thank you for putting this together!
  • To post as a guest, your comment is unpublished.
    Sebastian · 4 years ago
    Hey,

    Thx for this! Ran the macro and it worked smoothly! Saved me tons of time..thanks!!
  • To post as a guest, your comment is unpublished.
    Aditya Rao · 4 years ago
    Thank you for the tip - that's brilliant!
  • To post as a guest, your comment is unpublished.
    BALA · 4 years ago
    thank u for your VBA code to fill blank
    hot fill in one one column only
    please help me