Friday evening I was meeting some friends for dinner. One of the worked at a law office on Seventh Avenue, so I met them at their office beforehand. They were performing some calculations on an Excel spreadsheet at the time, and as they were copying and pasting a repetitive formula, I showed them this trick which I am now sharing with you. This technique works with Excel 2003 and Excel 2007.
Sometimes you may want to use a formula or function repeatedly. In such a case you can easily copy the formula from one consecutive cell to another by using the fill handle of the active cell. The fill handle is the small square that appears in the lower right corner of an active cell.
To Copy A Formula With The Fill Handle
- Activate the cell that contains the formula you want to copy. (This typically done by clicking on the cell).
- Use the mouse to grab the fill handle. (Move the mouse close enough to the fill handle so that the mouse turns into a small black cross. When you see the small black cross, hold down the left mouse button.)
- While holding down the left mouse button, drag the mouse down for as many rows as you would like to copy to. Then release the left mouse button.
Fill Handle Usage
That’s it! The references used in the formula will be updated to match the new rows, so that the answers will be correct for the new rows.
Post Comments or Questions with the link below. Keep up-to-date with Skylarking: By Email or RSS Newsfeed or on Twitter. You can also send questions with my email form.
When I teach courses for using Microsoft Office applications such as Word, Excel, PowerPoint, and Publisher, I always provide my students with a handy sheet of tips, tricks, and keyboard shortcuts.
This handout, which I am providing here as a downloadable PDF is particularly popular in my Microsoft Word classes. Since Word is naturally a keyboard intensive program, people enjoy knowing that many of the tasks they often perform with a mouse can also be performed with the keyboard. Such shortcuts include boldface, italics, copy and paste, printing, and doublespacing of paragraphs.
Some useful tricks include creating a double underline, copyright symbols, and arrows, along with the Word calculator for performing calculations.
The tips will work with most versions of Microsoft Office up to and including Office 2007.
If you know of any other shortcuts or tricks, feel free to post them here as a comment. Want to know if there’s a shortcut for performing a particular task? Just post your question as a comment as well. I’ll be happy to answer your questions.
Right click and “Save As” to download or click to open: Microsoft Office Tips, Tricks, and Shortcuts (PDF)
Post Comments or Questions with the link below. Keep up-to-date with Skylarking: By Email or RSS Newsfeed or on Twitter. You can also send questions with my email form. I’m looking forward to hearing from you.
Babs, a guest reader from Long Island, NY emailed me yesterday morning:
A former student of yours in NYC told me to contact you. Sometimes when I print a one page letter on Word I get a blank second page too. Can you help?
After writing to Babs, I learned that she was an experienced typist who had spent as much time on typewriters as she had on word processors like Word.
In my teaching experience, I have seen many people who are accustomed to using typewriters —however long ago that may have been— have a habit of hitting the “Enter” key several times when they finish typing a letter. Typewriter users would often do this to eject the paper sheet from the typewriter.
On a word processor this often results in the cursor going onto a second page. A glance at the status bar in the lower left hand corner of the monitor will show the ‘typing cursor location’ and ‘page count’ (shown in picture at right). The message “Page 2 of 2” means that the cursor is on the second page of a two page document. Even though there is nothing typed on the second page the printer will load and eject a second blank page.
Okay, but how do I fix this?
You just need to move your typing cursor to the end of the document and tap the ‘Delete’ key several times until the cursor appears at the end of the last word in your document.
A quick way to get to the end of a document is to use CTRL + End. (Hold the ‘CTRL’ key with one hand, and tap the ‘End’ key with your other hand). The cursor will move to the end of the document.