Most of the time, we will use Excel to do some calculating. During the process, we will certainly use the formulas in Excel to help speed up the process. Thus, we have the ways to solve errors when copying cells with formulas.
The formula in Excel shows its powerful capabilities. But in some cases, we need to use the results produced by those formulas in other worksheets. If we don’t know which number is the result of calculation, we will meet with some troubles. If we simply copy it and paste to another cell, actually we copy a formula. And the result will be another number or show an error. And here is such an example.
An Example of Copy Error in Formula
In the image below, you can see that this is a worksheet of sales volume of some employees.
The last column is the total volume of every one in a year. And actually, the result is calculated through the formula of “Sum” formula. But now you need to copy those numbers to a wage worksheet to calculate employees’ bonus. If you directly copy it and paste to a new column, the result will be like this:
This means that the formula cannot work here.
Thus, we need to display cells with formulas before we have the next move. And the following are the two methods to solve this problem.
Display Cells with Formulas
- Click “File” on the ribbon.
- Next, click the “Options” on the left.
- And then you will see a new window pops up. Click the option of “Advanced”.
- And now drag the toolbar until you see the option of “Display options for this worksheet”.
- Then click the small arrow at this part.
- And in the drop-down menu, choose the worksheet that you want to show the original formula.
- And then check the second option “Show formulas in cells instead of their calculated results”.
- Now you can click “OK” to save your setting.
And come back to the worksheet, you will see that the formulas of the total column show up.
And now you know which cells have formula and which cells don’t have. Thus, you can choose to copy the values of cells.
Copy the Values of Formulas
Still you can use special options in paste even if you don’t know which one is the result of formulas.
- Copy the target area.
- Right click the target cells where you want to input the numbers.
- And in the new list, click the “Values” in the paste option.
Thus, you can get the numbers instead of the errors in your new worksheets.
A Quick Guide to Recover Damaged Excel
In the process of using Excel, you may have the possibility of meeting a damaged Excel files. When such accidents happen, the recovery of Excel files becomes your primary task. In addition, you can use a third party tool to fix Excel files. It can retrieve your information in a quick time.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com
Leave a Reply