Note: The other languages of the website are Google-translated. Back to English

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


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

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 300 handy Excel add-ins, free to try with no limitation in 30 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?


The Best Office Productivity Tools

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. 60-day money back guarantee.
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
Comments (98)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.....
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Abhijit, thanks a lot! Just using IF(A3="",B2,A3)on the newly inserted duplicate column preceding the original column, did the trick!
This comment was minimized by the moderator on the site
Thats trick can't be use if double blank or more at top cell.
This comment was minimized by the moderator on the site
I finally figured it out!The cell properties were in "Text" format. When I changed it to "General" it worked fine.
This comment was minimized by the moderator on the site
Phew! Thank you so much for this quick formula tip. Works like a charm. Hate copy pasting so many times. THANK YOU! :-)
This comment was minimized by the moderator on the site
THANK YOU VERY MUCH THIS FORMULA SAVE MY LOTS OF TIME
This comment was minimized by the moderator on the site
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)
This comment was minimized by the moderator on the site
Dear thanks a lot of. I have done my 12 hours job with in 12 minute by the tips.
This comment was minimized by the moderator on the site
thanks thanks thanks
This comment was minimized by the moderator on the site
how to transpose this data into a single row??
This comment was minimized by the moderator on the site
[quote]how to transpose this data into a single row??By shermina[/quote] Hello,Please contact me at jaychivo#extend office.com. Please replace # with @.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Thank you. I used the A2 approach, and solved a big file consisting of many empty cells.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Thanks a lot. This was so handy!! saved a lot of time in a sheet with 1135 rows and many of these instances :)
This comment was minimized by the moderator on the site
This is what I wanted. Neat! Works!!
This comment was minimized by the moderator on the site
Excel Ent! just what I was looking for, thanks
This comment was minimized by the moderator on the site
excellent, well wishez for you..
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Excellent. Thank you very much. This saved a lot of labour work.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Great help I was relieved doing it manually
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations