July 21, 2016
Hi fellow Excel Addict,
Only 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.
If 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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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.
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;
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;
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.
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;
2) 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;
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;
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.
|"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