Sometimes when working with spreadsheets in Excel, you may want to distribute a workbook with static values. If your worksheet has formulas that are linked to other worksheets, you may end up with the dreaded Ref# error when distributing. That is why it is important to convert the formulas to values.
In this tip, we explain how to convert formulas to values with a drag.
You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® for Windows 2010, 2013, 2016
Consider the example below:
The Column New Price includes a formula, which is based on the Price column.
To keep the results of the formula, you could copy the New Price column and paste special values, however, you can also achieve this using another method:
1. Highlight the data containing the formulas.
2. With the right mouse button clicked, select and drag the highlighted cells away and then back on itself (Alternatively, you could drag the cells to be copied to a new location).
3. Release the right mouse button, a list of options will pop up, select Copy Here as Values Only.
4. The data is now copied as values.
By using this method, you’ll quickly and easily keep values without their formulas, therefore avoiding the dreaded Ref# error.
The post Avoiding the Ref# error: How to convert formulas to values in Excel appeared first on Sage Intelligence.
Powered by WPeMatico