Fill Blank Cells in Excel With Value from Above
HTML-код
- Опубликовано: 13 окт 2013
- ✅ 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/MissData
✅ Excel resources I recommend ► myctx.link/xlpick
Instructor: Debra Dalgleish, Contextures Inc.
More Excel Tips and Tutorials: www.contextures.com/tiptech.html
Subscribe to Contextures RUclips: ruclips.net/user/contextu...
#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.
God Bless you! my wife was struggling since many days by copying each cell manually. Due to you she was joyous :) Thanks once again
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
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!
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
My wife and I just got super HYPED for this. This is amazing!!
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
Thank you so much, the video was very much helpful.... it helped me in saving my time at work.
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! This literally saved me HOURS of work.
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!
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.
Exactly what I needed, thank you for the quick tutorial
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! Thanks for letting me know that this tip will save you lots of time
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
Thanks for making this video. I knew there had to be some way to do this. Very helpful!
You are my HERO! Love you!!
Cannot describe how much time this will save me!
Genius, You have saved me 30 minutes!!! Thank you very much!!!!
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!
Thanks!! Simple explanation without extra blabla! Helped me a lot . .
Exactly what I was looking for. Thank you !!!
You have saved hours of my work with this video thank 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!
After 7 years, this is still a life saver!
Thanks for letting me know that the video is still helpful!
This's exactly what I was looking for yesterday!
Thank you so much! This video was really helpful and it's a huge time saving!
You're welcome, Siena! Thank you for letting me know that the video helped you
you are the best! thanks a lot!
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 so much! I've been looking for this solution for ages!
You're welcome, Nóra! Thanks for letting me know that the video was helpful -- Debra
This is a super-cool technique,Debra ! Very Thankful !! - Sam
Detailed explanation in brief... Thanks a lot
Thank you! Great tutorial, very well done.
You are a Wizard. Thank 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!
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!
Thanks this was extremely helpful and simple to do!!!!
Good clear explanation and solved a problem for me - thanks
Exactly what I needed! Thank youuuu
Thank you so much! This tip is very useful to me. It saves me days working on my large dataset.
You're welcome, Khánh! Thank you for leaving your comment.
this was exactly what i was looking for, thank you very clear and helpful
Thank you! I'm glad you found it helpful.
Simple and elegant. Thank you.
You're welcome, and thanks for your comment!
Thank you so much ! This method has saved me so much time.
Thank you so much for the clear explanation.
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!
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!
Oh my goodness thank you thank you!!!!!!! You have saved me so much time and your video was so easy to follow.
You're welcome, Tammy! Thanks for letting me know that this video saved you some time - I appreciate it!
Thanks for the cool tricks!
thank y9u much! I just find the solution in one second after hours to tryout in this video!!!!
Few hours saved, thanks!
You're welcome, Sjoerd, and thanks for your comment!
I am very grateful
I have been looking for this a lot of hours
appreciate it thanks!
You're welcome, Manar, and thanks for your comment!
You the real OG, thanks 😁
Thanks. Just was trying to find the way and got it😊
Thank you.... keep doing that great job
Thank you so much for sharing useful information.
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.
Very helpful. Thank you!
You saved me days! thank you
Thank you very much - I have over 9000 rows - Excellent Video!
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!! you saved lot of effort!! God Bless You !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
OMG!! Miracle.. 😂😂.. Saved my life...thank you sooo much
You're welcome, Sagar, and thanks for your comment!
Thank you so much. This really helps.
*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
this was amazing! thanks for the clear explanation!
You're welcome, and thanks for your comment!
Thank you thank you thank you. You are a savior
Thank you very much, this video helped me!
Thank you, just saved me 20 mins.
Lifesaver! Thank you!!
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!
Thanks for this very useful video, It's very easy to do! I now can save my time of work.
You're welcome, Toukta! Thanks for letting me know that the video helped you save time
I use to copy and column over and due a gnarly formula this is so much faster thanks!
you save lives. really.
life saver! Thanks! Works beautifully!
That's great to hear, Dhavina, and thanks for your comment!
Excellent explanation. Clear and neat. Many thanks.
You're welcome, David, and thanks for your comment!
Very good information . Thank you very much
I have learned the trick in just 1 minute watching this video. Cool!!!
This was truely helpful
Thank you, Sourav, and I'm glad the video was helpful to you!
Thank you for this, i love 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
This saved my precious 3 hours ..!! kudos
Great! Thanks for letting me know that the video help you save time
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!
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!
Thank you!, very useful!
Unbelievably helpful! thank you so much!
You're welcome, and thanks for letting me know that it helped you!
This was life saver. Tried and can confirm, it works.
Thank you, Fronald! I'm glad the video helped you
Very helpful video thanks
You know this simple trick saved me a lot of time.
Thank you Mohammed, and I'm so glad to hear that!
What a GREAT tip!! Thank you!
You're welcome, Robert! Thanks for your comment
Amazing, it is very useful!
brilliant, helped me a lot. thanks
Thank you! I used this technique
Amazing fix! Thank you so much!
You're welcome, Sebastian, and thanks for your comment!
Thank you! This tutorial helped me a lot!
You're welcome, and thanks for letting me know that the video helped you! 🌈
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
This is a big help to me, Thank you!
You're welcome, Humphrey, and thanks for your comment!
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!
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!
Awesome... Thanks a ton!
wow, great trick!!! thanks.