Software

Quick Tip: Use this mouse trick to edit Excel formulas

Instead of manually typing pieces of a cell reference, use this quick mouse trick to extend the reference when modifying a formula.

You probably know how to edit a formula - press [F2] and make changes. If you're like most users, you press [F2] or click inside the formula bar and then use the keyboard to change cell references. For example, to include values for Canada in the sheet below, you'd press [F2] or click the formula bar and then highlight the 6 in the B6 reference and type 7.

When editing a formula, you might have noticed that Excel outlines the referenced range. You can extend that range to update the formula's reference. Hover the mouse over one of the corner handles until Excel displays the double-arrow pointer. Click the handle and extend it to include the appropriate cell(s). Doing so automatically adjusts the respective reference in your formula. Press Enter to enter the modified formula.

You can move the reference instead of extending it by hovering over a border instead of a corner handle. Excel will display the four-arrow pointer, which you can then use to move the border.

Extending the range is certainly easier than trying to highlight characters one at a time.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

11 comments
bconsult
bconsult

Come on guys.. yes, you can use the combination of using the mouse and keyboard to accomplish the same thing when you are editing cells in Excel. (and many other programs for that matter.) I think Susan must have been trying to help out MAC users.

kncuda
kncuda

Click once on the formula in the formula bar, drag the range with the mouse, and click on the check mark next to the formula bar. No keys necessary, just 3 clicks. Or you could save the last click by a quick thumb poke at the enter key on a full size keyboard keypad. Marvellous how Microsoft programmed in several ways to do things so we could pick a method that works best for ourselves!

cliffbeacham
cliffbeacham

I agree with muntz! A mouse is a 1 handed tool. The keyboard allows 2 hands BUT the best is a combination of the Keyboard with the left hand the mouse in the right hand. You are shooting with 2 guns instead of 1. It is an almost natural progression - people start with the kb, progress to the mouse and then, when they get realy proficient, start to use the above. Too many people seem to stop at the mouse stage and become mouse-dependant.

john_sullivan
john_sullivan

Like several of you, constantly having to switch between mouse and KB drives me crazy and is a big waste of time and motion. I generally prefer the KB and dislike having to move the mouse.

Nebraska Jack
Nebraska Jack

If you are using the mouse, just double click in the cell to edit the formula. No need to go to the keyboard to press F2. If you are using the keyboard, press F2 to edit - no need to go to the mouse.

Greg_D
Greg_D

Double click the formula cell and drag and all done with one hand

awgiedawgie
awgiedawgie

If you use the mouse to start editing the formula, then this may be a shortcut. However, if you start editing by pressing F2, then move over to the mouse to make the change, and then back over to the keyboard to press to finalise the change, then the mouse edit is wasted motion. No, it's not much wasted motion, but throughout the day, all those little wasted motions add up to wasted time.

addween
addween

Wonderful. Share a website with you , put this url in google sirch ( http://www.chic-goods.com/ ) Believe you will love it. We accept any form of payment.

dhays
dhays

I have one spreadsheet that counts the number of blanks in a column, so whenever something is added, adding more blanks, then the range has to be modified. I usually click the formula, cell, then go to the formula bar, go to the range box highlight, drag the range to include the new row(s), and then hit enter, or even change the numbers whichever seems to be the best option. I never noticed the checkmark on the formula bar before. I didn't know of the F2 option either, never have used it, but my keyboard has the Fx keys disabled (has an F lock key) by default, so I don't use them much, except in Word (F3 change case--I don't know of an option in Excel that is similar) and not very often there either. Never have learned all of the options for them, so as I said, I don't use them very often.

Muntz
Muntz

You use two hands. I generally keep my left hand staionary over the keyboard, and my right hand on the mouse. I can easily press F2 with my left hand and follow up in a coordinated manner with the mouse.

Mark.Mathews
Mark.Mathews

Leave a blank row above your formula, and include this row in the formula. When you need to insert rows, insert in this row. That will automatically extend the range for you. You can eliminate the problem of having an extra blank cell by appending your formula with "-1" or you can enter something in the extra row, like a space or a "." or whatever.

Editor's Picks