Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to reference format and value from another cell in Excel?

Normally, we use the formula =A1 to reference cell A1 to another cell in worksheet. But this can only reference the cell value. If you want to reference the cell value as well as its format, you need to try another method. This article provides two methods for you to achieve it.

Reference format and value from another cell with pasting linked picture
Auto reference format and value from another cell with VBA

Reference format and value from another cell with pasting linked picture

Supposing you want to reference format and value from cell A1, please do as follows to get it done.

1. Select the cell (A1) you need to reference, then copy it with pressing Ctrl + C keys.

2. Go to the cell you want to link the reference cell, right click it and select > Paste Special > Linked Picture. See screenshot:

Now the format and value of cell A1 is referenced to a specified cell. And the format and value of these two cells will be synchronic as below screenshot shown.

Auto reference format and value from another cell with VBA

You can reference format and value from another cell automatically by running the below VBA script.

1. Right click the sheet tab which contains the cell you need to reference, and then click View Code from the right-clicking menu.

2. In the popping up Microsoft Visual for Applications window, copy and paste the below VBA code into the Code window.

VBA code: Reference format and value from another cell

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Worksheets("Sheet1")
        If .Range("A1").Value2 <> "" Then
            On Error Resume Next
            Range("A1").Copy (.Range("E2"))
        End If
    End With
End Sub

Note: In the code, Sheet1 is the sheet name which contains the cell you need to reference both the value and format. A1 and E2 means that cell A1 will be referenced to cell E2 automatically.

From now on, when value changes in cell A1 of Sheet1, its value and format will be referenced to cell E2 immediately.

Related articles:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (9)
No ratings yet. Be the first to rate!
Julian
Julian
This comment was minimized by the moderator on the site
Hi! Very cool!! Is there a way to do the first option in Google Spreadsheets?
Reply
crystal
crystal     Julian
This comment was minimized by the moderator on the site
Hi Julian,
Sorry we didn't test in Google sheets. Thanks for your comment.
Reply
Report
Lil
Lil
This comment was minimized by the moderator on the site
How do I do that if I want to copy to a different sheet?
Reply
crystal
crystal     Lil
This comment was minimized by the moderator on the site
Hi Lil,
If you need to copy to a different sheet, please apply the below VBA code. Sheet1 is the original worksheet, Sheet3 is the destination worksheet. Please change them based on your needs.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet1")
If .Range("A5").Value2 <> "" Then
On Error Resume Next
Range("A5").Copy (Worksheets("Sheet3").Range("E2"))
End If
End With
End Sub
Reply
Report
Jesse
Jesse     crystal
This comment was minimized by the moderator on the site
How to run this code on excel? Does it need to be run on both worksheet? Can it be specific like step-by-step?
Reply
crystal
crystal     Jesse
This comment was minimized by the moderator on the site
Hi Jesse,Hi, as the above code mentioned, you just need to fill the code in the original worksheet's Code window, when changing the cell value in the original worksheet (A5 in Sheet1 in this case), the code will be runned automatically. And cell A5 in Sheet1 will be copied to E2 in Sheet3.
Reply
Report
rdk
rdk
This comment was minimized by the moderator on the site
Hello did the below code but it did not work
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Competitive Analysis Summary")
If .Range("BI7").Value2 <> "" Then
On Error Resume Next
Range("BI7").Copy (Worksheets("In Depth View - ADP").Range("E55"))
End If
End With
End Sub
Reply
crystal
crystal     rdk
This comment was minimized by the moderator on the site
Hi, after adding the code, you need to modify the worksheet "Competitive Analysis Summary" to enable the VBA.
Reply
Report
Deb J
Deb J
This comment was minimized by the moderator on the site
Can I mirror the first 5 columns on each new sheet? I have the data just want the formatting to change with all sheets instead of having to do so repeatedly
Reply
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations