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 from
• ROW(1:1) returns the row number of the cell where the formula is entered
• COLUMN(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 from
• row_num is the row number in the array from which to return a value
• column_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:

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.

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:

1. Select the range with the formulas (B1:B3 in this example)
2. Copy the selected cells (Ctrl+C or right-click > Copy)
3. Right-click the selection and choose Paste Special > Values

This pastes just the values returned by the formulas, converting the cells to regular data.

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

Vaishvi Desai

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.