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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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 🙂
Tip # 12: Select entire worksheet
If you type A:XFD and press enter you will select the entire spreadsheet.
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 🙂
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.
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.
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.
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.
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.