http://www.extendoffice.com/documents/excel/771-excel-fill-blank-cells-with-value-above.html
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.
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).
Using 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:
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.
Using 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
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub
3. Then click 
button to run the code. And the blank cells will be filled with the value above.
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.
Kutools 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:
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.
Related articles:
Is your problem solved?
Yes, I want to be more efficiency and save time when using Excel No, the problem persists and I need further support
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.
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
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day! 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...
Comments
-3# Jeffatkins 2013-10-17 14:28
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.
Reply | Reply with quote | QuoteAny suggestions? Thanks.
0# Abhijit 2013-12-20 11:49
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.....
Reply | Reply with quote | QuoteStep 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.....
+2# RK 2014-05-20 22:54
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.
Reply | Reply with quote | Quote
0# RK 2014-05-20 22:57
Abhijit, thanks a lot!
Just using IF(A3="",B2,A3) on the newly inserted duplicate column preceding the original column, did the trick!
Reply | Reply with quote | QuoteJust using IF(A3="",B2,A3) on the newly inserted duplicate column preceding the original column, did the trick!
+1# Jeffatkins 2013-10-17 15:04
I finally figured it out!The cell properties were in "Text" format. When I changed it to "General" it worked fine.
Reply | Reply with quote | Quote
+1# Usamah 2013-11-20 06:21
Phew! Thank you so much for this quick formula tip. Works like a charm. Hate copy pasting so many times.
THANK YOU!
Reply | Reply with quote | QuoteTHANK YOU!
+1# HELPLINE 2013-12-03 19:10
THANK YOU VERY MUCH THIS FORMULA SAVE MY LOTS OF TIME
Reply | Reply with quote | Quote
+1# carmelita30A 2013-12-06 14:20
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.
Reply | Reply with quote | Quote
0# Saiful Islam 2013-12-18 05:11
Dear thanks a lot of. I have done my 12 hours job with in 12 minute by the tips.
Reply | Reply with quote | Quote
0# shermina 2014-02-09 11:38
how to transpose this data into a single row??
Reply | Reply with quote | Quote
0# Admin-jay 2014-02-10 03:19
Quoting shermina:
Hello,Please contact me at jaychivo#extend office.com. Please replace # with @.
Reply | Reply with quote | Quotehow to transpose this data into a single row??
Hello,Please contact me at jaychivo#extend office.com. Please replace # with @.
0# Bushral 2014-02-12 08:02
Thank you.
I used the A2 approach, and solved a big file consisting of many empty cells.
Reply | Reply with quote | QuoteI used the A2 approach, and solved a big file consisting of many empty cells.
0# Alun 2014-02-17 10:38
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.
Reply | Reply with quote | Quote
0# Saeed 2014-02-25 23:47
Thanks a lot. This was so handy!! saved a lot of time in a sheet with 1135 rows and many of these instances :)
Reply | Reply with quote | Quote
0# Omar 2014-03-12 22:01
Excel Ent! just what I was looking for, thanks
Reply | Reply with quote | Quote
0# Nash 2014-03-31 08:50
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.
Reply | Reply with quote | QuoteHow do I get KUtools in my excel? Download is displaying "exe.file not valid"
Thanks for assistance.
+1# Venkat 2014-04-05 07:10
Excellent. Thank you very much. This saved a lot of labour work.
Reply | Reply with quote | Quote
0# Hp 2014-04-08 16:09
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
Reply | Reply with quote | QuoteA 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
+1# Dilipkumar Ngcha 2014-04-16 09:04
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.
Reply | Reply with quote | Quote
0# Ravi 2014-04-23 18:34
Great help I was relieved doing it manually
Reply | Reply with quote | Quote
0# Andy 2014-05-08 10:13
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.
Reply | Reply with quote | QuoteYou 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.
0# Trish 2015-02-11 22:05
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!
Reply | Reply with quote | Quote
+1# Patricia 2014-06-10 14:03
Thank you so much! this is a very useful function. Thanks for sharing! cheers!
Reply | Reply with quote | Quote
0# Praveen 2014-06-23 08:28
Thanks for the VBA code it really helped me lot
Thanks once It really lot of time for me....
Reply | Reply with quote | QuoteThanks once It really lot of time for me....
0# niecy wallace 2014-07-03 16:35
Instructions to fill blank cells with value above in Excel.
Reply | Reply with quote | Quote
0# surendar 2014-07-23 07:30
How to use reverse formal means back yo front ofethod
Reply | Reply with quote | Quote
0# Aravind 2014-08-11 20:33
Awesome I should say. Saved lots of time. Thanks a ton. Appreciate very much so cool.
Reply | Reply with quote | Quote
0# Debra 2014-08-19 18:43
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?
Reply | Reply with quote | Quote
0# Diane Pruitt 2014-09-05 19:25
I have been trying to do this for years, trying to write code, etc. This works like a charm!!!! Thank you!!
Reply | Reply with quote | Quote
+1# kundan kumar 2014-10-04 11:28
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.
Reply | Reply with quote | Quote
+1# iftikhar khan 2014-10-17 04:09
Thanks alotThanks alotThanks alot
Reply | Reply with quote | Quote
+1# Amit 2014-10-30 15:25
This is really useful. Clear instructions and worked for me 1st time.
Reply | Reply with quote | Quote
0# Brenda Harwood 2014-12-03 14:55
The VBA code to fill in the blanks worked great - thanks!
Reply | Reply with quote | Quote
0# BALA 2014-12-05 08:45
thank u for your VBA code to fill blank
hot fill in one one column only
please help me
Reply | Reply with quote | Quotehot fill in one one column only
please help me
0# Aditya Rao 2015-01-20 16:30
Thank you for the tip - that's brilliant!
Reply | Reply with quote | Quote
0# Tim 2015-01-21 06:00
Below are the detailed evaluation between Format Factory and its
Mac alternative and the tutorial to use the Data format Factory
for Mac.
Here is my blog: format factory download
Reply | Reply with quote | QuoteMac alternative and the tutorial to use the Data format Factory
for Mac.
Here is my blog: format factory download
0# Sebastian 2015-01-27 18:12
Hey,
Thx for this! Ran the macro and it worked smoothly! Saved me tons of time..thanks!!
Reply | Reply with quote | QuoteThx for this! Ran the macro and it worked smoothly! Saved me tons of time..thanks!!
0# Eric- 2015-02-19 14:09
This is so helpful!!! Thank you for putting this together!
Reply | Reply with quote | Quote
0# Stan 2015-04-21 04:02
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.
Reply | Reply with quote | QuoteI guess will have to do a loop macro.
0# Andy 2015-04-28 10:13
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.
Reply | Reply with quote | QuoteThanks for taking the time to publish the tips.
Thanks For Kutools for Excel Crack 23.00 License Key Free Dowload 2021 You can also visit my Website crackmods.com
ReplyDeleteThis site have particular software articles which emits an impression of being a significant and significant for you individual, able software installation.This is the spot you can get helps for any software installation, usage and cracked.
ReplyDeletekutools for excel crack
tweakbit pcsuite crack
jetaudio music player apk crack
eco falling ball crack
material batch uninstaller crack
ReplyDeleteReally Appreciable Article, Honestly Said The Thing Actually I liked The most is the step-by-step explanation of everything needed to be known for a blogger or webmaster to comment, I am going show this to my other blogger friends too.
restoro-crack
kutools-for-excel-crack
deep-freeze-crack
little-snitch-crack
sidify-music-converter-crack
sony-vegas-pro-crack
sparkol-videoscribe-pro-crack
avocode-crack
beecut-crack
ninjagram-crack
This site have particular software articles which emits an impression of being a significant and significant for you individual, able software installation.This is the spot you can get helps for any software installation, usage and cracked.
ReplyDeletekickasscrack.com
kutools-for-excel-crack
Best Softwares
DeleteKutools for Excel Crack
EasyWorship Crack
ManicTime Pro Crack
Universal Document Converter Crack