Bug - Deleting custom number format used in conditional format corrupts Excel file and cannot save

%3CLINGO-SUB%20id%3D%22lingo-sub-2615306%22%20slang%3D%22en-US%22%3EBug%20-%20Deleting%20custom%20number%20format%20used%20in%20conditional%20format%20corrupts%20Excel%20file%20and%20cannot%20save%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2615306%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20I%20have%20found%20a%20bug%20in%20Excel%20that%20has%20existed%20for%20some%20time%20and%20still%20exists%20in%20current%20version%20of%20Excel%202019.%26nbsp%3B%20This%20can%20be%20easily%20reproduced%20in%20all%20recent%20versions%20off%20Excel%2C%20across%20versions%20of%20Windows.%26nbsp%3B%20I%20have%20attempted%20to%20report%20via%20technical%20MS%20support%2C%20but%20have%20been%20unable%20to%20get%20any%20traction.%26nbsp%3B%20Can%20anybody%20confirm%20this%20bug%20and%20duplicate%3F%26nbsp%3B%20If%20so%2C%20what%20is%20the%20best%20way%20to%20get%20this%20logged%20with%20Microsoft%20and%20get%20a%20fix%20in%20future%20versions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESteps%20to%20recreate%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Open%20blank%2Fnew%20Excel%20workbook%20in%20Desktop%20App.%26nbsp%3B%20I%20happen%20to%20be%20using%20Excel%20build%2013801.20808.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Pick%20a%20cell%2C%20for%20example%2C%20A1%2C%20right%20click%2C%20Format%20Cells%2C%20and%20then%20click%20custom%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3)%20Create%20a%20new%20custom%20format.%26nbsp%3B%20For%20example%2C%20enter%20%22A%22%20(including%20quotes)%20as%20a%20sample%20format%20-%20the%20format%20doesn't%20really%20matter%20as%20long%20as%20it%20is%20a%20new%20custom%20format.%26nbsp%3B%20Click%20OK%20to%20save%20the%20custom%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4)%20Note%20-%20After%20doing%20this%2C%20Excel%20creates%202%20new%20custom%20formats%20that%20appear%20in%20the%20list%20of%20formats%20%22after%22%20the%20newly%20created%20one.%26nbsp%3B%20Go%20back%20into%20custom%20formats%20and%20create%20a%20%22B%22%20custom%20format%20so%20that%20the%20%22B%22%20custom%20format%20is%20last%20in%20the%20list.%26nbsp%3B%20Alternately%2C%20you%20can%20delete%20the%20two%20additional%20custom%20formats%20added%20by%20excel%20so%20yours%20is%20last%20in%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22madsenkevin_1-1628168896681.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F300763i7601F703EB5CC954%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22madsenkevin_1-1628168896681.png%22%20alt%3D%22madsenkevin_1-1628168896681.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E5)%20Now%2C%20we%20are%20ready%20to%20corrupt%20the%20Excel%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E6)%20Enter%20some%20values%2C%20such%20as%201%2C%202%2C%203%2C%204%2C%205%20in%20some%20cells.%26nbsp%3B%20Select%20one%20of%20the%20cells%2C%20such%20as%20the%20one%20with%20%222%22%20in%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E7)%20Create%20a%20conditional%20format%20using%20the%20custom%20format%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Ba)%20From%20the%20menu%20select%20Home%2C%20Conditional%20Formatting%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bb)%20New%20Rule%2C%20Use%20a%20Formula%20to%20determine%20which%20cells%20to%20format%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bc)%20for%20the%20formula%20enter%20%22%3D2%22%20(no%20quotes)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bd)%20click%20the%20format%20button%2C%20select%20Number%20tab%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Be)%20select%20%22Custom%22%2C%20then%20select%20the%20last%20custom%20format%20in%20the%20list%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bf)%20Click%20Ok%20to%20apply%20conditional%20format%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E8)At%20this%20point%2C%20you%20should%20have%20a%20custom%20format%20that%20is%20last%20in%20the%20list%20of%20custom%20formats.%26nbsp%3B%20The%20custom%20format%20is%20used%20in%20a%20conditional%20format%20applied%20to%20a%20cell%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E9)%20To%20corrupt%20the%20worksheet%2C%20simply%20go%20to%20edit%20the%20custom%20formats%2C%20find%20your%20new%20custom%20format%2C%20and%20delete%20the%20custom%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E10)%20Try%20to%20save%20the%20document%2C%20you%20will%20get%20this%20error%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22madsenkevin_2-1628169392190.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F300768iF603663E7AE8A61E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22madsenkevin_2-1628169392190.png%22%20alt%3D%22madsenkevin_2-1628169392190.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20this%20point%2C%20your%20file%20is%20corrupt.%26nbsp%3B%20My%20experience%20is%20that%20even%20if%20you%20ask%20Excel%20to%20repair%2C%20the%20file%20will%20still%20be%20corrupt.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20diagnosis%20is%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20applying%20conditional%20formats%2C%20the%20programming%20logic%20used%20in%20Excel%20uses%20an%20%22index%20number%22%20of%20the%20format%20used.%26nbsp%3B%20For%20example%2C%20In%20my%20spreadsheet%2C%20my%20custom%20format%20%22B%22%20was%2040th%20in%20the%20list%20of%20custom%20formats.%26nbsp%3B%20When%20the%20conditional%20format%20was%20created%2C%20behind%20the%20scene%2C%20it%20says%20%22use%20the%2040th%22%20custom%20format%20in%20the%20list.%26nbsp%3B%20If%20that%2040th%20custom%20format%20is%20deleted%2C%20the%20conditional%20formatting%20still%20says%20%22use%20the%2040th%22%20custom%20format.%26nbsp%3B%20BUT%2C%20it%20no%20longer%20exists%20after%20I%20have%20deleted.%26nbsp%3B%20Thus%2C%20it%20creates%20an%20error%20that%20cannot%20be%20handled%20and%20you%20cannot%20save%20the%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20corrected%20-%20go%20back%20in%20and%20add%20a%20new%20%2240th%22%20custom%20format%20and%20all%20of%20a%20sudden%20you%20can%20save%20the%20file!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEven%20more%20interesting%2C%20if%20you%20use%20a%20conditional%20format%20for%20lets%20say%20the%2020th%20custom%20format%20in%20the%20list%20and%20then%20delete%20the%2010th%20custom%20format%2C%20the%20list%20%22moves%20up%22.%26nbsp%3B%20What%20was%20the%2021st%20custom%20format%20is%20now%20the%2020th%20custom%20format.%26nbsp%3B%20The%20conditional%20format%20set%20does%20not%20change%2C%20resulting%20in%20the%20format%20using%20the%20NEW%2020th%20custom%20number%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20fix%20to%20this%20needs%20to%20include%20a%20routine%20such%20that%20if%20a%20custom%20number%20format%20is%20deleted%2C%20it%20needs%20to%20then%20automatically%20review%20all%20conditional%20formats%20that%20use%20that%20custom%20number%20format%20or%20any%20later%20in%20the%20list%20of%20custom%20number%20formats%20and%20update%20the%20%22index%22%20of%20which%20custom%20number%20format%20is%20being%20applied.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20all%20makes%20sense%20and%20was%20described%20clearly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anybody%20else%20reproduce%20this%3F%26nbsp%3B%20I%20have%20been%20able%20to%20reproduce%20this%20on%20any%20version%20of%20Excel%202019%20and%20Windows%2010%20that%20I%20have%20tried.%26nbsp%3B%20I%20think%20this%20is%20a%20fundamental%20bug%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F41501%22%20target%3D%22_blank%22%3E%40microsoft%3C%2FA%3E-%20Can%20you%20log%20this%20as%20a%20bug%20and%20get%20it%20fixed%3F%3C%2FP%3E%3CP%3E8)Click%20the%20%22Format%20buttom%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2615306%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I think I have found a bug in Excel that has existed for some time and still exists in current version of Excel 2019.  This can be easily reproduced in all recent versions off Excel, across versions of Windows.  I have attempted to report via technical MS support, but have been unable to get any traction.  Can anybody confirm this bug and duplicate?  If so, what is the best way to get this logged with Microsoft and get a fix in future versions?

 

Steps to recreate:

 

1) Open blank/new Excel workbook in Desktop App.  I happen to be using Excel build 13801.20808.

 

2) Pick a cell, for example, A1, right click, Format Cells, and then click custom

 

3) Create a new custom format.  For example, enter "A" (including quotes) as a sample format - the format doesn't really matter as long as it is a new custom format.  Click OK to save the custom format.

 

4) Note - After doing this, Excel creates 2 new custom formats that appear in the list of formats "after" the newly created one.  Go back into custom formats and create a "B" custom format so that the "B" custom format is last in the list.  Alternately, you can delete the two additional custom formats added by excel so yours is last in the list.

 

madsenkevin_1-1628168896681.png

 

5) Now, we are ready to corrupt the Excel document.

 

6) Enter some values, such as 1, 2, 3, 4, 5 in some cells.  Select one of the cells, such as the one with "2" in it.  

 

7) Create a conditional format using the custom format

     a) From the menu select Home, Conditional Formatting

     b) New Rule, Use a Formula to determine which cells to format

     c) for the formula enter "=2" (no quotes)

     d) click the format button, select Number tab

     e) select "Custom", then select the last custom format in the list

     f) Click Ok to apply conditional format

 

At this point, you should have a custom format that is last in the list of custom formats.  The custom format is used in a conditional format applied to a cell

 

9) To corrupt the worksheet, simply go to edit the custom formats, find your new custom format, and delete the custom format.

 

10) Try to save the document, you will get this error:

madsenkevin_2-1628169392190.png

 

At this point, your file is corrupt.  My experience is that even if you ask Excel to repair, the file will still be corrupt.

 

My diagnosis is this:

 

When applying conditional formats, the programming logic used in Excel uses an "index number" of the format used.  For example, In my spreadsheet, my custom format "B" was 40th in the list of custom formats.  When the conditional format was created, behind the scene, it says "use the 40th" custom format in the list.  If that 40th custom format is deleted, the conditional formatting still says "use the 40th" custom format.  BUT, it no longer exists after I have deleted.  Thus, it creates an error that cannot be handled and you cannot save the file.

 

This can be corrected - go back in and add a new "40th" custom format and all of a sudden you can save the file!

 

Even more interesting, if you use a conditional format for lets say the 20th custom format in the list and then delete the 10th custom format, the list "moves up".  What was the 21st custom format is now the 20th custom format.  The conditional format set does not change, resulting in the format using the NEW 20th custom number format.

 

The fix to this needs to include a routine such that if a custom number format is deleted, it needs to then automatically review all conditional formats that use that custom number format or any later in the list of custom number formats and update the "index" of which custom number format is being applied.

 

Hope this all makes sense and was described clearly.

 

Can anybody else reproduce this?  I have been able to reproduce this on any version of Excel 2019 and Windows 10 that I have tried.  I think this is a fundamental bug in Excel.

 

@microsoft- Can you log this as a bug and get it fixed?

Click the "Format buttom

 

 

4) 

 

 

1 Reply

Your best bet is to replicate the issue, write out everything you wrote in your message above into the spreadsheet, making sure all text and the screenshots are visible on the same window (no scrolling required) and then click File, Feedback, "I don't like something" and fill out the form, checking the screenshot box..