MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Right-drag Border To Access More Copying Options


April 19, 2021 - by Bill Jelen

Right-drag Border To Access More Copying Options

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?
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.



Bill Jelen is the author / co-author of:

MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.