Copy hundreds of rows of Excel formulas in three steps
Thursday, August 20, 2009 12:01 PM
Copying formulas in Excel is relatively simple, unless you need to copy down through dozens or even hundreds of cells. Here's a foolproof trick to get it done.
Microsoft Excel
Copy hundreds of rows of Excel formulas in three steps
Excel provides a number of ways to copy formulas.
You can use Excel's Auto Fill feature or, as an alternative, select the cells you want to copy to, enter the formula in the active cell, and then press [Ctrl][Enter]. These methods are fine for copying formulas down a column of cells--unless the column extends down hundreds of rows.
For example, say Column A in your worksheet lists 100 products and Column B lists the wholesale price of each. You would like to enter a formula that calculates the retail price for each product and displays the results in Column C. Selecting 100 cells is a time-consuming and error-prone process, but you can copy the formula down Column C without having to select the cells.
Follow these steps for foolproof cell copying:
- Click in C2 and enter =B2*300%
- Press [Ctrl][Enter].
- Double-click the fill handle (the small black square in the lower-right corner of the cell).
Excel will stop copying the formula when it reaches a row with a blank cell in Column B. To ensure that you copied the formula correctly, click in C1 and then press [Ctrl][Shift][End].
Microsoft Word
Create a custom number format in Word
In Word, you can quickly number a list by selecting the items and clicking the Numbering button on Word's Formatting toolbar. Word even offers a selection of predefined formats that are adequate for most uses. However, you're not stuck with just the predefined formats.
For example, you might want to print an underscore in front of a numbered question. You see these types of lines on tests, surveys, and questionnaires.
Word doesn't offer a predefined format for that type of list, but you can create it yourself. First, begin as you would any other list by typing the items.
Once you have your list, you're ready to create and apply the custom formatting as follows:
- Select all the items in the list.
- From the Format menu, choose Bullets And Numbers.
- In the resulting dialog box, click the Numbered tab.
- Select a numbering option that you seldom or never use, because you're going to modify it.
- Click Customize.
- In the Customize Numbered List dialog box, click before the number in the Number Format control.
- Type several underscore characters and a space.
- Click OK and Word applies the custom numbering scheme to the selected items.
You can rearrange the list and even insert new items; Word will automatically adjust the numbering scheme to accommodate your changes.
Microsoft Access
Redocking the Immediate window in Access
The Visual Basic Editor docks the current module and the Immediate window in a center window. It's a nice arrangement that gives just enough real estate to both windows.
Recently, I lost that configuration and I couldn't get it back. I was constantly restoring, resizing, and rearranging the module and the Immediate window. It was terribly annoying.
Access developer Paul Hartland finally came through with the right trick:
- Right-click the Immediate window and make sure Dockable is checked.
- Double-click the Immediate window's title bar to maximize it. Doing so will redock the Immediate window to the bottom border.
- If the Immediate window is too large, simply reduce it by pulling down the window's top margin a bit. As you do, the VBE will also enlarge the size of the open module window. If no module is open, the VBE will adjust the next module to fit in the allotted space above the Immediate window.
With the Immediate window redocked, it and the module windows fit nicely together.
There are currently no comments for this post.