Right-drag Border To Access More Copying Options

April 19, 2021 - by Bill Jelen

Challenge: You once stumbled upon a handy menu for accessing extra copying options. But how did you open it, and what can it do for you?

Solution: Excel has an incredibly useful but obscure shortcut menu, as shown in Figure 71. To open it, you select a cell or a range of cells. Then you right-click and drag the border of the range to a new location. When you release the right mouse button, Excel opens this menu, which has the following options, among others:

Figure 71. This menu offers faster ways for Paste Values, Format Painter, and more. How do you find the menu?

Copy Here as Values Only: Using Copy Here as Values Only is an incredibly fast way to convert a range of formulas to values. A good method is to select a range of formulas, right-click, drag right, drag back to the original location, let go of the right mouse button, and choose Copy Here as Values Only.
Copy Here As Formats Only: You can copy numeric formatting, borders, and more by using Copy Here As Formats Only. For example, you can copy column widths by selecting a range of entire columns, such as A:C. Then you right-click and drag the border to E:G. When you let go of the mouse button, if you choose Copy Here As Formats Only, Excel changes the column widths of E:G to match those of A:C.
Link Here: For a faster way to set up formulas to point to a range, you can select A1:A10, right-click and drag the border to C5, and select Link Here. Cell C5 now contains the formula =A1, and cell C14 contains the formula =A10.
Create Hyperlink Here: This is a cool option but is rather difficult to use, and it does not work in an unsaved file. For details on how to use it, see “Quickly Create a Hyperlink Menu.”

Gotcha: When you click on the border of a selection, do not click on the square dot in the lower-right corner of the cell. This dot is the fill handle, and clicking it invokes the AutoFill options instead of this menu.

Summary: By right-clicking and dragging the border of a selection, you can get quick access to several options.

Title Photo: Alexander Schimmeck on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.