Formatting Issues

How to Preserve Cell Formatting When Referencing Another Excel Cell

Huda Qurayshi Oct 16, 2026 868 views

Question details

The user needs to reference the value of another cell while retaining its original rich formatting, such as font sizes and background colors.

How to Preserve Cell Formatting When Referencing Another Excel Cell

Product: Excel
Device & OS: not provided
Scenario: Referencing data from a source cell to a destination cell and needing the visual style to carry over automatically.
Observed behavior: Standard formulas like '=A1' only pull the raw data value and fail to copy any of the source cell's formatting.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) if you plan to use VBA to automate the format copying process.

Solution 1Recommended

Use a VBA Macro to Copy Values and Formatting

Since built-in functions only return values, running a VBA macro is the most effective way to copy both the value and the rich formatting from the source cell to the destination.

VBA allows you to bypass the limitations of standard formulas by executing a direct copy-and-paste command in the background.

You can bind this macro to a button or use a Worksheet_Change event to update the destination cell automatically when the source changes.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click 'Insert' in the top menu and select 'Module'. A blank code window will appear.

Enter the Copy Code

Type a basic copy-paste script, such as 'Range("A1").Copy Destination:=Range("B1")', replacing the cell references with your actual source and destination ranges.

Run the Macro

Press F5 or click the 'Run' button. The destination cell will now display both the value and the exact formatting of the source cell.

Use a VBA Macro to Copy Values and Formatting

Automation Tip: To avoid running the macro manually, you can place your code within the Worksheet_Calculate or Worksheet_Change event to keep the destination cell synced with the source.

Solution 2

Use Paste as Linked Picture

If you simply need to view the formatted cell in another location and do not need to manipulate its text data, pasting it as a linked picture is a quick workaround without using code.

Seamless Data and Formatting Management with WPS Spreadsheet

WPS Spreadsheet provides robust tools for handling data and formats efficiently. With full support for VBA macros and advanced paste options, you can easily synchronize both values and styles across your worksheets.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing data file.
2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon to access Macros and the VBA Editor.
3. Automate Formatting: Write your VBA script or use the straightforward Paste Special menu to duplicate your cell formats effortlessly.

Fully compatible with Microsoft Excel (.xlsx, .xls, and .xlsm) formats.Advanced Developer tools with built-in VBA support to automate format copying.Intuitive Paste Special options, including Linked Picture, for quick visual referencing.

microsoft office alternative - wps office

Frequently Asked Questions

Is there any built-in Excel function that can copy formatting?

No, Excel functions and formulas are designed strictly to perform calculations and return values. They cannot retrieve or apply formatting such as font color, bold text, or cell background color.

Will Conditional Formatting rules transfer with a standard formula?

No. When you use a formula like '=A1', only the resulting text or number is transferred. You must apply the same Conditional Formatting rules manually to the destination cell for it to look the same.

How can I quickly copy formatting without VBA?

If you don't need the format to update dynamically, you can use the Format Painter tool. Select your formatted source cell, click the Format Painter icon on the Home tab, and then click your destination cell to apply the style.

Document & File Issues