How to Quickly Copy Formula In Excel
Copy a formula in several rows - of speed copying formula
In this Excel example tutorial I will show you how you can quickly copy the formula down to a few rows. Excel has the ability to drag-and-drop option for copying. That means pressing the left mouse button => hold the left mouse button => pull into place => drop or release the left mouse button.
When it comes to copy the formula that applies to the following. In Excel you will see a small box/square point on the joints of the right angle of the frame cells. After entering a formula, on cell position the cursor in the lower right corner of the frame cells (Fill Handle) until the cursor turns into a "little cross". When instead of "the arrow of the mouse pointer" appears "cross", press the left mouse button and hold it. Move (drag) the mouse pointer down to the intended row and release the left mouse button. Excel is automatically copied formula in all the rows.
NOTE! If you have multiple rows and the need to copy the formula across multiple windows, when copying a fast Excel to scrolling down to the last row in which you want to copy the formula.
For even faster copying formulas in hundreds of rows, see the instructions below this image.
Quickly copy formulas in a certain range of rows or cells (Name box option)
Another way to quickly copy formulas in a few thousand rows is called. "Name Box options". If you're an Excel spreadsheet add new data to the new rows range of cells and how we need to copy the formula for a certain range of newly-added thousands of rows, we can then do the following.
- Select the last formula in the cell (in this figure that the cells C16)
- In the Name box, type the range of rows in which you want to copy the formula (in the example in the image C16:C3000).
- Press the ENTER button (now you have selected all the cells in the specified range cells. Situation2 the picture below)
- Press the F2 key and after that press combination CTRL+ENTER key
Note! Also in this step you have another option, you can press CTRL+D (or some other combination and it depends on Windows/Excel setting preferences or local version of the language area. See Help and search shortcut to describe [Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below])
- Finally the formula is copied to several thousand rows down (Situation3 the picture below).
- Select the last formula in the cell (in the image that the cells C16)
- Press CTRL+G to open the GoTo window and Reference field, type the cell range C16:C3000 (picture below).
- Click the OK button (now you have selected all the cells in the specified range cells. Situation2 the picture below)
- Press F2 and then CTRL+ENTER and Excel will automatically copy the formulas in the range of rows/cells).
Automatic fast copying formulas in Excel table a few hundred rows
Another variant of the automatic rapid copying formulas in hundreds of rows down is to use the actual Excel spreadsheet (dynamic table)
When working with dynamic tables in Excel, automatically when you add a new row and the information in it Excel automatically copies the formula to the added rows.
Automatic copying formulas in a few thousand rows using VBA macro (Fill Down a Formula with VBA)
If you want to copy the formula once in several thousand rows then you use the VBA macro. In the example below there is a VBA macro that copies a selected cell that contains several thousand rows below.
- Select the cell containing the formula
- Start VBA macro (ALT+F11 => Run)
The result is copied formula down to the last filled rows or cells that contain the information on the left
' source excelsemipro
' Filldown a formula for in column of data.
' Assumes a data table with headings in the first row, the formula in the second row and is the active cell.
Dim rng As Range
Dim rngData As Range
Dim rngFormula As Range
Dim rowData As Long
Dim colData As Long
' Set the ranges
Set rng = ActiveCell 'cell with formula
Set rngData = rng.CurrentRegion
' Set the row and column variables
rowData = rngData.CurrentRegion.Rows.Count
colData = rng.Column
' Set the formula range and fill down the formula
Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
rngFormula.FillDown 'copy formula
How quickly copy the formula in the hundreds or thousands of rows by Fill Handle
If you need to copy the formula in the hundreds of thousands of rows then instead of the above-described fast way of copying formulas refer to the following instructions.
Take for example that in columns 'A' and 'B' have the data to be to add up. In the column 'C' enter the formula.
When you create a formula, position your mouse over the arrow on bottom right corner of the frame cells (Fill handle). Double click the left mouse button on the Fill handle. After the double-click the left mouse Excel is automatically copied the formula to the last row in relation to the data on the left column 'C'.
NOTE! If you in this case, the formula set in the column 'H' and did as instructed, Excel would not automatically copied the formula in column H ', because it is not physically in contact with the column' B '. So the ability to quickly copy formulas feasible only if the column in which the formula is touching the left or right side of the data. Therefore, it is desirable that the column containing the formula has the adjacent column, which contains the data.
Finally summary: Double click the left mouse button on the Fill handle and you quickly copied formula into several thousand rows.
How to extract text at the beginning of alphanumeric data
FLASH FILL in Excel
Enter text in the first cell to the right of the source data.
Press Alt + V + U
(or press Ctrl + E)
Excel will automatically populate all other cells to the last filled row.
Do the same for the numbers.