How to Keep Formatting in Excel When Referencing Cells (2 Methods)

The article focuses on how to keep formatting in Excel when referencing one or multiple cells. When we reference a cell in an excel worksheet, we can have the value of that cell but we won’t get the formatting of the cell. So we will be discussing how we can get the formatting of a cell when we reference them,

Here we have a dataset of the ages of some people. We have two columns mentioning these peoples’ names and ages. The header and data of this dataset have different cell colors as formatting.

Table of Contents hide

Download Practice Workbook

2 Ways to Keep Formatting in Excel When Referencing Cells

1. Keeping Format in Excel When Referencing Cells by Copy and Paste Feature

2. Using VBA to Keep Formatting in Excel When Referencing Cells

Practice Section

Conclusion

Download Practice Workbook

Keep the Format While Referencing Cells.xlsm

2 Ways to Keep Formatting in Excel When Referencing Cells

1. Keeping Format in Excel When Referencing Cells by Copy and Paste Feature

Suppose you want to move the Name column to a new position with the formatting it has. We can simply do this by copying and Paste Special command. Let’s discuss the procedure.

Steps:

Select the cells B4 to B10 and press CTRL + C.

Select a cell where you want to paste this column. I want to copy in column E, so I select E4.

Now do the right-click on cell E4 and select Linked Picture from Paste Special.

You will see the cells B4 to B10 in column E with the same formatting. You can also see that the cell reference of the copied column also remains the same in the formula bar.

Thus, you can keep the formatting of the cells you want to refer to in other cells.

Similar Readings:

2. Using VBA to Keep Formatting in Excel When Referencing Cells

Another approach to keep the formatting of the cells when referencing them is to utilize Visual Basic for Application (VBA). To apply the VBA, follow the procedure below.

Steps:

First, open Visual Basic from Developer Tab

VBA will open. Double click to open Sheet4 (Or in the sheet you want to run VBA)

Type the following code in the Sheet.

Private changing As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = [B4].Address Or changing Then 
    Exit Sub
    changing = True
    [B4].Copy [E4]
    changing = False
End Sub

Here, we want to refer cell B4 to E4. Whatever change we make in cell B4, it will also appear in cell E4. Here, I used an IF statement to check whether the used cell reference has Address or not. So we target the address of B4 and then Copy it to E4 by using the Copy method.

Now save the code by pressing CTRL + S and go to the sheet (in my case it’s Sheet4)..
Copy cell B4 and Paste it into E4.

Now type something different in cell B4. Say we want to change the heading and give the new title Name of Players. So type it in cell B4.
After that, press ENTER and you will see the title in E4 also changes.

The change in E4 is exactly the same as B4 with formatting. Thus we can keep the formatting when referencing cells by applying VBA.

Practice Section

In the following picture you will see the dataset of this article. I’m giving it to you so you can practice on your own.

Conclusion

The bottom line is, the article shows 2 possible ways to keep formatting in Excel when referencing cells. I hope you may be benefited by this article. If you have any special ideas regarding this topic or any feedback, please feel free to leave them in the comment box. This will help me enrich my vision as well as my contents in the upcoming articles.