Forum Discussion
Solved
What's the shortcut to quickly select entire data in same column without header?
Hi,
Currently I am using XLOOKUP function where I need to pull the specific data from certain column. In the past, I would click on the first data cell and then press "Ctrl+Shift"Down Arrow" to quickly select all the data in the column (without header) however my dataset has a lot of blank spaces so this process is not efficient now.
Is there another alternative method shortcut?
Thanks,
Amy
- The best thing to do is use the Table references instead of the row-column references. It is both fast and more robust in case the size of the table changes. i did this in answering your other question. So in your example here it would be Table1[people_fully_vaccinated_per_hundred]
What is really nice is that as you type Table1 is will show in the pop up info window and you hit tab to accept it, then hit "[" and the whole list of options for that table will show up and as you type "p" it filters down to 2 options, hit the down arrow to select the one you want and hit tab to accept, and finally type "]" to finish the reference.
8 Replies
- You may find Ctrl+Space useful up to three times if you begin in a (pyamas) table cell.
(Shift+Space selects alike but for rows and may come handy before Ctrl++ and Ctrl+D to duplicate rows.)
Also Ctrl+Click may come a little handy with XLOOKUP;
Click a cell, type =XLO and type tab to get =XLOOKUP(
Click the cell with the lookup_value, Ctrl+click a cell in the lookup_array (Ctrl+click = splitted selection with a parameter delimiter in between) and then Ctrl+Space to select that column as parameter 2.
Finally one more Ctrl+click in the return_array and type Ctrl+Space to select that column.
You may also save a second by skipping the one closing parenthesis - since there is only one missing, Excel will guess correct and add it for you.bosinander Hi Bosinander, thank you!!! I am so happy to learn about the Ctrl+Space technique, that really makes it so much more efficient.
Thank you,
Amy
- You're welcome 🙂
You may also find combining shift+space with ctrl+space useful to select the whole pyamas table.
ie, pressing either of them expands the current selection sideways (SHift+space (think SHelf)) or COntrol+space to go for the COlumns.
As a variant of selecting the named column, you could use INDEX to address a column by its order. eg
XLOOKUP(A6, INDEX(Table1,0,3), INDEX(Table1,0,4)) to lookup in the third column and return from the fourth.
- The best thing to do is use the Table references instead of the row-column references. It is both fast and more robust in case the size of the table changes. i did this in answering your other question. So in your example here it would be Table1[people_fully_vaccinated_per_hundred]
What is really nice is that as you type Table1 is will show in the pop up info window and you hit tab to accept it, then hit "[" and the whole list of options for that table will show up and as you type "p" it filters down to 2 options, hit the down arrow to select the one you want and hit tab to accept, and finally type "]" to finish the reference.- Dear Mtarler,
Understood, thank you so much for your clear instructions, that makes sense and I will do that moving forward.
Kind regards,
Amy
