Appearances are often deceptive! It is true in the case of MS Excel too where numbers would wear the disguise of text. The implications can be brutal since we would be looking around to find out why everything has gone haywire, especially when formulae are involved. So, in this article, we would be exploring the ways to identify and convert these fancy dress participants back to their original form.
The following is the data we will be using to perform this exercise.
Anyone with the basic know-how to use MS Excel might be able to figure out that something has gone terribly wrong at the very first sight. What is with those green marks at the top left corner of the cells?
Let us click on any of these cells, to have a look at what it is trying to convey.
Oooo! There seems to be a pop up with an exclamatory sign. Now, let us click on that to look further into what it has got to say.
Finally, there’s an answer to the green mark. This drop-down tells us that all the numbers within the cells though may look like numbers are stored in the format of Text. While this may seem a bit difficult to comprehend, let’s widen our view to get a better picture.
In the above image, it is evident that the cell’s format is in Text & there’s also something strange in the entries of column C. The contents of the selected cell C3 is being displayed in the Formula Bar & it seems that the number is preceded by an apostrophe (‘). That explains why Excel has got a number confused with text.
Enough of these mishaps & let us get these entries converted back to numbers. One shall do that by clicking on the Convert to Number option available in the drop-down as shown in the following image.
The same process is to be repeated by selecting the remaining entries of column C & clicking on Convert to Number as shown in the below image.
Once done, the green mark disappears, the apostrophe preceding the number is gone for good & the format of the cell is now displayed as General, instead of Text as shown below.
Let us now bring the change to column E containing the Total Price by selecting all the entries which currently display the formula rather than the results of it.
It is evident in the above image that the format of all the selected cells is Text. We shall now change this into General by choosing from the drop-down list as shown below.
There would be no visible changes even after choosing General as the cell format. Patience mate! Double click on any cell, in this example, we are going to double click E3. A blinking cursor would appear within the cell as shown below.
Now hit ENTER! The active cell moves to E4, but it leaves the number from the result of the formula rather than the formula itself as shown below.
Put the active cell back to E3 & select all the entries in column E as shown below.
Press CTRL + D – the command to duplicate cells, but since the formula is relatively referenced to the cells, it updates itself simultaneously as each cell duplicates & displays the end results as numbers as shown below.
Conclusion
Similar to this QuickExcel contains many other articles that can help you with ways various to use MS Excel. Please do have a look at this article detailing the removal of duplicate entries in MS Excel. Cheers!