---
title: Copy an Excel 2016 formula without changing the cell references
description: How do you copy an Excel 2016 formula without changing the cell references?
---

[Skip to content](https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference#main-content)

![Conny-Söderholm](https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg)

- [Home](https://www.connysoderholm.com/)
- [Courses](https://www.connysoderholm.com/courses)
- [Articles](https://www.connysoderholm.com/blog)
- [Contact Me](https://www.connysoderholm.com/contact-me)

Open main navigation

Close main navigation

- [Home](https://www.connysoderholm.com/)
- [Courses](https://www.connysoderholm.com/courses)
- [Articles](https://www.connysoderholm.com/blog)
- [Contact Me](https://www.connysoderholm.com/contact-me)

 June 13, 2018

# Copy an Excel 2016 formula without changing the cell references

![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm)

Share: [facebook-f icon](http://www.facebook.com/share.php?u=https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference) [linkedin-in icon](http://www.linkedin.com/shareArticle?mini=true&url=https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference) [Twitter icon](https://twitter.com/intent/tweet?url=https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference) [pinterest-p icon](http://pinterest.com/pin/create/link/?url=https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference) [envelope icon](mailto:?body=https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference)

*How do you copy an Excel 2016 formula without changing the cell references?*- Have you copied a range of cells in Excel and wanted them to reference the same cells as before? Have you happily copied them, pasted them and realized that Excel dynamically updates the cell references? There are two ways around this. Either you lock the cell references with the dollar ($) sign or use a small Notepad hack which I will show you below.

Suppose you have a 4x2 table starting from cell A1 in a spreadsheet. You have another table starting from cell B8 that has the formula "=A1" and so on. Now you want to copy this range so that you can do some other calculations etc on the same values. You copy range B8:E9 and paste it on cell B11. You will now see that your old references are not preserved.

To solve this problem, just go through each cell's formula and press F4. Suppose you press F4 in cell B8's formula. The formula will change to "=$A$1". This means that the cell formulas reference will not change, even when you are copying, cutting, dragging or pasting the formula reference. Problem solved. But, what do you do when you have created a table that could be 100x100 cells? It is very time-consuming to change all these cell references.

You can then do another thing. First, go to the Formula tab and click on the "Show formulas" button. Your spreadsheet will now look like below. Now select your range, and copy it. Open Word, Notepad or any text editor and paste your content. You will see that you have pasted the formulas. Select all the text (Control + A) and go back to your spreadsheet. Paste the formulas anywhere on your spreadsheet and deactivate the "Show Formulas" button. Voila! You have just copied the formulas without having to add any dollar sign. Happy copypasting!

[Excel](https://www.connysoderholm.com/blog/tag/excel)

## Related posts

[![](https://www.connysoderholm.com/hs-fs/hubfs/Imported_Blog_Media/28269236-99e7ea34-6b01-11e7-8ff9-21285576122f.png?height=200&name=28269236-99e7ea34-6b01-11e7-8ff9-21285576122f.png)](https://www.connysoderholm.com/blog/carnac-keypress-capture-software)

## [Carnac, Keypress capture software](https://www.connysoderholm.com/blog/carnac-keypress-capture-software)

![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) 

 August 27, 2019

For anybody searching for a great Keypress capture software, that also is free, check out Carnac

[Read more](https://www.connysoderholm.com/blog/carnac-keypress-capture-software)

[Excel](https://www.connysoderholm.com/blog/tag/excel)

## [Creating and formatting a chart automatically in Excel with VBA](https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba)

![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) 

 July 3, 2018

Creating and formatting a chart automatically in Excel with VBA Using Excel involves creating a lot...

[Read more](https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba)

[Python](https://www.connysoderholm.com/blog/tag/python), [openpyxl](https://www.connysoderholm.com/blog/tag/openpyxl), [Excel](https://www.connysoderholm.com/blog/tag/excel)

## [Control Excel with Python & OpenPyXL](https://www.connysoderholm.com/blog/control-excel-with-python-openpyxl)

![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) 

 August 1, 2019

I have finally published my first course, Control Excel with Python & OpenPyXL!

[Read more](https://www.connysoderholm.com/blog/control-excel-with-python-openpyxl)

[linkedin-in icon](https://www.linkedin.com/in/connysoderholm) [Follow us on Facebook](https://www.youtube.com/@connysoderholm1473)

- [Home](https://www.connysoderholm.com)
- [Courses](https://www.connysoderholm.com/courses)
- [Articles](https://www.connysoderholm.com/blog)
- [Contact Me](https://www.connysoderholm.com/contact-me)
- [Privacy Policy](https://www.connysoderholm.com/privacy-policy)
- [Disclaimer](https://www.connysoderholm.com/disclaimer)

---

[![Conny Söderholm Logo](https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg "Conny Söderholm Logo")](http://Conny%20Söderholm)

Copyright © 2026 Conny Söderholm

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Conny Söderholm",
    "url" : "https://www.connysoderholm.com/blog/author/conny-soderholm"
  },
  "dateModified" : "2023-07-31T14:40:22.609Z",
  "datePublished" : "2018-06-13T18:58:36.000Z",
  "headline" : "Copy an Excel 2016 formula without changing the cell references",
  "mainEntityOfPage" : {
    "@id" : "https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg"
    },
    "name" : "Conny Söderholm"
  }
}
```