FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003

July 21, 2016

 
Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes - TheExcelAddict.comOnly a couple of days left before the big race. No! Not the Republican National Convention! The Tely 10 race that I will be running on Sunday.

Also, I want to let you know that after today, I will be taking a break from my newsletter for 2 or 3 weeks to take advantage of some of this nice summer weather. In Newfoundland there's no guarantee how long it may last. I don't have any specific vacation plans, so you may see me send out a repeat tip from a past newsletter...but that's just a 'maybe'.

Today's tip 'How to Copy Formulas And Keep Cell References From Changing' was again prompted from several questions I have received. Every Excel user occasionally needs to copy a bunch of formulas exactly as they are and not have their cell references change. Today I'm going to show you couple of options you have.

I
f you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you how to 'Print a List All Named Ranges in Your Workbook'. You can read the tip here.

I hope you are enjoying your summer (or winter if you live 'down under') and I look forward to being back in touch in a few weeks. Until then...

Keep on
Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com




If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

"When it is obvious that the goals cannot be reached,
don’t adjust the goals, adjust the action steps."

-- Confucius --

If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

How to Copy Formulas And Keep Cell References From Changing

You know that if you don't want the cell references of a formula to change when you copy it to another cell, you can copy it directly from the Formula Bar or you can use an absolute references in your formula. That is, you put dollar signs ($) before the column letter and row number for the cell reference that you don't want to change (e.g. =SUM($B$3:$C$3)).

To add absolute referencing you can type the $ directly in the formula or you can select the cell reference in the Formula Bar and then press the F4 key to toggle through the variations of cell referencing (e.g. B3, $B$3, B$3, $B3).

This is pretty easy to do when we're talking about just a few formulas, but if you have a whole range of formulas that you want to copy to another worksheet or workbook, these methods are not practical.

Copy Range Of Formulas in Microsoft Excel 2007 2010 2013 2016 365
I want to show you two techniques you can use to make this process much easier.

Practice on your own worksheet or you can download this practice file.

The first technique for copying formulas and keeping their cell references from changing is...

Find & Replace


In this technique we are temporarily going to replace the equal sign in each formula with another character, so that they are no longer formulas. This allows us to copy them elsewhere without the cell references automatically adjusting.

1) First, select the range of formulas you want to copy;

2) Press CTRL+H to open the 'Find and Replace' dialog;

3) In the 'Find what' box, type = (i.e. an equal sign);

4) In the 'Replace with' box, type # (i.e. number, hash, pound, hashtag);

5) In the Options area make sure 'Look in' is set to Formulas and the 'Match entire cell contents' option is not checked. If you don't see these options in your dialog, click the Options>> button;

Change Formulas To Text in Microsoft Excel 2007 2010 2013 2016 365
6) Next, click the Replace All button and all of the formula will now have their = replaced with a #, thereby converting them to text strings;

Formulas As Text in Microsoft Excel 2007 2010 2013 2016 365
7) You can now Copy and Paste these text-string formulas wherever you want;

8) Once you have pasted them, use 'Find and Replace' once again, this time using # in the 'Find what' box and = in the 'Replace with' box. Then click Replace All. All of the text-string formulas will be converted back to regular formulas. If any of the formulas you have copied reference other cells in that same copied range, you may get #VALUE! errors, however, once you complete the next step (9), all formulas should be good to go;

9) Finally, go back to the original range and repeat the previous step (i.e. Find and Replace) to convert your original cells back to regular formulas.

Convert Text Strings Back To Formulas in Microsoft Excel 2007 2010 2013 2016 365

Although this technique is effective and much easier than manually adjusting the referencing for each formula, there is a risk that you may mess things up.

Maybe you will prefer this next technique.

The second technique for copying formulas and maintaining their references is...

Using Notepad as a Helper

This technique is less prone to error because you aren't actually changing the formulas in your worksheet. You are using Notepad as a tool, outside of Excel, to transfer the formulas intact.

1) First, select the range of formulas you want to copy;

Keyboard shortcut to display Excel formulas2) From the Formulas tab, click Show Formulas in the Formula Auditing group. This displays formulas in your worksheet instead of showing the formula results. Alternatively you can press CTRL+` (accent grave) to toggle between showing formulas and showing results. The accent grave key is just above the Tab key on typical North American keyboards. Other keyboards may be different;


Copy Formulas Without Changing Cell References in Microsoft Excel 2007 2010 2013 2016 365
3) Copy these cells and Paste them into a 'plain text' editor such as Notepad or TextPad. Do not use MSO Word or Wordpad or any other program that uses Rich Text formatting;

Copy Formulas To Plain Text Notepad in Microsoft Excel 2007 2010 2013 2016 365
4) In the text editor, press CTRL+A to select all the text and CTRL+C to copy it;

5) Go back to Excel and select the top left cell of the range where you want to paste the formulas and press CTRL+V to paste. The formulas will be pasted into the sheet exactly the same as the originals;


6) Finally, go back to the sheet where you copied the formulas from and
click Show Formulas (or press CTRL+`) on the Formulas tab to turn off formula view.

Try both of these options and let me know which one you prefer for copying your formulas.

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week


Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630