Text Format in Excel

By
In this blog post we will learn in detail about “Text Format in Excel”. There are many instances when we need to convert numbers in “Text Format”. There are some advantages as well as some drawbacks of this “Text Format in Excel”. We will learn all these things in detail in this blog post.

In our previous blog post we have seen 03 methods to go to additional options of formatting cell values. If you missed to read those methods, please click here before going for this blog post.

We can use any of these 03 methods to get the “Format Cells” dialog box and select “Text” Category as shown in below image. With this we can see all the additional options under “Text Format in excel” as shown in below image:


Sample: In this field we can see how our output will look like. We can see the sample output of the value which we are going to convert in “Text Format”.

First we will see how we can convert the number into “Text Format” with simple example.

1. We have a value which is already in the “Number Format” as shown in below image.


2. Select the cell where we have entered value in “Number Format” and which we wish to convert into “Text Format” and select “Text” category under Number Tab which will help us to see the additional options available under “Text Format”. (You can use any of the 03 methods to get this “Format Cells” dialog box, click here to read more about these methods or press “Ctrl+1” keys on keyboard).


3. Once we click on “OK” button, we can see that the selected value from “Number Format” is now converted into “Text Format”.


IMPORTANT NOTE: If we see the Green dot on the top left corner of the cell then it indicates that the cell is formatted in “Text Format” in excel.

When to Use “Text Format”:

When we want to display the value which we are entering in cell “as it is” then we can use “Text Format”. There are some instances when excel will manipulate the values which we are entering in cell and will not display the correct value. We will see few cases as listed below where excel will not show correct values. We will also see how we can see “Text Format” to overcome these problems.

A. Sometimes Prefix 0 is not visible in number.
B. Numbers with 16 or more digits does not display properly sometimes.

A. Prefix 0 is not visible in number sometimes:

If we try to enter value in excel with prefix 0, excel will automatically convert this value in “General Format” by default and the prefix 0 will not be visible in cell.

1. Let’s try entering below value in cell.
2. Once we press enter or we will move to another cell, then we can see that this cell is converted into “General Format” by default and the prefix 0 will not be visible in cell.


3. To overcome this problem we have to first convert this cell into “Text Format” by following the steps explained in the beginning of this blog post and then enter the value with prefix 0.

4. Once we press “Enter” key then we can see that the value is now displaying “as it is” with prefix 0.

B. Numbers with 16 or more digits does not display properly sometimes.

Excel has limitation to display values entered in “Number Format” in a cell. If we enter a value in “Number Format” with 16 or more digits then excel will round down the 16th and all the following digits to zero. This is because Excel has maximum 15 digits of precision.

So in this case we can convert the numbers into text format which will allow us to display the numbers “as it is” even if they are with 16 or more digits.

1. Let’s try entering below value in cell which is already converted into “Number Format”.

2. Once we press enter or we will move to another cell, we can see that the 16th and all other following digits are converted into 0. This is because Excel has maximum 15 digits of precision.

3. To overcome this problem, we have to first convert this cell into “Text Format” by following the steps explained in the beginning of this blog post and then enter the below value.

4. Once we press “Enter” key, we can see that the value is now displaying “as it is”.


Drawback of Using “Text Format’ in Excel:

Until now in this blog post we only seen the benefits of “Text Format in Excel” or how this “Text Format in Excel” is useful to us in some situations. But apart from these benefits “Text Format in Excel” also have some drawbacks. Let’s learn about this also.

Excel showing wrong calculation sometimes.

Let’s take an example as shown in below image. We have two numbers listed in two cells 5500 and 4500 and we want to calculate sum of these two numbers. The output of SUM should be 10000 but excel is showing the SUM output as 4500. This is wrong.

You might be thinking why excel is showing wrong calculation?

The answer is our 1st number is formatted as “Text Format”. Excel will not consider the values which are formatted as “Text Format” while performing the calculation.
If we want to correct this calculation then we have to convert all the cells which are involved in calculation into “Number Format”.

If we want excel to display correct calculation we must take care of cell formatting in excel.

I hope you are now clear with “Text Format in Excel” concept and you can use this “Text Format in Excel” wisely as per your requirements.
Microsoft Certified Excel Expert, Enthusiastic to teach excel tricks and basic excel concepts which will help to save time and efforts in our daily office work.

Comments

  1. UnknownFebruary 3, 2022 at 10:43 AM

    Thank you so much

    ReplyDelete
    Replies
    1. TejrajFebruary 5, 2022 at 10:59 PM

      Most welcome. Please go to "Topics" section and click on each topic to learn in detail.

      Delete

Post a Comment

Related Topics..

Fill Up, Down, Right & Left in Excel

By
Image
Excel has many great tricks which can help us to save our time while working on big data. In this blog post we are going to see one of those tricks where we can fill the data to the upwards of the selected cells, to the downwards of the selected cells, to the right of the selected cells and to the left of the selected cells. There are many situations where we may need to copy the data from selected cell to the upward, downward, left and right cells. But how can we do so?? In excel we can do so with the help of “Fill” command. This command we can found in: Home Tab --> Editing Group --> Fill Command. Let’s take an example. We have inserted a text “Tejraj” in a cell “D5”. Now we will see how to fill this text from cell D5 to Up, Down, Right & Left. Fill Down: 1. Let’s assume that we have to fill the data from cells D5 to D10. For this, select the Cell Range D5:D10. Keep in mind that the data from 1st cell of the selected Cell Range will fill down in entire selection. 2. Select ...
Read more

Excel Shortcuts Ctrl A to Ctrl Z

By
Image
Use of keyboard shortcuts while working in excel will be a great help for us. It will save a lot of time. In this post we are going to see Excel Keyboard Shortcuts for alphabets A to Z while using Ctrl key. Below table will give you a brief idea for each shortcut and its functionality: Now, we will see in detail how each of above listed shortcut combinations will work: Ctrl + A :  It will select all content present on the worksheet. If active cell is in the table then it will select entire table. Ctrl + B :  It will convert selected text into Bold . Ctrl + C : It will copy the selected content. The content which is copied can be seen in the Clipboard. Ctrl + D :  It will fill down the content from upper cell. For example we have inserted a number “1” in the cell A1. If we select the cell range from A1:A5 and press Ctrl + D, then this number “1” will automatically fill down in the entire cell range . ...
Read more

Excel Pivot Table

By
Image
Pivot Table in excel is a very powerful feature which helps us to to organize and summarize the data in very effective manner, that too without using any complex excel formula or functions. In this blog post we will see how to create Pivot Table in Excel. Below is the dummy sales data for last three years (2018, 2019 & 2020). In this sales data employees A, B, C & D sales various electronic components like Resistor, Capacitor, Inductor, Diodes & LED in all South, East, North & West region of India. Creating Pivot Table: Now we will see how to create Pivot Table in Excel. Pivot Table feature is available in Insert Tab. Pivot Table command is available in "Insert Tab" under "Tables Group" as shown in below image. Once you click on Pivot Table command, “Create Pivot Table” dialog box will appear on screen. This is like as shown in below image. This “Create Pivot Table” dialog box is very important to create Pivot Table. There are two important options a...
Read more

How to Remove Duplicate Entries from Multiple Columns in Excel

By
Image
In our last blog post, we have learned to remove duplicates entries in excel . In this blog post, we will go little advanced to remove the duplicate entries from multiple columns. Removing Duplicates Entries from 02 Columns: We will learn to remove duplicate entries from the data as shown in below image. The entries which are duplicate in both the “Fruits” column and “Price/Kg” column are highlighted with different colors. To remove duplicate entries in these 02 columns, follow the steps provided below: 1. Select the data from which we want to remove the duplicates. 2. Go to “Data” tab and under “Data Tools” we can find command named as “Remove Duplicates” as highlighted in below image. 3. Once we click on “Remove Duplicate” command, one dialog box with same name “Remove Duplicate” will appear on our excel screen. 4. If we check the checkbox for “My Data has headers” then the header row from our selection will be excluded automatically and under “Columns” section we will see the name o...
Read more

Insert Multiple Rows in Excel Based On Cell Value

By
Image
In this blog post we will learn to Insert Multiple Rows in excel based on cell value with the help of VBA Code. For this we have prepared dummy data as shown in below image. In this data we have listed few “Products”, their “Quantities” and their “Unit Price”. Now we want to insert multiple rows based on their “Quantities”. For example, “Product 1” has 04 quantities so we have to add 04 rows below the Product 1, “Product 2” has 02 quantities so we have to add 02 rows below the Product 2 and so on. To avoid wasting our time by manually inserting rows one by one, we can use VBA code which will do our work within seconds. To do this, just follow the below steps: 1. Add “Developer Tab” on the Ribbon if it is not already added. If you are not aware about how to add Developer Tab on the Ribbon then click here . Once added on the Ribbon, “Developer Tab” will looks like as highlighted with red in below image. 2. Now, click on the “Visual Basic” command as highlighted in below image. 3. Once ...
Read more

Select all cells highlighted with specific color at a time

By
Image
Many times we apply colors to cells in excel sheet which will help us to identify important cells just by looking at the data. But what if we use multiple colors to highlight data and later on want to select all the cells which are highlighted with specific color only???  YouTube Video: For example, we have listed some values in excel sheet and highlighted few cells with red color and few cells with yellow color. Now, we want to select all cells which are highlighted with only yellow color at a time. But how????  Don't worry... We will provide you a fantastic solution for this!! Follow below steps:  1. Select entire data. 2. Press Ctrl + F. "Find and Replace" window will appear. 3. Click on Options button. 4. "Find and Replace" window will expand with some additional options. Click on Format button. 5. "Find Format" window will appear. Click on Fill tab and select yellow color. You will s...
Read more

Customizing Sparklines in Excel

By
Image
In our previous blog post we have learned to insert Sparklines in excel . Now, we will go little advanced in this blog post and learn to customize the Sparklines. If you have not visited our blog post on inserting Sparklines in excel then please click here before proceeding for this blog post, as we are going to use the same data which we have used previously. We have crated 03 types of Sparklines: Line, Column and Win/Loss as shown in below image. To customize Sparklines in excel, we have to first enable the “Design” tab on the ribbon. Design tab will get enabled if and only if we click on the Sparklines. If we click on any portion where Sparklines are not created then Design tab will get disabled. Once we click on the “Design Tab” then we can see the commands which are divided in various groups. In this blog post of “Customizing the Sparklines”, we will see all the commands “Show Group” which are highlighted with Pink color and the commands to change the “Sparkline Color” and “Mark...
Read more

SUMIFS Function in Excel

By
Image
In this blog post we will learn to about SUMIFS function in excel. In our previous blog post we learned about SUMIF function in which we learned to calculate the SUM based on certain criteria or condition. In this blog post we will learn to calculate the sum based on set of criteria’s or conditions, with the help of SUMIFS function. Where to find SUMIFS function on Excel Screen: SUMIFS function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. SUMIFS function is highlighted in blue in below image. Once we click on SUMIFS option as highlighted above, we will get the function argument dialog box as shown in below image. Syntax of SUMIFS Function: The Syntax of SUMIFS function is as below: =SUMIFS(sum_range, cirteria_range1, cirteria1, …) Arguments of SUMIFS Function: SUMIFS fun...
Read more

Bold, Italic and Underline Commands from Font Group

By
Image
Bold, Italic and Underline… These three commands are very basic but very important and frequently used in excel. These commands can be found under Font Group. YouTube Video: Let us have detailed description about these commands..!                                              Bold : If we want to change our text into Bold format, then we can use this command. Keyboard shortcut to use this command is Ctrl+B. In below image we have inserted a sample text in cell A1. If we click on the Bold icon from font group or use keyboard shortcut Ctrl+B, then the text from cell A1 will convert into Bold as shown in below image. Italic : If we want to convert our text into Italic format, then we can use this command. Keyboard shortcut to use this command is Ctrl+I. In below image we have inserted a sample text in cell A1. If we click on the Italic icon...
Read more

Conditional Formatting: Highlighting Entire Row in Excel

By
Image
In this blog post we will learn how to highlight entire row with the help of Conditional Formatting in excel. For this we have prepared a dummy data of 04 team members working on different tasks. We have also provided status for each task whether it is “Open” or “Closed”. We will now learn how we can highlight the entire row based on the provided status. We will highlight the entire row in green color if the status is “Closed”. Let’s follow the below steps and you will be able to highlight the entire row based on our specific criteria. 1. Select all the cells in which we wish to highlight the entire row based on specific criteria. Please Note: While selecting the cells we must exclude the header row. 2. Now, we have to click on the “Conditional Formatting” option which we can find in Styles Group under Home Tab as shown in below image. 3. Once we click on the “Conditional Formatting” option, we can see the further options will appear on our screen. We have to Click on “New Rule” ...
Read more