How to Avoid #REF! Errors When Deleting Data in Excel
Here is a true story. When I was working at my last day job, someone in our marketing department was given a list of leads from a trade show. The Excel file had the area codes in one column and the telephone numbers in another column. There were several thousand leads. Someone in Marketing was retyping all of the telephone numbers in order to get the area code and phone number in one cell, as shown in Fig. 288.
As shown in Fig. 289, I showed this person how to use a formula to quickly convert all five thousand telephone numbers to the format that she wanted. I started to walk away, whistling a little tune because I had just saved someone a whole lot of typing. I had just about reached the door of the marketing department when I heard a scream. Since column J contained the data that she needed, she had deleted columns H and I. However, since column J was still a live formula, Excel no longer knew how to calculate the results in J. Because cells referenced in the formula had been deleted, all of the telephone numbers that were in column J now showed the #REF! error, as shown in Fig. 290.
Strategy: Immediately hit Ctrl+Z to undo the delete and get the source columns back. Next, you will copy the formulas and use Paste Special to paste the cells as values instead of formulas.
-
With the cell pointer in cell J2, hold down the Shift key while you tap the End key and then the Down Arrow key. This will select the entire contiguous range of cells in column J.
-
From the menu, select Edit – Copy to copy the cells to the clipboard.
- Without changing the selection, select Edit – Paste Special from the menu. In the Paste Special dialog box, choose the Values option, as shown in Fig. 291. Choose OK.
Result: The telephone numbers in column J are converted from formulas to the results of those formulas, as shown in Fig. 292.
You can now safely delete columns H and I and the telephone numbers in column J will not change to #REF! errors.
Summary: Using the Paste Special Values technique is essential to becoming proficient in Excel. It will convert a range of formulas to the current value of the formula. You can then safely delete the data in the source columns.
Commands Discussed: Edit – Copy; Edit – Paste Special; Undo
Images
