Excel's Paste Special tool lets you select the parts of the spreadsheet you want
Q I have developed an Excel spreadsheet with various formulas and data that ultimately produce a results sheet for printing out.
I would like to do a ‘Save as’ to produce the Excel file with the answers to the various calculations only, thus removing the formulas from the file. In this way, I can email an editable results file. Is this possible?
A The various versions of Excel do of course have several Save as options – as we imagine you’ve already discovered – but the ability to save a sheet without formulas is lacking. However, there is a way around this, using Excel’s Paste Special tool.
You didn’t tell us which version of Excel you use but it doesn’t really matter, because the process is largely the same across editions.
To begin, open your spreadsheet and then save it using a different name. Now select all the cells either by clicking the blank square at the top left, where the column and row headings intersect, or by holding down Control (Ctrl) and tapping A (depending on how your sheet is set up, you may need to tap A a couple of times in order to highlight the entire sheet).
Next, copy the entire sheet to the Windows Clipboard using the Control and C keyboard shortcut.
The final stage is to use the Paste Special function to paste only the cell values back into place. The precise method varies between Excel versions, but only slightly. In Excel 2003, for example, choose Paste Special from the Edit menu, click to select the Values radio button.
The process is the same in Excel 2007 and 2010 but to reach the Paste Special dialogue box, click the down-pointing arrow at the foot of the Paste button on the ribbon’s Home tab.
Click OK and it will look as though nothing has changed. However, the spreadsheet is now stripped of the unwanted formulae – save the spreadsheet and that’s that.
Updating your subscription status