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:
- How to reference or link value in unopened/closed Excel workbook file?
- How to reference worksheet by index number instead of name in Excel?
- How to remove all formula references but keep values in cells in Excel?
- How to keep formula cell reference constant in Excel?
- How to reference tab name in cell in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!









