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

Get FREE Advanced Excel Exercises with Solutions!

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.

how to keep formatting in excel when referencing cells


How to Keep Formatting in Excel When Referencing Cells: 2 Ways

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.

how to keep formatting in excel when referencing cells

  • Now 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 remains the same in the formula bar.

how to keep formatting in excel when referencing cells

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

Read More: How to Link Cells for Sorting in Excel


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 the Developer Tab.

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

how to keep formatting in excel when referencing cells

  • 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.

how to keep formatting in excel when referencing cells

  • 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.

how to keep formatting in excel when referencing cells


Download Practice Workbook


Conclusion

The bottom line is, the article shows 2 possible ways to keep formatting in Excel when referencing cells. I hope you may benefit from 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 content in the upcoming articles.


Further Readings

<< Go Back To Excel Link Cells | Linking in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

6 Comments
  1. Thank you Nahian. However, what about referencing cells from an external spreadsheet? I basically want to copy the cell’s value/text, including the formatting, into another spreadsheet. The values are easy enough but I want the formating to be copied too.

    The original spreadsheet is basically a calender with several apartments booking details. I need to extract one apartment details (each day of the month, each month in one row) to put into a separate spreadsheet.

    This is so that I can share this new spreadsheet with the owner of this apartment without them being able to see the original spreadsheet that has all the other information.

    I can do this easily however, the formatting is not copied over.

    • Thank you Julie for reaching out. The first method can be done to solve your problem. Just copy the data and paste this as linked picture into a new spreadsheet. Any change in your main spreadsheet will automatically be updated in the new sheet.

  2. Excel crashes after running the VBA.

    • Hi Z
      I have checked the code in different Excel versions on different laptops. It’s working fine. This is an event-driven macro. So, on changing the cell’s value, you should get the output. Maybe due to some compatibility issues or external problems, you are facing this hiccup.
      Thank you

  3. Hi, Would this work on ranges of cells so that one VBA can be written for all rather than one cell at a time.
    For example sheet 1 range A1:A67 as reference cells, copied into sheet 2 range A26:A85 cells?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 22, 2024 at 4:37 PM

      Hello Lea, thanks for reaching out. Here’s a solution for your query.

      
      Private Sub Worksheet_Change(ByVal Target As Range)
          On Error Resume Next
          Application.EnableEvents = False
          
          ' Check if the changed range is in Sheet1 A1:A67
          If Not Intersect(Target, Sheets("Sheet1").Range("A1:A67")) Is Nothing Then
              ' Update data in Sheet2 A26:A85
              Sheets("Sheet2").Range("A26:A85").Value = Sheets("Sheet1").Range("A1:A67").Value
              
              ' Copy formatting from Sheet1 A1:A67 to Sheet2 A26:A85
              Sheets("Sheet1").Range("A1:A67").Copy
              Sheets("Sheet2").Range("A26:A85").PasteSpecial Paste:=xlPasteFormats
              Application.CutCopyMode = False
          End If
          
          Application.EnableEvents = True
          On Error GoTo 0
      End Sub
      

      Procedure:

      1. Right click on the Sheet1 tab of your workbook and select View Code.

      2. Paste the code and save the workbook.

      3. Now, if you do any formatting and insert a value in the range of A1:A67 of Sheet1, you can see the values and formatting get copied in the Sheet2.

      Regards
      Meraz Al Nahian
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo