MS Excel Tips and Tricks – Change the Cell format from Text to Number

When you use Microsoft Excel, You will sometimes find that the numbers are not calculated properly. This is mainly because of the cell format.

Excel accepts Text and the Number input, it has the great capability to interpret between two. All the Excel cells are in General format, when you start working on any Excel sheet It automatically allots the format to each cell based on the characters inside.

If you see any cells containing the numbers but are aligned left side, you need to understand that the cell format is in Text mode. Whenever you see the numbers in any cells arranged in right side, The cell format is in Number.

Excell Cells in Text format

The calculation only works if you have the cell in numbers format

You can change the format from Text to Number by following the method below

Click on any empty cell and right click

Excel Format Cell Menu

Choose cell formatting

You will see a menu opening as seen below

Excel Format Cell Options

Under the Number Tab, Click number as shown in the image

Choose the decimal you want to have, make it ‘0’ if you do not want any decimal

Click OK to save

Now type ‘1’ in the cell that you have changed the format, Press enter

Then copy the cell

Copy Cell

Now select all the cells that you want to be changed to Number formatting

Then right click and choose Paste Special

Excel Cell Paste Special

Excel will give you Paste special Options as seen below

Excel Cell Paste Special 1

Choose Multiply

Then click OK, Do not change any other values

Now you will see all the selected cell formatting changed to Numbers and Excel automatically aligns the numbers to the right side

Excel Number Formatting

Now your calculation should work

If you have any questions or suggestions, please post a comment

Read More Tips and Tricks from our website

MS Excel Tips and Tricks – Remove or Add Fixed Decimal Points

Sometimes you will see Microsoft excel sheet adds the decimal points with all the numbers that you enter. You can remove that from happening.

If you want to add the decimal points in every number you enter you can also do that.

  • Open the Excel Sheet
  • Go to Tools
  • Click Options

Excel Decimal Options

[ad#post-link]

  • Click the Edit tab
  • As highlighted in the above image, you will see the fixed decimal option
  • If you want the Excel to automatically enter the decimal points check the Fixed Decimal option, also you can choose how many decimal points that you want to have
  • If you want to remove the automatic decimal points, un check the Fixed decimal points
  • Click Ok to save the settings
  • Now any number you enter you will see the changes

This is applicable to Microsoft Office 2003, Excel 2003 and Excel 2007.

Read more Excel Tips and Tricks from our website

[ad#post-link]