How to fill blank cells with value above 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 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:

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?

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

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

gold star1200 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...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink 0 Jeffatkins
Thanks for this informative piece. I have excel 2010. Tried filling the blanks using the formula but didn't work.Instead it copies "A2" into all the blank cells.
Any suggestions? Thanks.
2013-10-17 14:28 Reply Reply with quote Quote
Permalink +3 Abhijit
THere is a formula.
Step 1 - Inert a coloumn in suceeding the coloumn in which you want to fill the blanks ie. in the above example, after the A coloumn.

Step 2 - In the Cell(B2) enter the text 'QUE001'. YOu can copy paste the text (or) use the '=' formula.

Step 3 - enter this formula in the Cell(B3)ie. the next unfilled cell - =+IF(A3=$A$4,B2,A3).

Step 4 - Just copy paste this formula in the subsequent cells

step 5 - paste special the values in the 'B' coloumn

Enjoy.....
2013-12-20 11:49 Reply Reply with quote Quote
Permalink +3 RK
Abhijit, thanks a lot. Just using IF(A3="",B2,A3) , on the new duplicated column (that was inserted from the preceding column), worked very well.
2014-05-20 22:54 Reply Reply with quote Quote
Permalink +1 RK
Abhijit, thanks a lot!
Just using IF(A3="",B2,A3) on the newly inserted duplicate column preceding the original column, did the trick!
2014-05-20 22:57 Reply Reply with quote Quote
Permalink +1 Jeffatkins
I finally figured it out!The cell properties were in "Text" format. When I changed it to "General" it worked fine.
2013-10-17 15:04 Reply Reply with quote Quote
Permalink +1 Usamah
Phew! Thank you so much for this quick formula tip. Works like a charm. Hate copy pasting so many times.
THANK YOU! :-)
2013-11-20 06:21 Reply Reply with quote Quote
Permalink +1 HELPLINE
THANK YOU VERY MUCH THIS FORMULA SAVE MY LOTS OF TIME
2013-12-03 19:10 Reply Reply with quote Quote
Permalink +2 carmelita30A
Wow! What a time saver. Great solution to common issue created when using pivot tables and subtotals and extracting choppy data files. Wish I had found this 10 years ago. I recommend showing your boss how manual the process is, waiting for him/her to walk out of the room, using this 1 second trick instead, then heading to the beach.


8)
2013-12-06 14:20 Reply Reply with quote Quote
Permalink 0 Saiful Islam
Dear thanks a lot of. I have done my 12 hours job with in 12 minute by the tips.
2013-12-18 05:11 Reply Reply with quote Quote
Permalink 0 Ahmed Reda
thanks thanks thanks
2014-01-20 08:02 Reply Reply with quote Quote
Permalink 0 shermina
how to transpose this data into a single row??
2014-02-09 11:38 Reply Reply with quote Quote
Permalink +1 Admin-jay
Quoting shermina:
how to transpose this data into a single row??

Hello,Please contact me at jaychivo#extend office.com. Please replace # with @.
2014-02-10 03:19 Reply Reply with quote Quote
Permalink 0 ME
Not sure what version you have of Excel and this is seriously old now but if you haven't figured it out. There is now a "Paste Special" "Transpose" out there that will switch it between Rows and columns.
2015-10-14 16:52 Reply Reply with quote Quote
Permalink 0 Bushral
Thank you.
I used the A2 approach, and solved a big file consisting of many empty cells.
2014-02-12 08:02 Reply Reply with quote Quote
Permalink 0 Alun
Very helpful and clear instructions. Such a simple process and saves a lot of time, not quite teh 12 hrs to 12 mins another person has commented on but definitely saved afair bit of time. My file has over 22,000 entries.
2014-02-17 10:38 Reply Reply with quote Quote
Permalink 0 Saeed
Thanks a lot. This was so handy!! saved a lot of time in a sheet with 1135 rows and many of these instances :)
2014-02-25 23:47 Reply Reply with quote Quote
Permalink 0 Gowri
This is what I wanted. Neat! Works!!
2014-03-06 12:35 Reply Reply with quote Quote
Permalink 0 Omar
Excel Ent! just what I was looking for, thanks
2014-03-12 22:01 Reply Reply with quote Quote
Permalink 0 Hasan
excellent, well wishez for you..
2014-03-29 07:47 Reply Reply with quote Quote
Permalink 0 Nash
Thanks a lot. The formula has worked excellently.
How do I get KUtools in my excel? Download is displaying "exe.file not valid"

Thanks for assistance.
2014-03-31 08:50 Reply Reply with quote Quote
Permalink +1 Venkat
Excellent. Thank you very much. This saved a lot of labour work.
2014-04-05 07:10 Reply Reply with quote Quote
Permalink 0 Hp
From Pivot table,
A B C
1 12345678 HP 50
2 DP 70
3 11112222 HS 20
4 DS 30

Basically i want to display
12345678 (in one cell) and in next cell (HP-50; DP-70)
11112222 (in one cell) and in next cell (HS-20; DS-30)

i m coping the data from pivot table, so there can be a possibility that in column A i can 33334444 and in B it can have 4 items (meaning HP, DV, JC, AB and so forth) and in C there will be corresponding values. Let me know if this can be doable
2014-04-08 16:09 Reply Reply with quote Quote
Permalink +2 Dilipkumar Ngcha
Normally it is not my habit though bad, anyway thanks for your value help. working without this piece of knowledge is really incomplete for those who are working on excel.
2014-04-16 09:04 Reply Reply with quote Quote
Permalink 0 Ravi
Great help I was relieved doing it manually
2014-04-23 18:34 Reply Reply with quote Quote
Permalink 0 Andy
I found the answer to my question that hasnt been published yet.. worked a treat!here is the answer

You can do it the same way you fill from top to bottom using GoTo special blanks. Highlight the area as you normally would, then GoTo special blanks. The GoTo blank function default is to activate the upper most blank cell in your highlighted area. You don't want this cell activated if you've got blanks beneath it followed by data that you want to fill upward. So, once you have your blanks highlighted simply press the control key and while depressed, use your mouse and click on the first blank cell above the data you want to fill upward. Then enter "=" (w/out quotes) and arrow down to select the data you want to fill upward. I just tested this on an entire column of data, and it worked like a charm.
2014-05-08 10:13 Reply Reply with quote Quote
Permalink 0 Trish
Thank you. I was knowledgeable about the GoTo Special command for filling blank cells going downward, but didn't know how to do it upwards...which is what I needed. This works great!
2015-02-11 22:05 Reply Reply with quote Quote
Permalink +1 Milind
:-) This was really helpfull!
2014-05-09 10:08 Reply Reply with quote Quote
Permalink +2 Patricia
Thank you so much! this is a very useful function. Thanks for sharing! cheers!
2014-06-10 14:03 Reply Reply with quote Quote
Permalink 0 Praveen
Thanks for the VBA code it really helped me lot
Thanks once It really lot of time for me....
2014-06-23 08:28 Reply Reply with quote Quote
Permalink 0 niecy wallace
Instructions to fill blank cells with value above in Excel.
2014-07-03 16:35 Reply Reply with quote Quote
Permalink 0 surendar
How to use reverse formal means back yo front ofethod
2014-07-23 07:30 Reply Reply with quote Quote
Permalink 0 Aravind
Awesome I should say. Saved lots of time. Thanks a ton. Appreciate very much so cool.
2014-08-11 20:33 Reply Reply with quote Quote
Permalink 0 Debra
I love this and it works on my test data, but in a work book where I don't want to start at A1, it says no blanks found. Any ideas?
2014-08-19 18:43 Reply Reply with quote Quote
Permalink 0 Diane Pruitt
I have been trying to do this for years, trying to write code, etc. This works like a charm!!!! Thank you!!
2014-09-05 19:25 Reply Reply with quote Quote
Permalink +2 kundan kumar
I use a different trick. Simply put a filter on the first column and filter all the blanks. Them in the first blank cell copy use = upper cell(=A4) in this case and then drag it. This way you can ignore the hidden cell and copy the content of the upper cell without changing the hidden cell.
2014-10-04 11:28 Reply Reply with quote Quote
Permalink +1 iftikhar khan
Thanks alotThanks alotThanks alot
2014-10-17 04:09 Reply Reply with quote Quote
Permalink +1 Amit
This is really useful. Clear instructions and worked for me 1st time.
2014-10-30 15:25 Reply Reply with quote Quote
Permalink 0 Brenda Harwood
The VBA code to fill in the blanks worked great - thanks!
2014-12-03 14:55 Reply Reply with quote Quote
Permalink 0 BALA
thank u for your VBA code to fill blank
hot fill in one one column only
please help me
2014-12-05 08:45 Reply Reply with quote Quote
Permalink 0 Aditya Rao
Thank you for the tip - that's brilliant!
2015-01-20 16:30 Reply Reply with quote Quote
Permalink 0 Sebastian
Hey,

Thx for this! Ran the macro and it worked smoothly! Saved me tons of time..thanks!!
2015-01-27 18:12 Reply Reply with quote Quote
Permalink 0 Eric-
This is so helpful!!! Thank you for putting this together!
2015-02-19 14:09 Reply Reply with quote Quote
Permalink 0 RAMAKRISHNA
thank this is so helpful
2015-03-25 06:48 Reply Reply with quote Quote
Permalink -1 Jen
THANK you!!! save me hours of work.
2015-04-03 16:08 Reply Reply with quote Quote
Permalink -1 Stan
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.
2015-04-21 04:02 Reply Reply with quote Quote
Permalink -1 Andy
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.
2015-04-28 10:13 Reply Reply with quote Quote
Permalink +1 Faisal
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
2015-06-19 10:48 Reply Reply with quote Quote
Permalink 0 Nadia
Thank you so much it's help me a lot
2015-07-27 09:52 Reply Reply with quote Quote
Permalink 0 curvygirlme
thank you - keep forgetting this formula!
2015-08-11 07:20 Reply Reply with quote Quote
Permalink 0 Justin
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.
2015-08-19 20:32 Reply Reply with quote Quote
Permalink 0 Sheryl
Thank you very much! A real time saver.
2015-08-27 20:33 Reply Reply with quote Quote
Permalink 0 EliD
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
2015-09-04 11:26 Reply Reply with quote Quote
Permalink 0 Frank Medoff
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?
2015-09-07 21:10 Reply Reply with quote Quote
Permalink +1 berdict
How to revert it to the blank cell after I done that?
Thanks =)
2015-09-30 07:25 Reply Reply with quote Quote
Permalink 0 Lucky
Thank you very much! A real time saver.
2015-11-04 12:06 Reply Reply with quote Quote
Permalink 0 praveen
thank u so much.it is real time saving. i really wasted my time before i got to konw
2015-11-17 18:17 Reply Reply with quote Quote
Permalink 0 sunitha
thanku so much , it saved my time
2015-11-25 11:43 Reply Reply with quote Quote
Permalink 0 Ani
Saved ages of time. Works great!!! Thanks for sharing
2015-12-27 05:51 Reply Reply with quote Quote
Permalink 0 v s raju
This is really helpful and I saved lot of time
2015-12-30 10:07 Reply Reply with quote Quote
Permalink 0 Indramani
Amazing trick.
This trick will save my lot of time.
Thanks.
2016-02-08 12:00 Reply Reply with quote Quote
Permalink 0 Cathern
Someone necessarily assist to make critically posts I'd state.
This is the first time I frequented your web page and so far?
I amazed with the analysis you made to create this particular
put up extraordinary. Wonderful process!

Also visit my blog link vao bong88: http://m88betwin.com/m88-new/link-vao-bong88-bong88cado-b88ag-moi-nhat.html
2016-03-03 04:52 Reply Reply with quote Quote
Permalink 0 MJ
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!
2016-04-26 13:38 Reply Reply with quote Quote
Permalink 0 Ana
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?
2016-05-03 17:35 Reply Reply with quote Quote
Permalink 0 Vnay
Awesome! Worked for me to update close to 100K rows and saved lot of time...thanks much for the tip!
2016-05-06 10:55 Reply Reply with quote Quote
Permalink 0 JB
This is awesome and helped me a lot. Thank you.
2016-05-20 04:18 Reply Reply with quote Quote
Permalink 0 anusri
Eyeopening!! thanks a lot, thats such a relief
2016-06-29 08:48 Reply Reply with quote Quote
Permalink 0 Anuj kumar
use indirect function: indirect("colum n_name"&row()-1 ) like column is A then indirect("A"&ro w()-1)
2016-07-22 06:00 Reply Reply with quote Quote
Permalink 0 suresh
Thank you so much, for this shortcuts
2016-08-02 10:42 Reply Reply with quote Quote
Permalink 0 vipul
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.
2016-09-02 06:22 Reply Reply with quote Quote
Permalink 0 Wendy P
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
2016-10-11 04:38 Reply Reply with quote Quote
Permalink 0 sanket wazalwar
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......
2016-10-21 06:56 Reply Reply with quote Quote
Permalink 0 ALper
You just saved my life. I mean a few more months from my life time =D thank a lot
2016-11-13 11:33 Reply Reply with quote Quote
Permalink 0 Maaz Ahmad
It was extremely helpful, saved my hours. i was compelled to comment to appreciate your efforts.
2016-12-08 06:09 Reply Reply with quote Quote
Permalink 0 James
A real time saver - Thank you!!
2016-12-13 17:12 Reply Reply with quote Quote
Permalink 0 sreeja
Thank you!! saved my time!!!
2016-12-15 13:13 Reply Reply with quote Quote
Permalink 0 Daniel
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
2017-01-19 08:13 Reply Reply with quote Quote
Permalink 0 Elisa López
Best excel tip ever! Thank you so much!
2017-02-10 17:25 Reply Reply with quote Quote
Permalink 0 Chan Myae Htau
This trick is help me. And I think it will be help on problem of other excel users.
Thanks
Chan Myae Htay
2017-03-06 09:50 Reply Reply with quote Quote

Add comment


Security code
Refresh