+ Reply to Thread
Results 1 to 5 of 5

Excel will not allow Zero values to be reformatted

  1. #1
    Registered User
    Join Date
    12-31-2014
    Location
    Las Cruces, NM
    MS-Off Ver
    2013
    Posts
    6

    Angry Excel will not allow Zero values to be reformatted

    I teach an intermediate Excel course and have a student's file that is acting.. funny. I have never seen this issue before and would like some ideas on what could cause it/how to fix it. I have attached the file.

    In cell I3:J3 she has entered the value 0. The cell will not allow the number of decimal places nor the number formatting to be changed. The Automatically Insert Decimal point option is not enabled; the worksheet is set to calculate automatically. Moreover, the same thing occurs when a 0 is entered anywhere on the sheet.

    I thought maybe it was being caused by a trailing "ghost" character, and when I nested the 0 value inside the TRIM function, the extra two decimal places went away but I was still unable to change the number format. Any ideas?!!?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,911

    Re: Excel will not allow Zero values to be reformatted

    Try this:

    1. Select I3:J3 -> Clear -> Formats

    2. Merge & Center

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Excel will not allow Zero values to be reformatted

    @ Phuocam that fixes that specific (merged) cell, but not the underlying problem. As OP says, if you enter 0 anywhere, it happens there too.

    I cannot find what the problem is, but I will push this out to the other experts, see if they can help
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Excel will not allow Zero values to be reformatted

    There's a conditional formatting rule that is causing it.
    Last edited by AliGW; 10-29-2016 at 02:21 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    12-31-2014
    Location
    Las Cruces, NM
    MS-Off Ver
    2013
    Posts
    6

    Re: Excel will not allow Zero values to be reformatted

    Thank you everyone! Conditional formatting.. of course! :P

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 10-27-2016, 07:33 PM
  2. reformatted multiple series to use the same marker symbol
    By BlueDog45 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2016, 11:10 AM
  3. Replies: 1
    Last Post: 05-14-2014, 12:52 AM
  4. [SOLVED] excel vba filter values to listbox from combobox and save edit values in textbox
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2013, 08:42 AM
  5. Pulling Data Based on Name to New Reformatted Sheet
    By ngor3431 in forum Excel General
    Replies: 4
    Last Post: 05-28-2012, 04:56 AM
  6. Replies: 5
    Last Post: 01-18-2012, 03:32 PM
  7. Replies: 5
    Last Post: 04-27-2011, 02:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1