Home » Cells

Why Is Excel Changing The Last Digit Of My Number To Zero?

by

Hello Excellers, welcome to another blog Excel post in my #ExcelTips 2021 series. Today let’s look at why Excel Is changing the last digit of a number to zero. That’s correct Excel changes the last digit to zero sometimes. If you have ever come across this, it can be confusing and damn frustrating. You type a long number into Excel, and weirdly anything past digit 15 appears as zeros. So if you are entering a credit card number, which is usually 16 digits, then the last is displayed as a zero.

Excel Macro

Because Excel only stores 15 significant digits in a number, Excel changes any remaining digits to zeros. As the formatting of the cell is numbers, Excel interprets the numbers as being intended for calculation. Okay, so can we reformat them as Text or something else? Well, no, once entered, you cannot just reformat. Instead, you will need to format new cells as Text then enter the numbers again.

The Solution To Solve Why Excel Is Changing The Last Digit To Zero.

Hmmm, so is there anything else we can do to avoid having to re enter all of those numbers?…there are a few things.

Solution 1. Leading Apostrophe.

The first solution is to insert a leading ‘in front of you number. So, for example ‘2560089996999987 would display correctly. You can see in the screen grab below without the apostrphoe Excel changes the digit to zero.

Excel change last digit to zero

Solution 2. Using The Text Import Wizard.

Now, onto the second solution. If you are importing your data from a .rtf or.csv file, when the text import wizard appears, scroll to your field containing the offending ‘longer than 15 digits’ number. Click on it, then in the upper right hand section of the window, change the field of column type from ‘General’ to ‘Text’.

text import wizard to prevent excel changing last digit to zero

Note. The Text Import Wizard is a legacy function. If you are using esrlier version of Execl then you will be ok. If you have a newer version of Excel you will see the Get & Transform Tools. So since In Excel 365 and 2016 the “Text Import Wizard" has been removed. But, if you want to use the legacy version of Text Import Wizard you can get it back.

 

Restore The Text Import Wizard.

Follow the steps below to restore the original text Imprt Wizard.

  • FIle | Options | Data
  • Select Show Legacy Data Import Wizards
  • Hit Ok

 

Excel changes last digit to zero

 

You can now go ahead and use the Legacy Data Import feature to comportably import your data the way you want to.

why is Excel changing last digit to zero

Next select your file to import. After selection hit Import.

Excel last digit changes to Zero

 

I always make sure to check out any data I am importing to see if this issue may occur. It is easier to catch the issue at import stage than having to either get a leading apostrophe or re type. Who wants that right?.

More Excel Tips.

 

Enter Data Into Excel In A Specific Order

by

Have you ever experienced data entry in a workbook that jumps around the place, or you want users to enter data in a specific order?. Excel CAN force the sequence of cells that you want users to select.

This solution relies on defining a named range which essentially is a sequence of cells that you specify.

See below as an example of the sequence of cells that I want users to type into Excel.

 

So let’s give this a go.

  • Click in the cell that is second in your required sequence
  • Hold down the CTRL key and click the cell sequence 3, carry on and select the cells in the correct order
  • When you have reached your last cell in the sequence, click the first cell in the sequence
  • Click the cursor into the Name Box (the area to the left of the bar that shows the cells addresses)
  • Name this area of cells, i.e a named range in this example I am using MyDataSequence – then hit Enter and the name box will return to showing a normal cell reference
  • Save your file

 

 

  • When you need to get a user to enter data into Excel in that order just select your named range MyDataSequence.
  • Begin typing and press enter
  • Excel will jump to the next cell sequence ready for data entry
As you can see the named range is just a sequence of cells. If you want to view the details of the named range you created:-
  • Formula Tab
  • Defined Names
  • Name Manager

 

 

 

 

 

So, as you can see the named range is just a sequence of cells, that we selected earlier in the exercise. Give it a go.

 

 

 

 

 

 

 

Sorting Excel Data By Colour

by

Excel allows you to sort your data in many ways, but with Excel 2007 onwards we can now sort easily by the font colour or by the cell fill colour. It is easy and effective and is available as a custom sort function.

  1. Select the data you want to sort
  2. Data Tab
  3. Sort And Filter Group- Sort
  4. Use drop down menu in the Sort On Column and select to sort by either the font or cell colour
  5. Using the drop-down list in the Order column, select the color by which you want to sort. (Excel allows you to sort by any color within the range to be sorted.)
  6. Using the second (right-most) drop-down list in the Order column, specify whether the color you picked above should be on top or on bottom.
  7. If you want to additionally sort by other colors, click the Add Level button and repeat steps 4 through 6 for the additional level.
  8. Click Ok.
In my example below I have sorted my Sales by cell colour. My Sales column was originally conditionally formatted using the criteria to highlight all values over 5000. Then take this a step further by using the Sort By Colour function in Excel. Nice.

Want Even More Excel Tips?

1. Quickly Import Data Into Excel From The Internet

2.Quickie Excel Tip:- Shrink Your Excel Data

3. Create A Timestamp In Excel With Formulas

4. Reset You Last Used Cell In Excel

Quickly And Easily Import Data To Excel From The Internet.

by

If you have ever used data from Yahoo or Google in a data table have you retyped it in, or tried to copy and paste it? It either takes too long or the formatting appears all over the place. Google is an excellent source of information you can analyze using Excel, I just do not want to retype it or copy it!

You don’t need to to, you can import the data into Excel as long as the data is formatted on the web page as a table. Here’s how to do it:-

  • Open an Excel workbook
  • Data Tab
  • Get External Data
  • From Web
  • Type in the URL of the web page that has your table of information
  • Data tables will have a yellow arrow next to them
  • Click on the yellow arrow of the data table you want to import

 

 

 

 

 

 

 

 

 

 

  • Next Hit Import
  • Choose the position of the data table in a new or existing workbook
  • Hit Ok

There you go your data table is imported, no fuss, no mess and no re typing!

This is really handy for business statistics for sales trends or any published figures that are readily available on the Internet.

Give it a go!

 

 

 

 

 

 

 

 

 

 

More Excel Tips

1. Create A Timestamp In Excel With Formulas

2. Hide Your Formulas For Prying Eyes

3. Remove Personal Information From Excel 2007 Workbook

 

 

Format Individual Characters In An Excel Cell

by

In Excel you aren’t restricted to having your whole cell full of text formatted in the same way. You can have individual formatting on different characters within a cell. (This only works with cells that have text only!) . This is a nice way to emphasize a point you are trying to make or jazz up a workbook- why not??? Here is how

  • Select the cells you want to format in the usual way
  • Use the usual formatting controls to format your individual characters

Here’s a couple of examples:-

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar