Formula to Copy and Paste Values in Excel Automatically
Are you tired of manually copying and pasting values in Microsoft Excel? The good news is that there’s a formula you can use to automatically copy and paste values from one range to another in your Excel spreadsheet. In this article, we’ll explore how to use this formula step-by-step and cover some advanced techniques for working with dynamic ranges and 2D data sets.
Introduction
The formula to automatically copy and paste values in Excel is:
=INDEX(source_range, ROW(1:1), COLUMN(A:A))
Where:
source_range
is the range you want to copy values fromROW(1:1)
returns the row number of the cell where the formula is enteredCOLUMN(A:A)
returns the column number of the cell where the formula is entered
By combining the INDEX
, ROW
, and COLUMN
functions, this formula allows you to copy values from a source range and paste them into a destination range automatically, without any manual effort. Whether you’re working with a simple column of data or a complex 2D table, this formula can save you time and reduce errors.
Understanding The Formula to Copy and Paste Values in Excel Automatically
Let’s break down the components of this formula to understand how it works:
The INDEX Function
The INDEX
function is the cornerstone of this formula. It returns a value from a table based on the row and column number you specify. The syntax for INDEX
is:
INDEX(array, row_num, [column_num])
array
is the range of cells you want to retrieve a value fromrow_num
is the row number in the array from which to return a valuecolumn_num
is optional and specifies the column number in the array from which to return a value
In the copy-paste values formula, the array
argument is the source range you want to copy values from. The row_num
and column_num
arguments are provided by the ROW
and COLUMN
functions, which we’ll explore next.
The ROW Function
The ROW
function returns the row number of a reference. When used without an argument, like ROW()
, it returns the row number of the cell where the formula is entered.
In our formula, we use ROW(1:1)
. This returns the number 1, because row 1 is the first row in the 1:1
reference. As we’ll see later, using ROW(1:1)
instead of just ROW()
is crucial for making the formula copy correctly.
The COLUMN Function
The COLUMN
function is similar to ROW
, but returns the column number of a reference instead. COLUMN()
with no argument returns the column number of the cell containing the formula.
We use COLUMN(A:A)
in the copy-paste values formula, which always returns 1, since column A is the first column. Like ROW(1:1)
, this is important for the formula to work correctly when copied.
Putting it All Together
By combining INDEX
, ROW(1:1)
, and COLUMN(A:A)
, the complete formula looks like:
=INDEX(source_range, 1, 1)
This retrieves the value from row 1, column 1 of the source_range
. But the real magic happens when you copy this formula to other cells.
Relative cell references are the key. When you copy the formula to a new cell, Excel automatically adjusts the ROW(1:1)
and COLUMN(A:A)
values relative to the new cell’s position.
So each copied formula ends up like:
=INDEX(source_range, 2, 1)
=INDEX(source_range, 3, 1)
And so on, retrieving values from the corresponding row and column of source_range
. This allows the formula to fill in an entire range with values copied from the source, without you having to adjust anything manually.
Example: Copy a Column of Values
Let’s walk through a practical example to see how you can use the formula to automatically copy a column of values in Excel.
Suppose you have a list of fruit names in column A that you want to duplicate in column B:
A | B | |
---|---|---|
1 | Apple | |
2 | Banana | |
3 | Cherry |
Here’s how to use the copy-paste values formula:
Step 1: Enter the Formula
In cell B1, enter the formula:
=INDEX($A$1:$A$3, ROW(1:1), COLUMN(A:A))
The $
signs in $A$1:$A$3
create an absolute cell reference. This tells Excel to always refer to the range A1:A3, even if the formula is copied to other cells.
Step 2: Copy the Formula Down
Select cell B1 and double-click the small square in the bottom-right corner, known as the fill handle. This copies the formula down column B until it encounters a blank cell in column A.
Alternatively, you can drag the formula down to the last row you want to fill or copy and paste it into the desired range.
A | B | |
---|---|---|
1 | Apple | =INDEX($A$1:$A$3, ROW(1:1), COLUMN(A:A)) |
2 | Banana | =INDEX($A$1:$A$3, ROW(2:2), COLUMN(A:A)) |
3 | Cherry | =INDEX($A$1:$A$3, ROW(3:3), COLUMN(A:A)) |
Notice how the ROW(1:1)
part automatically updated to ROW(2:2)
and ROW(3:3)
when copied down, while the $A$1:$A$3
range reference stayed constant. This is how the formula fills in the correct values from column A.
Step 3: Convert Formulas to Values
At this point, column B is filled with formulas that fetch the corresponding values from column A. If you want to replace the formulas with static values:
- Select the range with the formulas (B1:B3 in this example)
- Copy the selected cells (Ctrl+C or right-click > Copy)
- Right-click the selection and choose Paste Special > Values
This pastes just the values returned by the formulas, converting the cells to regular data.
A | B | |
---|---|---|
1 | Apple | Apple |
2 | Banana | Banana |
3 | Cherry | Cherry |
Copying a 2D Range
The copy-paste values formula works equally well for 2D ranges, like tables. To copy values from a 2D range such as A1:C3, adjust the formula to include both the ROW and COLUMN arguments:
=INDEX(A1:C3, ROW(1:1), COLUMN(A:A))
Then, fill the formula down and across to populate the destination range. The ROW(1:1)
and COLUMN(A:A)
functions ensure the correct row and column are fetched from the 2D source range as the formula is copied.
Dynamic Ranges
In some cases, your data range may change in size over time. To make the copy-paste formula flexible, you can reference a dynamic named range instead of a fixed range like A1:A3
:
- Use the
OFFSET
function to define a range starting at a fixed cell and extending based on a logical condition - Create an Excel Table (Insert > Table) from your data and reference the table name in your formula
- Use the
INDIRECT
function to build the range reference from cell values or named ranges
By referencing a dynamic range, your copy-paste values formula will automatically adjust to changing data sizes without modification.
Final Thoughts
The =INDEX(source_range, ROW(1:1), COLUMN(A:A))
formula offers an efficient method to automatically copy and paste values in Excel from one range to another. By leveraging the power of the INDEX
, ROW
, and COLUMN
functions in combination with relative and absolute cell references, you can quickly duplicate values from any size range or table.
Whether you’re working with a simple column of data or a complex matrix, understanding how to use this formula will help you work faster and more accurately in Excel. The next time you find yourself reaching for the copy and paste shortcuts, try this formula instead and experience the benefits of automating your workflow!
FAQs
What is the formula to automatically copy and paste values in Excel?
The formula to automatically copy and paste values in Excel is: =INDEX(source_range, ROW(1:1), COLUMN(A:A))
, where source_range
is the range you want to copy values from.
How do I copy the formula down to fill a column with values?
To copy the formula down a column, select the cell containing the formula and double-click the fill handle (the small square in the bottom-right corner of the cell). Alternatively, drag the formula down or copy and paste it into the desired range.
How do I convert the copied formulas to static values?
To convert the copied formulas to static values, select the range with the formulas, copy the selected cells (Ctrl+C or right-click > Copy), then right-click the selection and choose “Paste Special > Values”.
Can this formula copy values from a 2D range or table?
Yes, to copy values from a 2D range like A1:C3, adjust the formula to include both the ROW and COLUMN arguments: =INDEX(A1:C3, ROW(1:1), COLUMN(A:A))
. Fill the formula down and across to populate the destination range.
How can I make the formula work with dynamic ranges that change in size?
To accommodate dynamic ranges, use the OFFSET function to define a range starting at a fixed cell and extending based on a condition, create an Excel Table and reference the table name, or use the INDIRECT function to build the range reference from cell values or named ranges.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.