Catalog

How to Copy Value from One Cell to Another in Excel Using Formula

October 16, 2023 1.2K views

Microsoft Excel is a comprehensive tool that can help people deal with complex formulas and record and track their data. Many people encounter errors when they want to copy a value from one cell to another in Excel using a formula. The question to answer is, how to copy the value from one cell to another in Excel using a formula? This guide will inform you of all the different ways to make this happen and recommend software for free.

How to Duplicate Value from One Cell to Another in Excel Using Formula?

To learn how to copy the value from one cell to another in Excel using the formula for Excel, follow the steps below:

Use “= (equal) key” (Suitable for beginners)

Step 1: Select the cell where you want the formula to be created. Press the “= (equal)” key on your keyboard, then click on the cell containing the necessary value. The procedure is built for you using a cell reference.

Selected cells in Microsoft Excel where one has an amount and the other has a formula

Selected cells in Microsoft Excel where one has an amount and the other has a formula

Step 2: Press the “Enter” key on your keyboard. Your formula will appear in the formula bar while the value will appear in the actual cell.

“Cell in Microsoft Excel with the highlighted cell having a formula identical to another cell”

“Cell in Microsoft Excel with the highlighted cell having a formula identical to another cell”

Step 3: Change the value in the original cell (e.g., D3) to update the value in the formula.

“New amount in the cell in Microsoft Excel with formula in G3 being identical to D3”

“New amount in the cell in Microsoft Excel with formula in G3 being identical to D3”

Using this method is quite simple, especially for beginners. Using a formula simplifies learning how to copy the value from one cell to another in Excel. It breaks down how to make this happen in the simplest form without the hassle of additional complex steps.

Use “INDEX-MATCH Functions” (Suitable for master)

Step 1: First, insert this formula in cell block “F5” to copy the value of cell block “B5”, then press “Enter.”

=INDEX(B5,MATCH(B5,B5,0))

“Entering a formula in a cell block in Microsoft Excel to copy the value of the original cell”

“Entering a formula in a cell block in Microsoft Excel to copy the value of the original cell”

This image can be found here

Step 2: Apply the same in cell block “G5” to change the cell reference to “C5”.

=INDEX(C5,MATCH(C5,C5,0))=INDEX(D5,MATCH(D5,D5,0))

“Formula in another cell block in Microsoft Excel to copy the value of the original cell”

“Formula in another cell block in Microsoft Excel to copy the value of the original cell”

This image can be found here

Also, use the formula in “H5” to change the cell reference to “D5”.

 “Formula in the Insert function bar showing the formula present for the selected cell to produce its particular value.”

“Formula in the Insert function bar showing the formula present for the selected cell to produce its particular value.”

This image can be found here

Note: In this formula, the “INDEX-MATCH” functions work as a dynamic array to look for the specific value horizontally and vertically. Along with it, type “0” for an exact match.

Step 3: Select cell range F5:H5 and use the AutoFill tool to get this final output.

AutoFill function with the formula applied in all cells

AutoFill function with the formula applied in all cells

This image can be found here

Using this method is quite challenging and time-consuming, especially for a beginner. However, this method will be a breeze if you are an expert in Microsoft Excel. Users can use the formula to simplify the entire process if they are more familiar with Excel, its functions, and formulas.

WPS Office: Free Editing Software for Excel, Word, and PowerPoint

WPS Office is a comprehensive productivity suite that boasts a range of tools to assist users in their everyday lives, personally and professionally. Using WPS Office is a smooth experience. The software is free and comes with a variety of tools that you can use even though they offer a paid version.

One of the great things about WPS Office is its user-friendly interface. It makes the process seamless. Another benefit to customers is that it’s highly compatible with Microsoft Office. That means you can easily make the switch without learning something new due to their similar interface.

How do you copy the value of one cell to another in Excel using a formula?

To learn how to copy the value from one cell to another in Excel using formula in WPS Spreadsheet, use the following steps:

Step 1: Select the cell where you want the formula to be created. Press the “= (equal)” key on your keyboard, then click on the cell containing the necessary value. The procedure is built for you using a cell reference.

Selected cells in WPS Spreadsheet where one has an amount and the other has a formula”

Selected cells in WPS Spreadsheet where one has an amount and the other has a formula”

Step 2: Press the “Enter” key on your keyboard. Your formula will appear in the formula bar while the value will appear in the actual cell.

“Cell in WPS Spreadsheet with the highlighted cell having a formula identical to another cell”

“Cell in WPS Spreadsheet with the highlighted cell having a formula identical to another cell”

Step 3: Change the value in the original cell (e.g., D3) to update the value in the formula.

New amount in the cell in WPS Spreadsheet with formula in G3 being identical to D3”

New amount in the cell in WPS Spreadsheet with formula in G3 being identical to D3”

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

  1. How do I extract data from a column in Excel?

GIF demonstrating how to extract data from a column in Microsoft Excel”

Step 1: Insert a new column. (Or as many as you need!)

Step 2: In the new column(s), write the following:

=LEFT(B1,2) to extract the first 2 characters of the cell B1.

=RIGHT(B1,8) to extract the last 8 characters of the cell B1.

=MID(B1,4,2) to extract the 2 characters following the 4th character in B1.

Step 3: To apply the changes to the cells below, drag down the blue square.

  1. How do I copy data from one cell to another in Excel automatically?

To understand how to copy data from one cell to another in Excel automatically, follow these steps:

Step 1: Create a VBA macro. In the “Ribbon”, go to “Developer” then “Visual Basic”. If you don’t have this tab available, find out how to add the “Developer” tab.

Step 2: Once you’re in the VBA window, in the “Ribbon”, select “Insert” then “Module”.

Step 3: In the code window on the right side, type the following macro:

Sub CopyData()

Range("B2").Copy Range("D2")

End Sub

This copies the data that is in cell B2 to cell D2.

Step 4: You can extend this macro to copy to more than one cell.

Sub CopyData()

Range("B2").Copy Range("D2:D6")

End Sub

This copies the data in cell B2 across to D2 and down to D6.

  1. What is the Excel formula to copy a cell value to another cell if the condition is met?

The Excel formula to copy a value to another cell if the condition is met can be done using the following steps:

Step 1: Select your desired cell.

Step 2: Type the following formula in the selected cell:

=VLOOKUP($[cell letter]$[cell number],[start of dataset]:[end of dataset],[column index number],1)

Step 3: Press the “Enter” button on your keyboard.

Summary

In closing, Microsoft Excel is a comprehensive and effective tool. Excel is intuitive and user-friendly for anyone to adapt and utilize with ease. This guide has shown how to copy values from one cell to another in Excel using formula.

However, for software that is free of charge but still offers you full functionalities, WPS Office Spreadsheet is the way to go. In addition to being free, it also has a user-friendly interface. So, users won’t have to learn something completely new with WPS Office’s compatibility with Microsoft Office.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.