Excel: Copy Value/Formula

As a Salesforce admin, at a point in time, we may need to use Microsoft Excel (unless you use Google Spreadsheet). Here is the shortcut: assume you have an Excel spreadsheet with hundreds of thousands of rows, and you need to copy a value or formula.

Filtering the rows and double-clicking the fill handle will stop copying when it reaches a row that is not blank.

Sample data:

If we filter column A = Z2, type "y" in B3, then double-clicking the fill handle of cell B3 will stop at row 4, as row 5 is not blank.

So here are the correct steps:

1. Filter column A = Z2

2. In the Name Box (upper left), type B3:B1000 (assume you have 1000 rows), then press Enter

3. Enter Alt + ; to select visible cell only

4. Type "y" in the Formula Bar and hit Ctrl + Enter

5. Done; all visible rows from B3 - B1000 will be filled with "y"

blue arrow is the Name Box, and the green arrow is the Formula Bar

If you need to fill a formula instead of a static value, change the "y" in step (4) to a formula, such as: =A3&"y"

Reference:

• Excel: Copy Formula