In Excel, you will certainly do some work of calculating. Thus, today we will talk about the calculation problem appearing in values copied from other sources.
Excel specializes in dealing with the calculations of numbers. However, there exists a problem in doing some calculations. Sometimes, if you copy the values from other sources, i.e. from a website, you will find errors in the calculation.
The Error in Calculating Copied Values
In this image, the currency values are copied from other sources.
And to calculate the sum of those values, using the function of SUM is the normal choice.
However, the function will not select the values automatically like it always does.
In addition, even if you select those numbers manually, the result of calculation will be 0. This is because the copied values are in the form of text, so Excel is unable to identify those numbers. Thus, before you do the calculation, you should first do a bit more steps.
Calculate the Copied Values
Now here are the steps that you should do to get the right result.
- Click any of the numbers.
- Select the front part of the number, and the space before the number should also be selected.
- Press the shortcut key “Ctrl + C”.
- Press the shortcut key “Ctrl + F”.
- Choose the tab “Replace”.
- In “Find what” text box, press the “Ctrl +V” to paste what you have copied.
- Now you click the button “Replace All” in the window. And then there will be a new window and tell you that the process has finished.
- Then close the “Find and Replace” window.
- Select all the cells that you want to calculate.
- Right click your mouse.
- Then in the pop-up menu, choose the option “Format Cells”.
- In the new window, choose the option “Currency”. In this example, the copied numbers are currency. And in actual situation, you can also change the cells format into “Number” or others according to your need.
- On the right part, choose the symbol of the currency. And here we choose the “$”.
- Next click “OK”.
Now you have finished all the settings. And click the “SUM” button, the area will be automatically selected and then you can get the result easily without any error.
Recover your Data without Losing any Time
The calamity of data corruption can cause you great loss. Not only will you cost a tidy sum, but also you will face the danger of data exposure. Therefore, you should take action as soon as you find the Excel File corruption. In addition, you can also choose to apply our powerful software to repair Excel files. It can handle almost all the damaged Excel files easily.
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