Fill Blank Cells in Excel With Value from Above
ฝัง
- เผยแพร่เมื่อ 10 ก.ย. 2024
- ✅ Get the sample Excel file to follow along: myctx.link/FILL
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 🔴
Fill Blank Cells From Above (updated) ► • Fill Blank Cells From ...
Repeat Pivot Table Headings ► • Repeat Headings in Exc...
Fix Excel Data in a Flash ► • Fix Excel Data in a Fl...
💡 Related Links 💡
Excel Data Entry Tips ► myctx.link/FILL
Repeat Pivot Table Headings ► myctx.link/Mis...
✅ Excel resources I recommend ► myctx.link/xlpick
Instructor: Debra Dalgleish, Contextures Inc.
More Excel Tips and Tutorials: www.contextures...
Subscribe to Contextures TH-cam: www.youtube.co...
#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.
Pure Gold! Thank you sooooooo 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.
This video literally just saved my life, because I was just about to jump out a window. Thank you for this!
I can understand your relief, ditto here !!
😂
Me too Bruh
Biggest relief in life after seeing this video
*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! :)
Your extra tip was a life saver for me. Thanks
@@barnabasocholla1488 You're are welcome Barnabas. :) Thank you for letting me know.
Can somebody help me pls
@@barnabasocholla1488 pp oil
Thankyou so much for this tip literally it help me alot
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.
Thank you! I appreciate your kind words!
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!
You're welcome, Verner, and thanks for letting me know that you finally found this fast way!
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!!
You're welcome, and thanks for posting your comment! I'm glad the video (and tags) helped you
God Bless you! my wife was struggling since many days by copying each cell manually. Due to you she was joyous :) Thanks once again
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!!!!
You're welcome, and thank you for letting me know that the video made your life easier!
After 7 years, this is still a life saver!
Thanks for letting me know that the video is still helpful!
I have a huge data with 10,000 rows to be filled and it would have eaten up so much of time!! thanks for this!!! May God bless you!
You're welcome, Joslita, and thanks for letting me know that the video helped you save time!
ur work from 2013 is still helping till this period of time
You're welcome, Suhail, and I'm glad this video is still helpful!
Thank you so much❤ 9 years down and still your video saves lives and time 🎉
You're welcome, Harshith, and thanks for your comment! It's great to hear that the video helped you, after all these years!
Cannot describe how much time this will save me!
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!!!
Thanks, Nathan! I'm glad this video helps you with those annual tasks
Thank you So much.
Being beginner to excel, I tried many tricks in shell scripting to achieve the same. Alas, I didn't get it.
So thanks for posting such an informational video.
You're welcome, Mohammad, and thanks for letting me know that the video helped you!
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.
Thank you, Peter, and I'm glad you finally found what you needed!
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.
Thanks, Max! It's great that the video helped you, and I appreciate that you signed in to leave a comment.
you dont even understand how i am jumping for joy right now. i had 7000 lines with this issue. thank you so so so so so so much
You're welcome, and thanks for letting me know that the video helped you with those 7000 lines!
This is a very effective and efficient way to fill the blanks by upper data and convert them to value. Thanks a lot.
You're welcome, Mohammad, and thanks for your comment!
OMG, this is the video I have been searching for days, finally, I am glad I didn't give up for searching, I knew there is some trick. I search earlier was "auto fill", which does not work. Thank you sooooooo much!
You're welcome, and thanks for letting me know this video helped you, after you searched for so long!
Thank you. I had forgotten how to do this and this is a good refresher
You're welcome, Joshua, and thanks for your comment!
thanks for this simple instruction. i've always wondered how to do this easilly especially with a very long sheet with data.
You're welcome, Roxanne, and thanks for your comment! This would certainly save you some time in a long sheet.
So its 1 am, deadline tomorrow and your trick just saved me a bunch of hours. So Im using a bit of that time to thank you. Amazing video.
Aw, thank you, and I appreciate hearing that the video helped you meet that deadline!
THANK YOU! This literally saved me HOURS of work.
Few hours saved, thanks!
You're welcome, Sjoerd, and thanks for your comment!
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
Your narration was so comforting to understand each step, Thank you.. 👍
You're welcome, Ali, and thanks for your lovely comment!
@@contextures You're welcome Dear..
You know this simple trick saved me a lot of time.
Thank you Mohammed, and I'm so glad to hear that!
My wife and I just got super HYPED for this. This is amazing!!
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!!!
You're welcome, Muneeb, and thanks for letting me know that the video will help you save time!
I use to copy and column over and due a gnarly formula this is so much faster thanks!
Your video helped me after 10 years of your effort.. Hats off Mam
Thank you, Abul, and it's great to hear that my video helped you, after so many years!
Thanks a million dear. I am trying to do this myself almost 3 days.But I couldn’t. After watching this Video I got the solution.
You're welcome, and thanks for letting me know this video had the solution you needed. I appreciate it!
This was life saver. Tried and can confirm, it works.
Thank you, Fronald! I'm glad the video helped you
Thank you saving my day by this video, it just helped me a lot. thank you so much
You're welcome, Soumya, and thanks for letting me know the video helped you!
Thanks a lot, mam this video worked for me, May Almighty bless you with huge success😇
You're welcome, Bushra, and thank you for your kind words!
This is a very common problem using spreadsheets, thanks for making this video :D
You're welcome, and thanks for your comment - I appreciate it!
Thank you so much. This help me save several hours of my life.
You're welcome, and thanks for letting me know that it helped you save time!
Thank you so much, the video was very much helpful.... it helped me in saving my time at work.
You are a Wizard. Thank you!
You're my savior, I had no idea you could select blanks like that. Now I don't need to autofill 2000 rows
Thank you, Tubular! Glad to hear that the video helped you
thanks for this, how i have suffered for 20 years when works seems easier than ever
You're welcome, and thanks for your comment!
Thank you sooooo muchhhh, you saved hours of work for me
You're welcome, Anika, and thanks for letting me know!
Excellent tip and delivered very clear. Thank you very much, you saved my day.
You're welcome, and thanks for your comment - I appreciate it!
Genius, You have saved me 30 minutes!!! Thank you very much!!!!
Wish I could like it twice or triple. Thank you so much!
You're welcome, Tani, and thanks for your lovely comment!
This was truely helpful
Thank you, Sourav, and I'm glad the video was helpful to you!
amazing!!! thanks for the sharing!!! just resolve my nightmare!
You're welcome, Mio, and thanks for your comment!
Thank you for this tutorial
very helpful for me.
You're welcome, Sarthak, and thanks for your comment!
I have learned the trick in just 1 minute watching this video. Cool!!!
Thank you! This tutorial helped me a lot!
You're welcome, and thanks for letting me know that the video helped you! 🌈
This is gold! Thank you!
You're welcome, Alberto, and thanks for your comment!
You Lady are a Genius .. Thank you so much ...
Excellent video, was looking for this....and this is a perfect video...thanks alot
You're welcome, Aamir, and thanks for letting me know that the video had what you were looking for!
Thank you so much you have saved lot of my time 👍
You're welcome, Sayyad, and thanks for letting me know it helped you save time!
OMG!! Miracle.. 😂😂.. Saved my life...thank you sooo much
You're welcome, Sagar, and thanks for your comment!
why i did not think of that before. Very clever! Thanks a lot!
You're welcome, and thanks for your comment! It's always fun to find a new trick in Excel
Pretty helpful, make my life easy
Thanks, and I'm glad to hear that!
This just saved me hours of manual work
Thanks, Eric, and I'm glad to hear that!
Awesome formula , solve my current issue
Thank you, and I'm glad the video helped you
Exactly what I was looking for..Thank you soooo much M'lady
You're welcome, Deva, and thanks for letting me know the video had what you were looking for!
Thanks!! Simple explanation without extra blabla! Helped me a lot . .
quicker way to paste values is select the cells you do not want the formula in and press. ctrl+C, Then Ctrl+alt+V (a windows will popup) then select "Values" and hit ok
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!
Thank you for making this video. I just used it with my spreadsheet, and it saved me a lot of time ")
You're welcome, Paola, and thanks for letting me know that it helped you save time!
This is a big help to me, Thank you!
You're welcome, Humphrey, and thanks for your comment!
You have saved hours of my work with this video thank you!
THANK YOU SAVE MY LIFE
You're welcome, Faqih, and thanks for your comment!
You are a time saver!
Thanks, Griffin, and I'm glad it helped you save time!
Thank you!! you saved lot of effort!! God Bless You !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thank you so much Mam..u saved my life
You're welcome, Vinoth, and thanks for your comment! I'm glad the video helped you
Unbelievably helpful! thank you so much!
You're welcome, and thanks for letting me know that it helped you!
Dear lady, you are amazing, thank you, saved me a TON of time! Bless you :))
You're welcome, Johana! Thank you for your lovely comment!
Omg, this video is a life saver!!!!
Thank you, Divya! I'm so happy to hear that!
thanks mam its very useful in office
Your teaching style is very simple ( in short video U teach very meaning full information )
Thanks 🙂
You're welcome, and thank you for your lovely comment - I appreciate it!
I don't know how to thank you. You saved me
You're welcome, Kasun, and thanks for your comment. I'm glad the video helped you!
Thanks, Mam, This saved my lot of work
Thanks, and I'm glad to hear that!
This is totally magical!
Thank you, Suresh!
Fine very good tool to save the time and laborious job convert to a good mood job.
Thank you, Syed, and I'm glad this video will help you save time!
Thank you,Very very very very very usefull information shared. This helps me lot in my work. .
You're welcome, Santosh, and thanks for your comment! I'm glad the video was useful to you!
Thanks this is Very Helpfull.... Many many thanks again
You're welcome, Rajesh, and thanks for your comment!
Thank you very much - I have over 9000 rows - Excellent Video!
Thank You, It's very helpful for me.
You're welcome, Shah, and thanks for letting me know it was helpful for you!
Detailed explanation in brief... Thanks a lot
Very simply explained, excellent.
Thank you, Phil!
Thank you very much, it was really helpful for me
You're welcome, Saad, and thanks for letting me know the video helped you!
Puuuure gold. Thank you so much
You're welcome, Oskar, and thanks for your comment!
thank you, thank you thank you - you have no idea - I had a sheet with over 14000 records and this would have taken me hours if not days manually.
Excellent explanation. Clear and neat. Many thanks.
You're welcome, David, and thanks for your comment!
thanks ..It saved me lot of time
You're welcome, Arafat, and thanks for your comment!
My fair maiden, for ya has saved ones arse today 🎉🎉
Glad to hear that all is well in the Excel realm again! 🏰
life saver! Thanks! Works beautifully!
That's great to hear, Dhavina, and thanks for your comment!
Thank you so much ! This method has saved me so much time.
you saved me, my project deadline date is coming near and i could not work on it because of it have blank cells in id column which needed to be filled with duplicates values of upper cells & there are than 400000 cells, but now my problem is solved.....Thank You.........:)
My system is not selecting blank cells instead it goes down to blank cell end of my sheet. Please help
I've got another video that shows how to fix those cells that look blank, but don't act like other blank cells:
th-cam.com/video/se-keFdKW4M/w-d-xo.html
Hello, when I go to drop the cells back in the correct spot from my right click, I don't get the option to insert them as values. That option doesn't show for me. Any reason why?
I don't know of a reason why that option wouldn't appear. You could copy and paste values with keyboard shortcuts instead. To copy: Ctrl+C. To paste values: Ctrl+Alt+V (that opens a dialog box), then tap V, then press Enter
you made my day . Thank you so much
You're welcome, Ranjit, and thanks for your comment! I'm glad the video made your day!
LIFE SAVER!!
Thank you, and I'm glad the video helped you!
Thank you 🎉- you were very helpful
You're welcome, Edwin, and thanks for your comment!
you are the best! thanks a lot!