How to Select Entire Columns with Blank Cells in Excel

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
In this tutorial we'll focus on various Excel shortcuts to highlight columns containing blank cells.
Key Highlights:
▪️ Shortcut Techniques: Different methods for highlighting a column in Excel based on the data's location and format.
▪️ Control Space Method: For data starting from row 1 and not in a table.
▪️ Control End Method: Highlighting from the current column to the last cell of the worksheet.
▪️ Control Period Method: For data not starting from the first row, including a workaround for blockages.
▪️ Excel Table Advantage: Easiest method of highlighting using tables in Excel.
Select the correct shortcut combination based on how your data is organized. Thank YOU for your contribution.
Topic: Different methods to select a column of data in Excel including blanks.
Link to Part 1: • Excel Hidden Shortcut ...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

[@NamanArya]

You know the thing I appreciate most about Leila? She's so very open to learning and accepts that her solution ain't the best one out there. Infact, goes on to record a video and tell the world that hey people there exists a better solution. Very rarely do you come across such instructors who impart knowledge and learn from others at the same time. Great work Ms Gharani. Kudos. :)

[@LeilaGharani]

Thank you! I really appreciate that.

[@123mailashish]

Well said!!! The step towards growth is accepting ur mistakes. Leila is one of the best in excels inspite of that c accepts that certain solutions are better than hers. Defntly c is alws on learning curve.

[@joshuamanampiu6489]

Yes this is very refreshing and it has really benefited me. Great job

[@garrysoriano2591]

Agree

[@ChrisLee-yr7tz]

"Just ignore the big mess over there"...points to no mess whatsoever.

[@LeilaGharani]

Well, it's unorganized. Qualifies as mess for me :)

[@BertLankester]

@@LeilaGharani You mean the books are not organized by color? I wish I was this "unorganized"

[@towfeek1338]

Thank you Leila always... even in tough times you are always there with your amazing MS Excel videos...

[@mostafa4321]

I usually use the table approach but all the other methods were an eye-opener. Thank you, Leila, stay safe. we are all going crazy here in Orlando Florida.

[@konstantinevseyenko9312]

Excel-lent, Leila ! :) Thanks a lot.

[@christoskonstantinou1074]

Thanks once again for your awesome lessons. Keep healthy

[@darrylmorgan]

Hi Leila!Thank You For Sharing These Shortcuts...Stay Safe In These Worrying Times!

[@azizagamirzeyev1143]

I would like to right it to your previous hidden shortcut video 😊. It's good to see you have found it yourself 👍🏻

[@saids.4307]

i NEED a shortcut to remember all the shortcuts 😁

[@theofficialseries]

😂🤣😂 me too

[@NaveenCha]

Everyone waiting for the same😜

[@albatainehahmad]

use them, and you will be master them, not only remember them, you will be able to use them before you can think about it :)

[@ckokse]

So true man!

[@LeilaGharani]

😁

[@shehrazrabbani]

As you said in the video, the Ctrl+. was such a different thing for me to know.. but it did seriously make sense! NEVER STOP LEARNING

[@BYlmaz-ny2qu]

I think you have a great teaching director.

[@startupstartup3887]

Thank you Leila your every excel video has something new thing to learn

[@vida1719]

Thanks for continuation. Keeping data in the table is always the best

[@emarcos40]

Great input. Thanks Leila.

[@kamiltecza4987]

Thank you Leila, you're great!

[@krn14242]

Great update Leila. Stay safe.

[@adityapatnaik7078]

AMAZING. WHAT YOU DISCUSS HERE HAS A LOT OF VALUE.

[@robertovelicaz7719]

Thanks for the great tips as always! You stay safe also!

[@dennisd5776]

Thank you for all of the shortcuts. Another wonderful video with useful tips.

[@osamasalah341]

Thank you Leila, you are always wonderful!

[@khalidrehman5045]

Thanks LG for the various ways, thumbs up 👍

[@mattschoular8844]

Thanks Leila. I appreciate your videos. Today I plan on learning how to create dynamic drop down lists to help me finish my website build project. And yes, I will be referencing a couple of your videos to do so.

[@maximeguilbault6893]

Thanks for the shortcuts!

[@mirasforaccounting570]

Thank you leila I love learn from you .

[@excelisfun]

Great video : ) Go Team!!!!!!

[@LeilaGharani]

Thank you Mike. I’m grateful for our active online Excel team :)

[@excelisfun]

@@LeilaGharani We are VERY Lucky : ) : ) : ) : )

[@tolkienfan4ever]

Hey, Leila! Thanks for checking with us all at the end of your video. Great content in there. Staying safe and sane 5 months after your posting. Hope you are doing well these days!

[@samuelkodjoe1645]

that was really powerful!!!...lovet it!!

[@rahuljangra646]

Thanks for sharing knowledge.......

[@alexkerr7436]

Thank you. Wow. I didn't know I needed this. But will use everyday.

[@nonoobott8602]

These shortcut combos are amazing, I actually never knew these. Thanks Leila

[@LeilaGharani]

You're welcome. I'm glad you like these!

[@8754911081]

U WON OUR"S HERATS ALWAYS

[@prasantapaul3884]

Thanks ma'am it's simply superb!!!

[@laasyavenugopal4704]

Thank you leila It is really helpful.👍🏻

[@kimfucku8074]

Big mess there? It's more tidy than at Ikea! Stay safe too and thank you for your content!

[@LeilaGharani]

Haha. Well, it's totally unorganized. To look for something specific always takes me ages :)

[@wayneedmondson1065]

Hi Leila.. great video and tips.. thanks! Here's another.. many of the shortcuts are dependent on the cell pointer being in row 1 of the subject column. If you happen to highlight the subject column with CTRL+SHIFT while the cell pointer is not in row 1 of the column, just press CTRL+. and the cell pointer will move to row 1 and the column will remain highlighted. From that point, all of the other shortcut procedures that you described will work. These are really super tips and tricks for improving functionality and efficiency. Thanks for sharing and for always adding something new to the Excel tool box :)) Thumbs up!!

[@nunocorreia5570]

Great, as usual.

[@robertakwasiadjei8223]

Thanks Once Again

[@gabriellaprievara7379]

Thank you for the great tips! Very useful in my work. Stay safe as well!

[@LeilaGharani]

That's great Gabriella. I hope it will come in handy.

[@jabrik9]

Terima kasih mba Leila atas tips&tricknya 👍

[@Anti-Hindian]

I’ve watched like 50+ videos of yours! Every time I watch the content of the video. But this time I had to admit that I watched you rather than the content of the video😃 😄😍 #simplyperfect#perfectlysimple!!!

[@majidsiddique8227]

Excellent Leila, very fantastic

[@satyendrasubedi7650]

Thanks for the info

[@theofficialseries]

You are the Best 😘 amazing video thanks to share with us......🤗

[@m.a.stough4994]

Leila, great job. Stay safe as well!

[@kmcrntn]

Your channel helps me so much. Thank you!!

[@LeilaGharani]

That's great Kimberly. I'm happy to hear that.

[@pareshprajapati7249]

Good video, as always. "Ctrl + ." actually cycles round the corners of the selected multi-column selected range.

[@satishgopal2406]

Thank you ✨✨🙏✨✨

[@yulinliu850]

Thanks Leila!

[@sachinrv1]

Well done !!!

[@hassanraza-vb5ss]

Thank you Leila :)

[@angelapieper5019]

This was great!!! Thank you! I feel the same way about tables, but sometimes you're having to manipulate data from another person who doesn't use tables so these shortcuts are awesome. I didn't know about (ctrl + .) either!

[@LeilaGharani]

Thank you Angela. Agree - it's good to keep these in mind depending on the situation.

[@ahmadkaleem9456]

Hi.. Thanks for your help

[@erendiz79]

Dear Leila, it's good to see you healthy and still vivid.. ;) I would like to comment this for the ctlr+. (period) method. Ctrl + . is like a sort of "line break" in programming when you are selecting ranges. So every time you want a new consequent short key combination you should break the former with a ctrl+period when it doesn't do what you would like it does. When you are in a cell above and outside a table you can use this combination: ctrl+space (; optionally ctrl+.) ; ctrl+shift+uparrow; ctrl+. ; ctrl+shift+downarrow. Similarly in any column that doesn't contain a table you can do the same. Sometimes in step 2 you don't need this break ctrl+. because the ctrl+shift+arrow combination immediately works. I don't understand why it does sometimes and why it doesn't the other times.

[@dennishd848]

Thank a lot

[@JR-xr4fc]

of course I find this after I make a sheet thousands of rows long lol. Thank you!!!!!!!!!!

[@KnuYorker]

This is very useful. Will use it immediately in a workbook I'm cleaning up. Thanks for posting.

[@LeilaGharani]

That's great. Hope it will come in handy.

[@ashoksahu9546]

Excellent video Mem.

[@babarqureshi5043]

Thank you very much mam.

[@salehmohammad5170]

Excellent... Carry on lovely

[@user-iz1ml6gs2y]

Thank you

[@chanchaldas6229]

Nice.. Tnx for support.☺️

[@kamleshsah6507]

Excellent

[@SolidSnake59]

Now that's a real shortcut! :)

[@umarpathan78]

Leila good work keep it up

[@sujitguha6295]

Its awesome

[@myem1983]

I'm a fan of your work!!! ❤❤ Btw instead of using cntr + . I used to do shift tab 😁

[@bret0974]

wow. you are so pretty, and very good in explaining things.... i'm learning a lot!

[@GSPirosaki]

Aha! Thank you.

[@alokghosh9311]

Very Good...

[@paulprobst7728]

@Leila Gharani Thank you Leila; Always appreciate your helpful tutorials; this one is awesome, as always!! Ps. And Ctrl+. when your selection is more than 1C wide will move you to the next corner around that selection. Fun stuff when you need to get there that way!!

[@dhunpagla3871]

Thanks L.....for another life saving clever tip.....👌👌👌👌 keep surprising

[@LeilaGharani]

I'll do my best :)

[@mamalik1088]

Super explain

[@subhrajyotisaha4u]

ctrl+. wow i learned something new

[@hosseinhosseinpoor4845]

thanks

[@nileshzala6317]

Amezing

[@Quantom_Cartography]

Hey Leila, Thanks for all the tips and tricks. Certainly, the update to this video was awesome. I am sure there are other methods and key combinations also, but Shift + Space will highlight the current row. Also Ctrl + Shift + Space will select the 'Current Region' - Ctrl + * also does the same thing Cheers

[@LeilaGharani]

Glad you like part 2 Peter :)

[@jajwarehouse1]

For the record, I like this video format better than your usual. I am sure it also has the added benefit of less editing for you.

[@LeilaGharani]

Good to know. Yes - it has a lot less editing than the other versions. I'll keep that in mind. Probably switch things up from time to time.

[@dkb1111]

Superb

[@chriss2595]

It's also my experience: working with tables is for a lot of things the better way, sometimes the only way (e.g. power query). And thx for sharing those shortcuts, you are really listening to your 'audience'.

[@LeilaGharani]

My pleasure Chris. I'm really thankful for this community.

[@umarpathan78]

Great

[@andrewmoss6449]

Thank you for providing us with another great video whilst we get through this coronavirus lockdown together. Big shame that the Global Excel Summit in London has been postponed, but it’s probably the right thing to do given the circumstances. Look forward to it when it gets rearranged anyway. 🙂

[@LeilaGharani]

Hi Andrew. Yes. It's a pity how things turned out. Looking forward to the new dates.

[@lahotipushkar]

Hi Liela, Ctrl+Backspace is also option to go back to selected cell. Same as Ctrl+.

[@hajaksksnsjksksbsnsn]

Mam you are really amazing...please upload video about drop-down calenders....thanks

[@edge5817]

Hi Leila, as per my previous comment. For Data_2 sheet, you can select A3 (start cell), then click inside the name box and type "A1000" (assume high number), then press SHIFT+ENTER.... and then CTRL+SHIFT+ARROW UP

[@albatainehahmad]

@Leila Gharani In the second scenario (Selecting data column with empty cells, where the first Non-Empty cell is NOT in Row 1) ,we could use the following approach: 1. Select any cell in the data column, the first Non-Empty cell or any cell after that. 2. Press Ctrl+Space. 3. Press Ctrl+Shift+Down-Arrow . 4. Press Ctrl+Shift+Up-Arrow. i find this way easier to use, since switching: Ctrl+Space Ctrl+Shift+Up-Arrow Ctrl+Dot Ctrl+Shift+Down-Arrow could be confusing In this video i learn Ctrl+Space, Ctrl+Dot, Thanks alot

[@amitsgvlog]

Nice

[@axox1990]

nice

[@VishwaManava_Sri]

Hi Leila, Thank you so much for sharing excel knowledge and insights, I am a great follower of all your videos, In fact i have purchased your courses in udmy. Request you to stay safe and healthy. take care. On a lighter note if you call those well organized bookshelf as mess I wonder what would be your expression if you ever happen to see my bookshelf. :)

[@LeilaGharani]

Many thanks for your support.

[@spilledgraphics]

CTRL + "." (period) can be powerful for other scenarios like expanding a range of cells in any direction : P Thanks for making a second video Leila !

[@LeilaGharani]

It's my new go-to shortcut now :)

[@YitzhakMoniz]

I use "Ctrl+shift" instead of "Ctrl+.", but I'll be sure to incorporate it in my workflow to.

[@poya.h5060]

Hi leila. first i would like to say your videos are really great and simple How can i generate numbers in formula bar? I need 1 to 7 and after some calculations choose smallest result(first use 1 if error use 2 and...... if more than one answer possible choose smallest one)

[@JonathanExcels]

As ExcelIsFun stated, it is a team effort. It seems Bob and I must have shared the same approach. As other's have already commented, Ctrl+. doesn't jump to the top or bottom. It cycles through the corners of your selected range. try selecting a rectangular range like A1:D9 and then use Ctrl+. and you will see what I mean. Interestingly, the macro recorder only records a select statement that changes each time you use the shortcut.

[@LeilaGharani]

Hi Jonathan - Yes :) Thank you for your input. Agree - Control . Is great for navigating a table of data by jumping to the corners.

[@jubileelifeinsurance5991]

MY BEST TEACHER MA'AM LEILA :)

[@alirezamogharabi8733]

Please record all videos in your home, That's so better 😊

[@LeilaGharani]

Thank you! I'll keep that in mind :)

[@adnanahmed8821]

Hi Leila. Use Alt+2 it will show all the previous activity u did in excel

[@rogersereinig864]

Attention: shortcuts Alt+1 to Alt+9 are user-based. You can configure this 9 shortcuts by yourself

[@bhairavpanchal8653]

Ctrl+Backspace also you can use instead Ctrl+.

[@johnborg5419]

Thanks Leila. This was helpful to me. On another note.....to all Office 365 insiders we have a new function LET(..... I already been experimenting with it and look great. Will we have videos on this??

[@LeilaGharani]

We definitely will John :)

[@abidjaavaid1764]

Hi leila, your videos are very helpful , rightnow i have a trouble , plz guide me through it, i have a data of 15 days of two same machines , now i want to high light the cell that contain production value which is less than the production of previous day production through conditional formating

[@GeertDelmulle]

Wait, at 4:26 did I get mentioned an a Leila Gharani video? OMG! Very sweet of you in these trying times of COVID-19. OTOH, it just might be cabin fever setting in... (;-o) Truth be told, exactly today in a very long Skype meeting I mentioned and referred your channel in the context of using symbols in tables instead of text or numbers. :-)

[@GeertDelmulle]

On the topic of COVID-19: All the people I know are doing their best to stay safe. Me too, I do my best to stay healthy, and sane, too. ;-) Watching Excel videos sure helps in all of this, too. Not to mention: it’s a lot of fun too. :-)

[@LeilaGharani]

Hi Geert. Yes. That was you! Thank you for your input and sharing the videos. Agree- Watching Excel videos is a good way of spending the extra time : )

[@mramsch]

Is there possibly a comparison of key shortcuts of the different language versions of Excel? For example Ctrl+. inserts the current date in German Excel. How do I find which key combination does the same for me as Ctrl+. does here in the video with English Excel?

[@m.vinoth1930]

I like ur vedios very much.iam from sri lanka .can i know how to copy and pasete a formula like a text in a cell