15+ Uses of Excel Name Box You MUST Know

Not many know the capabilities of Excel name box. Yeah I know many might not even know which box I am referring to. It is the same box at the left above worksheet area below ribbon that usually has the reference to active cell and in my early days I too thought that Excel name box is just there to give reference to active cell so that you don’t have to run your eyeballs along the rows and columns of active cell to know its address. But today we are going to learn some nifty tricks this almost nothing box can pull off.

For all this, I would like to convey my regards to Mr. Shane Devenshire whose post actually made me compile these tips with action illustrations.

Tip# 1: Jump to a specific cell in no time

In the name box if you type H7 and press enter you go to H7. If you type S6 it will go to S6.

excel name box 1

Tip # 2: Select and navigate to specific range

For this, you just need to know the first (top left) and last cell (bottom right) of the range.

If you type A1:D10 in the Excel name box and press enter, all the cells enclosed in cell A1 through D10 will get selected. Even if you were initially somewhere else on the excel, you will be snapped back to the selected region to have it in front of you.

excel name box 2

Tip # 3: Select a range from active cell using Excel Name Box

If you have an active cell B1 and you type C10 into the name box, press and hold down Shift key on the keyboard and then press enter key you will select the range B1 to C10

excel name box 3

Tip # 4: Select cells in addition to active cell

If you are in B1 and you type B10 into the name box, press and hold down the Ctrl key and press enter you will select only two cells i.e. B1 and B10. Remember SHIFT key will select the range whereas CTRL key will select only the cell you mentioned in the name box. Check previous tip to make sure.

excel name box 4

Tip # 5: Select multiple specific cells with Excel Name Box

If you type B1,H6,G10 into the name box and press enter you select A1,C5,E10. Pay attention to the use of Commas with no spaces.

excel name box 5

Tip # 6: Select multiple specific ranges

If you type A1:B10,D1:E10 in the name box and press enter key you will select two ranges separately i.e. A1 through B10 and D1 through E10. Pay attention to placement of colon and comma.

excel name box 6

Tip # 7: Select entire column(s)

In Excel name box if you type D:D and press enter you will select entire column D. Similarly if you type D:E it will select entirely both column D and E.

excel name box 7

Tip # 8: Select entire row(s) easily with name box

If you type 2:2 and press enter it will select entire row 2. If you type 3:5 and press enter you will select rows 3 through 5. Remember alphabets are for columns and numbers are for rows. Same rules apply for the name box.

excel name box 8

Tip # 9: Select multiple specific rows entirely

In the name box if you type 3:7,9:11 and press enter you will select row 3 through 7 skipping row 8 and then selecting row 9 through 11.

excel name box 9

Tip # 10: Select multiple specific column(s) and row(s) entirely and together

If you type H:H,6:6 and press enter you will select both entire column H and entire row 6. Similarly if you type B:C,6:7 then it will select entire column B and C with entire row 6 and 7.

excel name box 10

Tip # 11: Select only where specific column(s) and row(s) intersect

If you type A:D 3:8 and press enter you will select the intersection i.e. range enclosed inside cell A3 through D8. Pay attention to the space between D and 3. And also to know the area of intersection simply take first column of the range you mentioned with first row of the range you mentioned that gives you first cell of the range i.e. A3. now you can workout the other cell of the range 🙂

excel name box 11

Tip # 12: Select entire worksheet

If you type A:XFD and press enter you will select the entire spreadsheet.

excel name box 12

Tip # 13: Add additional ranges to selection

If you have A1:C5 already selected in the worksheet and you type E1:G10 in the name box, hold down the Ctrl key and press enter you will get two ranges selected i.e. A1 through C5 and E1 through G10. Don’t forget to experiment with SHIFT key 🙂

excel name box 13

Tip # 14: Select an entire column of active cell

If you type C into the name box and press enter you will select the whole column in which you have the active cell.

excel name box 14

Tip # 15: Select entire row of active cell

If you type R into the name box and press enter you will select the whole row in which you have the active cell.

excel name box 15

Tip # 16: Navigate back to active cell collapsing the selection

If you have a range selected and you type RC in the name box and press enter the selection will collapse to the active cell i.e. you will be navigated back to the active cell. Remember it is RC not CR.

excel name box 16

Tip # 17: Give a name to a selected range

Simply select the range, type the name in the name box and press Enter key on the keyboard. Now you can use this name in formulas mean that range instead of mentioning the range itself and this makes things much easier to understand and work.

Also Once you name the range, you can select that range again by simply selecting it from the name box. But remember the name cannot have spaces in it.

excel name box 17

Loved it? Pin it!

3 COMMENTS

  1. do you offer any on line programming courses, and how much are they ?

  2. Kanhaiyalal Newaskar. Kanhaiyalal Newaskar.

    Sir, 26th September,2019.
    Very Clearly and wisely you have given Notes.
    I must appreciate your efforts.
    I hope to receive more such interesting notes in future too.
    Once again thanking you.
    Kanhaiyalal Newaskar.
    AHMEDNAGAR (MAHARASHTRA)
    India.

  3. Working with Excel and teaching Excel since 1991 and programming Office applications using VBA for about 20 years, I didn’t pay much attention to thename box. I only used it with a pure celladdress or to work with given names. I haven’t known, that there were so many more additional possibilities – I didn’t even check for. Of course, some items can be solved more easily using the mouse by clicking in therow/column headers or using during selection the ctrl or shift key, but when the ranges are much wider spread or not even visible, these tips will be very very helpful.

Comments are closed.