How to Copy Data From One Sheet to Another in Excel Using Formula

You’ve set up a flawless, stylish, well-organized spreadsheet. It’s reader-friendly, easy to use, and clearly laid out. There’s only one problem: You need to put all of that data into a different sheet. Short of typing everything all over again, do you know how to copy data from one sheet to another in Excel using formula?

There are multiple ways to take the contents of one spreadsheet and use them somewhere else. While it isn’t always intuitive, it’s straightforward to pick up if you follow the step-by-step guide below. You’ll be able to transfer data from one Excel worksheet to another automatically, populate cells based on different criteria, and more.

Select all the data in the worksheet

To start, open the worksheets you want to work with. Begin by selecting all of the data that you want to move or copy.

1. Use Keyboard shortcuts

If you’re working quickly, keyboard shortcuts can be faster and less disruptive than mouse clicks. Try:

CTRL + Spacebar

Pressing CTRL and space together is a shortcut for selecting an entire column. Click on a cell, use this keyboard shortcut, and the cell’s whole column will be selected.

Shift + Spacebar

Shift and space work like CTRL and space, but for rows. Click on a cell, press shift and space, and the cell’s entire row will be selected.

2. Copy all the data

Once you’ve selected all of the data you need to transfer, you can copy it.

CTRL + C

CTRL and C is the shortcut for copying a selection. Leave the data you want to be selected, press CTRL and C, and it’ll be copied to your clipboard.

3. Check the plus sign to add a new blank worksheet

Assuming you don’t already have a worksheet you want to copy your data to, you can create a new one now. At the bottom of your window, you should see tabs with the names of each of your worksheets. At the end of these, there’s a plus sign. Click on this to add a new blank worksheet to your workbook.

4. Click the first cell in the new sheet

Go to the new sheet, and click on the first cell. All you need to do here is select one cell — don’t worry if it isn’t enough to hold everything you’ve copied.

5. CTRL + V to paste the data

Press CTRL and V to paste your copied data. It’ll transfer data from one Excel worksheet to another automatically, and take up as many rows or columns as it needs to fit.

How to copy data from one cell to another in excel without formulas

Say you have a sales worksheet that uses Excel formulas to calculate totals. You want to copy the data from a cell, but don’t want it to copy the formula itself. How do you transfer specific data from one worksheet to another for reports, without including the formulas from your first sheet?

1. Select the cells or ranges to be copied

Just like the process above, start by selecting the cells (or ranges of cells) that you need to copy data from.

2. Go to the Home tab

Go to the Home tab in your menu. It’ll offer you several options, but we only need to focus on one right now.

3. Select Copy in the Clipboard Section

Click “Copy.” You can also use the CTRL + C shortcut mentioned above.

4. Select the cell to paste to

Next, click the cell that you want to transfer the data to.

5. Select Paste button

Don’t click on the Paste button itself — look at the arrow just below it. You want to click on that, so it produces a dropdown menu.

6. click on Values

In the Paste menu, choose “Values.“

7. Select OK

Click “OK.” This will paste only the values of the cells you copied, not the formulas.

How to copy data from one cell to another in excel using a formula

Imagine you have a sales worksheet above. You want to take your total sales figures, and refer to them again elsewhere in the worksheet. If you type the number in by hand, you’ll have to re-type it again every time it changes. Fortunately, this isn’t necessary. A very simple formula can help you automatically transfer data from one cell to another. Here’s how to copy data from one cell to another in Excel using formula:

Select the target cell

Your target cell is the cell in which you want the data to appear. Click on it to select it.

Enter a = sign and the number of the source cell

Luckily, even if you have no idea how to copy data from one sheet to another in Excel using formula, the process is simple. With your target cell selected, type in an equals sign (=) and the name of the source cell. The source cell is the one you are copying from. The equals sign tells the cell that its contents should be equal to the contents of the target cell. That’s all you need!

Transfer data from one worksheet to another for reports

What if you need to learn how to copy data from one sheet to another in Excel using formula in order to generate reports? This might sound complicated, but it’s really very simple.

1. Locate the data you want to transfer

Open the worksheet that contains the data you need to transfer. Note the name of the worksheet, and the specific cell.

2. Go to the new worksheet

Next, go to the new worksheet. Locate your target cell.

=nameofspreadsheet!cell

Click on your target cell. Inside, type the above formula. Substitute “nameofspreadsheet” for the name of your original worksheet, and “cell” with the target cell to transfer specific data from one worksheet to another for reports. For example, this formula may look like =Sheet3!A1 when you’re finished. Then, the cell from A1 in Sheet 3 should automatically populate to the new cell.

How to copy data from one cell to another in Excel automatically according to cell value

Say you have a large inventory spreadsheet and want to create an easy way to navigate it. You could try organizing it alphabetically or numerically and scroll through to find what you need, but there are multiple ways to make your spreadsheet do the work for you. For this purpose, we’ll take a look at VLOOKUP.

This can help you figure out how to copy data from one sheet to another in Excel using formula, in situations where you want to use a specific cell value as your criteria.

Using VLOOKUP

This is a bit more complex than the other steps in this guide but is still very doable. VLOOKUP is used to find a value in the leftmost column of a table, then give a value from another column in that row. It’s used for a lot of things, but here it can help us figure out how to copy data from one cell to another in Excel automatically based on the value of the cell.

VLOOKUP syntax

To get VLOOKUP to work, you need to type it into a cell in a specific way. The proper syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).

lookup_value

The lookup_value criteria is the cell you’re going to use to populate the others in the table. Using the example of an inventory spreadsheet, you might use this cell to type in a product name or UPC that you need to know the price or inventory of.

table_array

The table_array criteria is the range of the table that you’re pulling data from. Start from the upper left corner, and go to the bottom right. As an example, this might look like $A$1:I$9$.

col_index_num

The next, col_index_num, is the number of the column in the table you want to transfer data from. This can’t be zero or negative.

[range_lookup]

The last one, [range_lookup], is optional. Use this if you want to specify if you want an exact or partial match for the lookup_value. If you leave it out, it will default to accepting partial matches.

Putting it all together

Imagine your inventory spreadsheet encompasses cells A1 to I9. Column A holds all of the products’ UPCs. Column H, the eighth column in the table, holds their prices. If we want an easy way to find the price of anything in the whole spreadsheet, without having to look it up by hand, we could go to an empty cell and use VLOOKUP.

Pick cell A10. This is going to be the cell you’ll use for our data. In the cell right next to it, B10, you could type

=VLOOKUP($A$10,$A$1:$I$9,8,1).

Now, whenever you type a UPC into cell A10, VLOOKUP will automatically populate B10 with the price of the item based on that value.

Transferring data from another sheet based on cell value

That’s all well and good if all of your data is in one worksheet, but how can Excel populate data from another sheet based on cell value? For that, you just need to make one small modification to VLOOKUP to include the name of the worksheet you’re pulling from.

Instead of the VLOOKUP syntax mentioned above, you need to type:

=VLOOKUP(lookup_value,nameofspreadsheet!table_array,col_index_num,[range_lookup]).

Using the inventory worksheet example above, it might look like:

=VLOOKUP($A$10,sheet1!$A$1:$I$9,8,1).

This will automatically search for the data you want in worksheet 1, cells A1 through I9, based on the value typed in to cell A10. Give it a try!

Whether you need to know how to copy data from one sheet to another in Excel using formula, or want to populate a new worksheet with data based on the values of another one, Excel has a way to do it. Using the equals sign is a simple, one-step formula for transferring data from one cell to another, while formulas like VLOOKUP can help you transfer data in more advanced ways. Set up practice worksheets to determine which formulas and shortcuts are best suited to your needs, and you’ll be an expert in no time.