# 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:

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.