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:

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

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.

Using a formula to fill blank cells with value above

Using VBA code to fill blank cells with value above

Kutools for Excel -- Quickly fill blank cells with value above

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
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.

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

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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

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

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


arrow blue right bubbleUsing 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

3. Then click doc-multiply-calculation-3 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.


arrow blue right bubbleKutools 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:

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-above8doc-merge-multiple-workbooks-arrow2doc-fill-blank-cells-with-value-above9

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:

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

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

-1#Jeffatkins2013-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 | Quote
0#Abhijit2013-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 | Quote
0#Jeffatkins2013-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
0#Usamah2013-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 | Quote
0#HELPLINE2013-12-03 19:10
THANK YOU VERY MUCH THIS FORMULA SAVE MY LOTS OF TIME
Reply | Reply with quote | Quote
0#carmelita30A2013-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.


8)
Reply | Reply with quote | Quote
0#Saiful Islam2013-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#Ahmed Reda2014-01-20 08:02
thanks thanks thanks
Reply | Reply with quote | Quote
0#shermina2014-02-09 11:38
how to transpose this data into a single row??
Reply | Reply with quote | Quote
0#Admin-jay2014-02-10 03:19
Quoting shermina:
how to transpose this data into a single row??

Hello,Please contact me at jaychivo#extend office.com. Please replace # with @.
Reply | Reply with quote | Quote
0#Bushral2014-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 | Quote
0#Alun2014-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#Saeed2014-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#Gowri2014-03-06 12:35
This is what I wanted. Neat! Works!!
Reply | Reply with quote | Quote
0#Omar2014-03-12 22:01
Excel Ent! just what I was looking for, thanks
Reply | Reply with quote | Quote
0#Hasan2014-03-29 07:47
excellent, well wishez for you..
Reply | Reply with quote | Quote
0#Nash2014-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 | Quote
0#Venkat2014-04-05 07:10
Excellent. Thank you very much. This saved a lot of labour work.
Reply | Reply with quote | Quote
0#Hp2014-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 | Quote
0#Dilipkumar Ngcha2014-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

Add comment


Security code
Refresh