Excellent video! Another tip on selecting the entire column in a table using Ctrl-Space is that if you select the first entry in the column (as you did in the video), Excel will shift the screen to the bottom of the column, like it does with Ctrl-Shift-Down Arrow. However, if you select any entry in the column besides the first entry, Excel will highlight the entire column but leave the screen alone.
What a great video Jon, your timing is was perfect and you have immediately solved some issues I was having and saved me a lot of time and effort thanks. I look forward to learning more.
Thank you, Jon, for that video! :) I will send it in my Excel, Polish newsletter, (20k + subscribers), so that even more people can learn these shortcuts :)
I use the “black arrow”-technique all the time. Furthermore, if the destination range is not an Excel Table, then you can still do the entire range in one formula by adding the entire lookup range in the first argument of XLOOKUP. The result is a dynamic array (or ‘vector’ as I like to call it).
Jon, thanks for your video I have a question, can Xlooku's lookup value in two column , which allow either column that matched the "target cell"? like "Or "in if function Eric
Hi Jon, nice to see one more video from you ! In a former one, I asked you if you knew a shortcut to merge and center. (I mean better than alt LN tab and down arrow many times ) ? Keep on doing great content ! Congrats from France.
I don’t believe there is a direct shortcuts for Merge & Center. One option is to add the button to your Quick Access Toolbar and then use an Alt+Number Key combination. Here’s a post with more info on setting up shortcuts for the QAT. www.excelcampus.com/keyboard-shortcuts/excel-quick-access-toolbar-qat-keyboard-shortcuts/ I hope that helps.
@@ExcelCampus thanks for the answer. Unfortunatly, I made a mistake : I forgot to add that the sortcuts would be for the "Center Across Selection" option, as merge and center is a bad idea. I don't expect a easy shortcuts as it's not something we have to do very often, but who knows ?
What video recording software is used in these Excel videos that I see on other channels as well, with zooming in and out as well as the animated orange boxes?
I love your tips for extending ranges in a formula. Thanks for that. But on the other hand i think that most of your examples try to fix errors in a bad data model. Whatever MS might say: Excel is perfect for calculations but it is NOT a proper database like SQL In a proper database you'd have a single table for companies and a field that indicates their state: prospect or customer. Or you could even say any entity with an order amount of 0.00 is a prospect. It's as simple as that from the point of a database designer... You give a good solution but for a problem that should not exist in the first place...
Hi Jomili. Great question! It sounds like you might be in cell Edit mode. You can hit F2 on the keyboard while editing a cell to toggle to Enter mode, then use the arrow keys to select cells. You also need to make sure you have the function name spelled correctly and have the open parenthesis. If you are on a version of Excel that doesn’t have XLOOKUP then it won’t recognize this as a function and the Enter/Edit modes won’t work. I hope that helps.
Hi! Im finding for someone who can thought me a formula on how will i set a unit price and amount in an specific item in a filtered row? Xan you help me? It so tiring to do this manually😰
@@Ali.Mostafa ahm its just a table of delivery items with an item decription, unit price and amount. And i need to update it as everyday as i could.Including the daily receiving of items. I do all of it manually. I just want to lessen my task to input a daily unit price to an item which is already existing in the table. Can you help me for this?
![Megan Thee Stallion - Mamushi (Remix) [feat. TWICE] [Official Audio]](http://i.ytimg.com/vi/5c0_u9PsrsQ/mqdefault.jpg)
Excellent video! Another tip on selecting the entire column in a table using Ctrl-Space is that if you select the first entry in the column (as you did in the video), Excel will shift the screen to the bottom of the column, like it does with Ctrl-Shift-Down Arrow. However, if you select any entry in the column besides the first entry, Excel will highlight the entire column but leave the screen alone.
Great tip! Thanks for sharing Steve! 👍
Excellent tricks, especially the Ctrl+Space one. Thank you Jon!
Great video-didnt know the CTRL space bar.
Really enjoyed this video series for Excel training,, Specifically, the excel files included for practice are really awesome. :). Thank You!
Thank you for this valuable post!
Such simple advice that will streamline my use even more. Thank you!
What a great video Jon, your timing is was perfect and you have immediately solved some issues I was having and saved me a lot of time and effort thanks. I look forward to learning more.
Great Tutorial And Super Cool Tips...Thank You Jon :)
Thanks so much Darryl! 🙂
Another great video! I always learn something new!
Thanks Angela! I’m happy to hear you’re learning from the videos and appreciate your support. 🙌
Excellent video! Thank you for sharing.
You're welcome, J! :)
Thank you, Jon, for that video! :)
I will send it in my Excel, Polish newsletter, (20k + subscribers), so that even more people can learn these shortcuts :)
Thanks Michal! I appreciate the nice feedback and your support! 🙏
Thanks.
Great tips.
Control space - who knew? Jon did of course! Thanks.
Haha thanks David! It’s probably one of my more frequently used shortcuts. Especially with tables.
Excellent video once again Jon. Thanks Paul
Thank you Paul! 🙂
I use the “black arrow”-technique all the time.
Furthermore, if the destination range is not an Excel Table, then you can still do the entire range in one formula by adding the entire lookup range in the first argument of XLOOKUP. The result is a dynamic array (or ‘vector’ as I like to call it).
Very cool! Thanks for sharing Geert! 👍
That's what I thought too while watching. Furthermore there would be no need to press F4 for the lookup and return values.
thank you so much
Really great tutorial, I was wondering is there a way to bring over notes linked to cells also with xlookup?
Is there any reason why you did not just select the entire column? I suppose that using =XLOOKUP(A2,E:E,F:F) could be more resource heavy?
I do that all the time, performance is acceptable
Jon, thanks for your video
I have a question, can Xlooku's lookup value in two column , which allow either column that matched the "target cell"? like "Or "in if function
Eric
Put the second lookup in the not found section of the xlookup
Hi Jon, nice to see one more video from you !
In a former one, I asked you if you knew a shortcut to merge and center. (I mean better than alt LN tab and down arrow many times ) ?
Keep on doing great content ! Congrats from France.
I don’t believe there is a direct shortcuts for Merge & Center. One option is to add the button to your Quick Access Toolbar and then use an Alt+Number Key combination.
Here’s a post with more info on setting up shortcuts for the QAT.
www.excelcampus.com/keyboard-shortcuts/excel-quick-access-toolbar-qat-keyboard-shortcuts/
I hope that helps.
@@ExcelCampus thanks for the answer.
Unfortunatly, I made a mistake : I forgot to add that the sortcuts would be for the "Center Across Selection" option, as merge and center is a bad idea.
I don't expect a easy shortcuts as it's not something we have to do very often, but who knows ?
Wow,
Thankyou
What video recording software is used in these Excel videos that I see on other channels as well, with zooming in and out as well as the animated orange boxes?
Any short cut key for drag a formula like a double click of mouse with same cell without going down side without table.
I love your tips for extending ranges in a formula. Thanks for that.
But on the other hand i think that most of your examples try to fix errors in a bad data model. Whatever MS might say: Excel is perfect for calculations but it is NOT a proper database like SQL
In a proper database you'd have a single table for companies and a field that indicates their state: prospect or customer. Or you could even say any entity with an order amount of 0.00 is a prospect.
It's as simple as that from the point of a database designer...
You give a good solution but for a problem that should not exist in the first place...
Lost me at the second tip. Hitting my left arrow moves me to the left side of the parenthesis in my formula. How do I left arrow over to A3?
Hi Jomili. Great question! It sounds like you might be in cell Edit mode. You can hit F2 on the keyboard while editing a cell to toggle to Enter mode, then use the arrow keys to select cells.
You also need to make sure you have the function name spelled correctly and have the open parenthesis.
If you are on a version of Excel that doesn’t have XLOOKUP then it won’t recognize this as a function and the Enter/Edit modes won’t work.
I hope that helps.
Hi! Im finding for someone who can thought me a formula on how will i set a unit price and amount in an specific item in a filtered row? Xan you help me? It so tiring to do this manually😰
Can you elaborate more?
@@Ali.Mostafa ahm its just a table of delivery items with an item decription, unit price and amount. And i need to update it as everyday as i could.Including the daily receiving of items. I do all of it manually.
I just want to lessen my task to input a daily unit price to an item which is already existing in the table. Can you help me for this?
Name ranges would be more useful in general.
ρɾσɱσʂɱ ✌️