Fill Blank Cells in Excel With Value from Above

Some Excel sheets have blank cells, to make the headings easier to read. However, if you want to sort or filter the list, you'll need to fill in the blanks, using the value from above. Watch this video for a quick way to fill all the blanks, so you can sort and filter without problems!

🔴 Related Excel Videos 🔴

💡 Related Links 💡

Instructor: Debra Dalgleish, Contextures Inc.
#ContexturesExcelTips

'----------
Video Transcript

Sometimes in Excel, you'll end up with data like this, possibly exported from another system where you've got headings, but blank cells below those headings.

Here we can see region and it's only listed once, and then blank to the end of the region, and the employees in each region. You might have one or multiple employees, but again, blank below those employee names.

This is fine for reading the list, but if you want to work with the data, perhaps filter it or sort things, then you need to fill in these blank cells. Here's a quick way to do that.

First, we'll select columns A and B where there are blanks. Then on the Ribbon's Home tab, go to Find & Select, Go To Special. In the Go To Special window, click Blanks, and then click OK.

All the blank cells are selected now and we're going to put in a very simple formula that just says get the value from the cell above.

Type an equal sign and then press the up arrow on your keyboard. You can see the formula here now has changed to A2.

To fill that formula into all the selected cells, press the Ctrl key and then tap Enter. That puts that same formula into all the selected cells.

Now we want to change these formulas to values so that we can move things around without having the values change.

I'm going to select columns A and B where we filled the blanks and then point to the border of what is selected. You'll see a four-headed arrow there.

Press the right button on the mouse and drag slightly to the right. Then drag right back to where you started.

Let go of the right mouse button, and the pop up menu has some choices. Click Copy Here As Values Only.

Now everything in here is a value instead of a formula, and you can sort or filter without any problems.

[ApexRailAutomation]

Pure Gold! Thank you much. I've got close to 20, 000 line items with blank cells. This would have had to be done manually. This saves allot of time.

[jdholzen]

This video literally just saved my life, because I was just about to jump out a window. Thank you for this!

[sandeepcool]

God Bless you! my wife was struggling since many days by copying each cell manually. Due to you she was joyous :) Thanks once again

[patrickjane]

I signed in just to leave a comment - this is amazing. I had 20k rows with a date filled in every 10 or so - I knew somehow this was possible but you proved it. Thank you so much.

[mikegorfin]

I never post comments on these things but I thought there is no way when I search this I'm going to find what I'm trying to do...and this demo was EXACTLY what I needed. So thank you for posting this and tagging it so accurately!!

[colin]

An actual God send! I'd started writing a VBA script for this, then thought there must be a better way, turns out there is! Thank you

[daedal]

My goodness...this just made my life so much easier, I was copy/pasting rows from a 40000+ line database export before this, now it takes seconds. Thank you, Thank you!!!!

[SYEDHASNAINAHMED]

Thank you so much i'm searching for this method for almost from 2 hours and you just help me in less than 2 minutes. Your method was the most easiest and helpful in every way.

[nathanbrown]

I have used this video on a number of occasions (usually have a file that I manipulate each year, just long enough I have to do, e back to listen again). This is a life saver!!!

[jennydrumm]

Very cool; I have needed this for so long! I especially like the "paste value" back in the columns. I've always right-click & copied the column and then right-click and Paste-special-values. I'm not positive, but I FEEL like your way is faster, so I'm sticking with it! Thanks!

[abulhasan]

Your video helped me after 10 years of your effort.. Hats off Mam

[robpickles]

*To anyone encountering problems* Like Debra says, the data may have originated from another system. So the blank cells you see might not be truly blank, there may be something from the other system causing false blank cells in Excel. Fear not, simply filter the columns to display only blank cells, then select all of those cells, then go to the Ribbon > Home > Clear > and select 'Clear All', then clear the filter and then follow Debra's brilliant tutorial! :)

Many thanks for your help Debra, once again you've saved the day for me! :)

[GoldenDave]

My wife and I just got super HYPED for this. This is amazing!!

[trevorlaughs]

THANK YOU! This literally saved me HOURS of work.

[muneebahmad]

Thank you SO MUCH! We have been trying to figure this out at my workplace for years (literally)... you explained it perfectly and solved a very tedious problem! I wish I could pay you for all the time we will save!!!

[Uveryahi]

After 7 years, this is still a life saver!

[Balaji_Kiran]

Thank you so much, the video was very much helpful.... it helped me in saving my time at work.

[VHNews]

after so many years with =if(cell to the left<>"", cell to the left, cell above) and copy plus paste as value for all columns ... thanks a gazillion for sharing in such a comprehensive and fast way!

[peter.muchiri]

Quite helpful indeed, and a time saver! I had to watch several tutorial videos until I landed on this one which was just perfect for what I needed, much appreciated.

[MMAVirtual]

This is a very effective and efficient way to fill the blanks by upper data and convert them to value. Thanks a lot.