Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

How Do I Get Excel To Display Zeros At The End Of My Decimals?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I cannot get this stupid program to display zeros at the end of my
decimals....this sucks....any suggestions?

View Answers     

Similar Excel Tutorials

Keep Leading Zeros in Numbers in Excel - 2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...
Store Large Numbers in Excel
I will show you how to display large, even huge, numbers in Excel.  In Excel, you can't show numbers that are too ...
NOW() - Display The Current Time in Excel
This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ...
Excel Formatting for Zip Codes and Phone Numbers
Display numbers in Excel as zip/postal codes and phone numbers in order to make them easier to read and retain any ...

Helpful Excel Macros

Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Filter Data to Show the Top X Number of Items in Excel - AutoFilter
- This Excel macro filters a data set to display only the top X number of items in that data set in Excel. This means tha
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Open Microsoft PowerPoint from Excel
- This free macro will open the Microsoft PowerPoint program on your computer. You do need to have this program first. T

Similar Topics

I am having an issue with text strings that contain numbers. I can strip off the number from string but when I do excel loses the trailing zeros in the string. I need to know how many decimal places there are.

String example
1245.00 p 2 decimals
11.000 bnz 3 decimals
900.1 cv 1 decimals
89.59 cv 2decimals

The variables that do not end in zero are easy to determine. I need something that will work for both.

I was thinking some sort of pattern search. The numbers are always separated by spaces.

Hi all,

Anyone knows of a formula or combination of formulas to display a number with different decimals depending on a condition?

for example;
If condition A is met, display number with no decimals
if condition B is met, display number with 2 decimals
if condition c is met, display number with 4 decimals

help will be greatly appreciated

Thanks ----
Juan Garces

Hi there!

I got a bunch of data stored in a CSV format, where some of the values are decimal values (two decimals). When I convert this into XLS something strange happens. When the first two digits (before the . and the decimals) are from 1 to 12 the XLS document doesnt display the number, it displays it as a date. I have checked that all my cells are formatted to general, and also tried to format them to scientific with 2 decimals and number with 2 decimals, none of which helps Any idea how to fix this?

(not surprisingly) the values without decimals remains as they wore in the CSV format.

Help will be greatly appreciated!

Id like zeros to appear as -'s, and negatives as red with no decimal places.


sorts out red, brackets and decimals but how do I add in the - please?

Thank you

I am entering numbers into a cell that is not being influenced by an active macro. Formatted for number with no decimals. The last one or two zeros from the entry are being truncated. When I enter 3,700,000 the cell will lose the last two zeros and display 37,000?

I can copy the same cell from another worksheet and it will accept it and the number remains complete, but if I try and re-enter another number into it the truncation begins again. The cell is being utilized in a calculation, but it does not have a formula in it, it's just a number value.

Strangest thing I have ever seen in excel.

Thanks for you suggestions and ideas about WTF is going on?


I want to have a blank cell that will be used for input to be formatted to display a specific number of decimal places based on the information in another cell.

if a2 = 'each' then d2 should display 0 decimals
if a2 = 'grams' then d2 should display 5 decimals
is a2 = 'price' then d2 should display 2 decimals

D2 will be a blank cell and the user will be entering a numeric value into this cell. I want the decimal display to be consistent based on the information found in a2. How can this be done??
This form will be used on both Excel 2003 and Excel 2007 platforms
The windows operating system is XP Prof.

I have a sheet that is going to be loaded into another program an that program will only accept two decimals. Today the first rows of column B looks like this (I have a couple of hundred rows):


I need a macro that will round of the decimals too two so it will look like this:


I can not just change the format too two decimals since the other program will only accept my sheet if row B looks exactly like the example above.

Thanks in advance


Hi all!

I need to remove the decimals and trailing zeros from numbers in order to use them with a real time financial data stream. For example, I need to change 9.50 to 95. I've stood on my head over this one with no luck. Any help would be appreciated.


It takes 5 clicks and a scroll down in Excel 2007 to untick: "Show a zero in cells that have zero value". And this is just for the current worksheet. Is there no shortcut to stop the display of zeros? Or some way to set the default to NOT display zeros?


Using Excel 2007: I have a spreadsheet that is calculating several formulas and placing the sum in specific cells. This works well, except; because of the extensive calculations, there are sometimes 15-25 numbers after the decimal. For example, in some cases, the sum displays as 25.3654896523658

I know that I can FORMAT the cell to display a specified number of decimals, however, how do I format the FORMULA to only calculate to TWO decimals. In other words, how do I direct the formula to round off to two decimals in the sum so the above example would read 25.36 in the formula bar.

Thank you in advance for the input.


Hello all,

Here is my situation. I am trying to create a list and I am running into some trouble. For example, I am typing the numbers in A1, A2, A3.....all the way down to 1000. I want it too look like this: 1, 1, 1, 2, 2, 2, 3, 3, 3, ......1000, 1000, 1000.

I tpye the first 9 in to set a forumula - (A1 -A9) 1, 1, 1, 2, 2, 2, 3, 3, 3

I then highlight that and drag it all the way down. It then berings up decimals in all of the numbers. When I do the normal format, set decimals, etc...., the decimals leave the cell but are still up in the bar. When I mail merge it to my word doc, the decimals appear. This is probably a stupid and easy thing to get rid of but I am at a loss. By now I have simply taken the time to type my numbers by hand but since I will do this a few times, I am hoping for a quicker way.

Hi All

Some of my data begins with zeros, but I cannot see how to display them ie:

I enter 0001234, which displays as 1234 when I wish it to display as 0001234. I can do this by changing cell properties to text, but I wish it to be a number.

Can this be done?

Many thanks Simon

Here's my problem. I need a formula to take the decimals out of a column but keep ending zeros. I have tried find/replace & several formulas but nothing seems to be working. Here is an example of the numbers in the column and the end result I need. Thanks!!!

9.66 966
0.50 050
13.09 1309
309.98 30998

I am trying to combine and display 3 cells. Cell 2 is variable and based off of another input. The last cell is a three digit alpha numeric sequence. The problem I am having is that the final display is dropping the zeros off the front if there are any.

2 JG
3 001
How can I get it to display 005 instead of 5 once it is combined with the other two cells?

2 JG
3 001
Any suggestions would be most appreciated. Thank you.

Values can be decimal (users should be able to enter as many decimals as excel allows. The sheet should display the % value with 1 decimal rounded. However, if you click on the cell, you should be able to see all the decimals rounded to 9) Example, user enters 85.678, display would be 85.7% and same would be true once user submits the value and when it is again retrieved from database

One of my queries computes age as follows:

Age: (Date()-[Birthdate])/365

The result, however, displays the decimal part of the computation. I want the result to be the age without decimals. I do not want it rounded up, which is what happens when I set the number of decimal places as 0.

Any suggestions?


I have a sheet that uses an ID number of 11 digits that has 3 leading zeros.

I want to create a new ID for each entry on a new line that is identical to the first entry +1. That is easy to do but the three leading zeros disappear when I apply the formula.

The original number had to be converted to text to display the leading zeros and the formula that I use =A1+1 will not work on the text formated number. Any suggestions or help would be greatly appreciated.

I have a CSV file I'm importing into Excell that contains leading zeros in a number field. I cannot get the zeros to display in Excel, but the user of the report needs to see them. Is there a way to dsiplay the leading zeros on a number field?

Good afternoon!

I have a spreadsheet in which a validation macro removes and rounds data that contains decimals within the cells;however, I noticed when I converted the spreadsheet to XML the decimals were still being captured and not rounded.

When I went back into the spreadsheet, the cell was rounded (no demimals-as intended), but in the formula bar the data maintained the decimal.

I tried formatting the cells without decimals, but it's not caught if the user cuts and pastes data.

How can I remove the decimals from the data in the formula bar?

Any suggestions?


Hi, all. On a worksheet that someone else provided (I do not have access to the original data) there are numbers displayed across several columns and rows. I need to display those numbers as percentages. When I format the cells to percent, the numbers display with two zeros before the decimal place. (I actually want zero decimal places, but even then there are still two zeros added to the number.) So, 103 becomes 10300%. 95 becomes 9500%. I don't think I've ever encountered this before and can't find a way to prevent this. Any help is appreciated!


Recently, without warning, all of my excel spreadsheets have changed so that numbers previously displayed with decimals now have commas instead of period decimals. What might have happened here!? How do I go about changing it back so all decimals are displayed with periods instead of commas?

Thanks so much,


I have a spreadsheet which has numbers with decimal thousand seperators rather than commas e.g. 178.455.136. If I do a find and replace I get 178,455,136 which is fine.

However there are some numbers e.g. 83.58 which needs to change to 83,580. I can change the number of decimal places of the original to 83.580 but when I do a find and replace it goes back to 83,58. There are also some numbers e.g. 972 which should be 972,000 or 59.3 which should be 59,300. I guess what I probably want is a way to force the original data to have the correct number of zeros at the end.

Hopefully there is a simple solution to this as i don't want to have to go through and add zeros manually.

Thanks for any help,


Is there a formula that I can create that will count the number of cells in a range with decimals. I would prefer to use conditioning format because it will hoght light the ones with decimals.


Results = 3. There were 3 cells that have decimals.

I wish to set up my phone numbers in a workbook. Many of these numbers start with zero, and I wish to display these leading zeros, but without using the apostrophe at the beginning, because this converts the information to a text string.

I am using this spreadshet as an input to my mobile phone SIM card, hence the numbers must be integers, not string text.

I would appreciate any help and suggestions

Help, I have tried to turn off the zeros in the chart options, but this is not working!!! Is there any other way to make a graph not see zeros??? I have zeros that are the result of a formula...I have tried making them "" blanks but still they get charted as ZEROS!!!!

Please help and thank you!!