Software

Make your life easier with this often-unnoticed Excel spreadsheet button

Spreadsheets are loaded with options that only experienced users know about. Schoun Regan shows you a button option that will save time and make your life easier.

I like toolbars with buttons adorned with small images on them that allow users to change the way an application works or the way the items in a file open. Of the myriad button options in spreadsheet applications, I'd like to focus on the Select Current Region button. This small, often-unnoticed button has the potential to save you time, make your life easier, and reduce your stress (really!).

Too much work
People use spreadsheets to compile sets of data, such as names, numbers, Social Security numbers for human resources info, addresses, and so on. These data sets are often several columns long and thousands of rows deep. Imagine, for example, that you wanted to make a formatting change to the entire data set. Or, alternatively, that you needed to make a cell reference change. How would you select the entire data set? A cell range name is one solution. But what if the data set continually grows? Would you click in cell A1, scroll down to the last row containing data, scroll right to the last column, then hold shift and click in the lower right cell containing data in that set? Whew! That's WAY too much work for Mr. Lazy here.

I've got a secret
Here's the secret to share with your students. In Excel, click on the View menu, select Toolbars, and then Customize. Then click on the Commands tab and select Edit under Categories. Under the commands, scroll down until you see the Select Current Region button. It looks like a small black cell with arrows in all-diagonal directions, as shown in Figure A (screen shot taken from a Macintosh Computer). Click on that button, hold, and drag it to your Standard Toolbar at the top of Excel. Then click Close on the Customize dialog box.

Figure A
Screen shot from Macintosh.


But what does it do?
Now, this little button does something that you're going to love. Pick any cell in a data set or range and then click on this button. I have chosen a data set that goes on for 1,500 rows and 60 columns. You can't see them all, but trust me. I've selected cell B11 and then clicked on the Select Current Region button. This button looks in all directions for adjacent cells containing data. It then adds those cells to the selection, as seen in Figure B. Everything in orange is now selected. Notice that the dates in cells A1:A4 are NOT selected. Now that thousand-row, multiple-column data set can be selected with just the click of a button.

Figure B
Screen shot from Macintosh.


Can it slice and dice too?
The uses for this tool are many. You can select any cell range with the touch of a button. Time saved. No more dragging and shift-clicking needed. I can't believe Microsoft refused to place the button up there by default. Oh wait, this is Microsoft, so yes I can.

A true believer in Macintosh computers, Schoun Regan is a consultant to training firms and travels across North America educating people for Complete Mac Seminars .

0 comments

Editor's Picks