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.

Read More... Free Download... Purchase...

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!

Read More... Free Download... Purchase...

7 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Deb J · 1 months ago
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
To post as a guest, your comment is unpublished.
rdk · 7 months ago
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
- To post as a guest, your comment is unpublished.
  crystal · 3 months ago
  
  Hi, after adding the code, you need to modify the worksheet "Competitive Analysis Summary" to enable the VBA.
  
  Reply
To post as a guest, your comment is unpublished.
Lil · 1 years ago
How do I do that if I want to copy to a different sheet?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  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
To post as a guest, your comment is unpublished.
Julian · 1 years ago
Hi! Very cool!! Is there a way to do the first option in Google Spreadsheets?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Julian,
  Sorry we didn't test in Google sheets. Thanks for your comment.
  
  Reply