Today we are going to discuss about Name Box in Excel and its hidden capabilities. Lot to learn, let's dive in.
ALT + F3 is the shortcut key to get into Name box.
In general, we use name box as replacement of GOTO function in excel.
Consider, our active cell is C5 and in name box, type A5000 and press Enter.
Now our active cell is A5000.
However, there is much more that we can accomplish with the name box, and today our task is to explore its full potential.
How to select entire column when data contain blanks?
Here, our requirement is to select entire 'C' column in data. But it contains more blanks in between, so CTRL + SHIFT + Down can't bail us out. There are multiple other ways to do that, here is the simple trick in name box.
If we don't know the last row of data, select any one column without any blanks. In our case select B1 cell in 'Order Id' column and press CTRL + Down arrow. This will get you to B9995 cell. Now we have identified the last row of our data.
Select C1 cell and press Alt + F3 to reach name box,
type C9995 and use shortcut key Shift + Enter. You can see C1:C9995 is selected.
Can we select non-contiguous ranges?
Type the address of all ranges as shown below and press Enter.
It will select all ranges as shown below,
How to select row or column of Active cell?
Consider you are in the cell C5, press ALT + F3 and type R and press Enter.
Entire row of active cell is selected.
For column selection type C in name box and press Enter.
How to get back into Active cell?
You can see that; we did select a range, and our requirement is to get back into active cell which is the top left cell of our selection. For that, press ALT + F3 and type RC and press Enter. In that case, you will lose your selection.
Bonus Tip:
CTRL + BACKSPACE is the effective shortcut key, to get back into active cell and keep the selection.
How to select Entire Column or Row in sheet?
In name box type 4:4 and press Enter to select 4th row,
type E:E and press Enter to select entire E column.
How to create named ranges using name box?
Select the range A1:B16 and in name box type any name within naming conventions and press Enter. Do same steps for E1:F5 to name that range.
Using the dropdown in the name box now allows us to switch between ranges. This function will operate even when we are on a different sheet within the same workbook.
Comments