Excel mangles conditional formatting



  • Periodically, I have to check the conditional formatting in my Excel worksheets, because inevitably, this happens:

    [IMG]http://i.imgur.com/UzDYypr.png[/IMG]

    I changed it (back) to this:

    [IMG]http://i.imgur.com/dL50mqz.png[/IMG]

    Thanks Excel!

    (And now if they'd just let me resize the tiny fucking little dialog box so that I wouldn't have to paste together eleventy million screenshots to fit all of the formatting rules in a single image)



  • I am really fail to understand your problem.



  • @Nagesh said:

    I am really fail to understand your problem.

    At least I wasn't the only one. I have no idea what we're looking at here/



  • Yes, if you insert rows, Excel turns each conditional formatting rule into three rules, one for the region above the inserted row, one for the region below the inserted row, and one for the inserted row itself. (I assume it does the same thing if you insert columns, although I don't think I have actually run into that.) This is nothing new. Ho, hum. Nothing to see here; move along.

    Why does Excel do this? I don't know, but there must be a good reason for making the user's formatting more difficult to maintain and making the file bigger. Since Excel is the pinnacle of software perfection, it must be The Right Way.



  • The problem was that the sum of all the rules shown in the first image is exactly the same as the rules shown in the second image. The problem is that I wanted to change one of the rules. The problem is that I didn't want to change ten copies of the same rule because Excel is too fucking stupid to realise that inserting, deleting, or moving entire rows shouldn't affect conditional formatting rules that were applied to entire columns.



  • @mikeTheLiar said:

    @Nagesh said:
    I am really fail to understand your problem.
    At least I wasn't the only one. I have no idea what we're looking at here.

    In excel you can set up formatting rules that get applied to various fields.  The top example was the autogenerated formatting rules that excel did, the bottom a simplified set that gets the same output (or at least that's what I think it is).



  • @locallunatic said:

    In excel you can set up formatting rules that get applied to various fields.  The top example was the autogenerated formatting rules that excel did, the bottom a simplified set that gets the same output (or at least that's what I think it is).

    Sort of. The bottom image is the conditional formatting rules that I originally set up. The top image is what happened to them after inserting, deleting, and moving rows. The bottom image is also what I changed them back to.



  • @HardwareGeek said:

    Yes, if you insert rows, Excel turns each conditional formatting rule into three rules, one for the region above the inserted row, one for the region below the inserted row, and one for the inserted row itself. (I assume it does the same thing if you insert columns, although I don't think I have actually run into that.) This is nothing new. Ho, hum. Nothing to see here; move along.

    Why does Excel do this? I don't know, but there must be a good reason for making the user's formatting more difficult to maintain and making the file bigger. Since Excel is the pinnacle of software perfection, it must be The Right Way.

    Actually, the reason is that the new row is inserted with no formats set, and then Excel copies the formatting from above. So inserting the row breaks the rule into two rules, and copying the formatting from the row above creates the third rule.

    The problem is that Excel is too dumb to realise that the 3 rules are exactly the same apart from having different ranges, which should be unioned to create a single rule again. (And even if Excel couldn't or shouldn't figure out that two rules are identical and combine them in the general case, it should know that inserting a row will always result in this and have a special case to handle this so it won't happen.)



  • @anotherusername said:

    @HardwareGeek said:
    ... Excel turns each conditional formatting rule into three rules...
    ... So inserting the row breaks the rule into two rules, and copying the formatting from the row above creates the third rule....
    Thanks for the additional detail.@anotherusername said:
    ... it should know that inserting a row will always result in this and have a special case to handle this so it won't happen.)
    Agreed, 1000%.



  • @anotherusername said:

    Actually, the reason is that the new row is inserted with no formats set, and then Excel copies the formatting from above. So inserting the row breaks the rule into two rules, and copying the formatting from the row above creates the third rule.

    I was just testing this as it sounds like a really silly way to do things and a copied row does bring it's formatting with it.  Now excel doesn't recognize that the new row's formatting matches the pattern that got split (and thus should be still one rule) but it's not quite as dumb as this part of your post:
    @anotherusername said:

    (And even if Excel couldn't or shouldn't figure out that two rules are identical and combine them in the general case, it should know that inserting a row will always result in this and have a special case to handle this so it won't happen.)



  • @locallunatic said:

    @anotherusername said:

    Actually, the reason is that the new row is inserted with no formats set, and then Excel copies the formatting from above. So inserting the row breaks the rule into two rules, and copying the formatting from the row above creates the third rule.

    I was just testing this as it sounds like a really silly way to do things and a copied row does bring it's formatting with it.  Now excel doesn't recognize that the new row's formatting matches the pattern that got split (and thus should be still one rule) but it's not quite as dumb as this part of your post:
    @anotherusername said:

    (And even if Excel couldn't or shouldn't figure out that two rules are identical and combine them in the general case, it should know that inserting a row will always result in this and have a special case to handle this so it won't happen.)

    I haven't really been able to figure out when it does it. Simply creating a fresh worksheet with a rule that applies to entire columns and then inserting, deleting, or moving rows doesn't affect the conditional formatting rule at all. It only seems to happen when you're not watching.



  • How dare you commit blasphemy against the almighty Excel! The authorities have been notified.



  • @mott555 said:

    How dare you commit blasphemy against the almighty Excel! The authorities have been notified.

    I'm sure they'll come for me, probably right after you get that dollar Bill Gates promised you for forwarding the chain e-mail he sent.



  • @anotherusername said:

    I haven't really been able to figure out when it does it. Simply creating a fresh worksheet with a rule that applies to entire columns and then inserting, deleting, or moving rows doesn't affect the conditional formatting rule at all. It only seems to happen when you're not watching.

    If you insert a row or a column it will keep the formatting.

    When you copy a cell from somewhere else and paste it it will also update the formatting with the formatting of the orignal cell.

    One way to avoid this should be by using the 'Paste Special' function and only paste the formula/values and not the formatting/layout.



  • @anotherusername said:

    I'm sure they'll come for me, probably right after you get that dollar Bill Gates promised you for forwarding the chain e-mail he sent.
    Anyone know if he got back at trying to install Windoes 8.1?



  • Someone get this guy a vb script to automatically merge the entries back again.


  • Trolleybus Mechanic

     If you think that's bad, you shoud see what it does to the Undo stack on save.



  • @henke37 said:

    Someone get this guy a vb script to automatically merge the entries back again.

    Now you have tw... er, fuck. I can't count that high



  • @Lorne Kates said:

     If you think that's bad, you shoud see what it does to the Undo stack on save.

    Pfft, if you think that's bad, you should see the newest Office 2013 "animation" features. That's right, every time you change something attached to, for example, a plot in Excel, it doesn't reflect automatically, but you have to wait for an animation of a bar going up to complete. It also makes Word fucking unusable, because there's a very noticeable delay between typing the words and actually seeing them on screen. And arrow keys are totally broken.



  • @Maciejasjmj said:

    It also makes Word fucking unusable,

    It makes the text appearing on-screen less distracting to help you keep focused on whatever material you're typing from. And per always, if you don't like it, JUST TURN IT THE FUCK OFF INSTEAD OF WHINING LIKE A BABY. It's in Options -> Advanced -> General

    @Maciejasjmj said:

    And arrow keys are totally broken.

    Liar.



  • @blakeyrat said:

    @Maciejasjmj said:
    It also makes Word fucking unusable,

    And per always, if you don't like it, JUST TURN IT THE FUCK OFF INSTEAD OF WHINING LIKE A BABY.

    Liar.

    But... but I thought that's what this forum was about... :(

    Also, it's indeed there in Word, but for some reason not in Excel.


  • Trolleybus Mechanic

    @Maciejasjmj said:

    fft, if you think that's bad, you should see the newest Office 2013 "animation" features. That's right, every time you change something attached to, for example, a plot in Excel, it doesn't reflect automatically, but you have to wait for an animation of a bar going up to complete. It also makes Word fucking unusable, because there's a very noticeable delay between typing the words and actually seeing them on screen.

    Seen it. Just started a new job, got a new machine with Office 2013. For those of you blessed not to have seen this yet, I want you to type a word into any text input in any program installed on any system. Seriously, ANY textbox, any program, any system (except, of course, for Word 2013).

    Type a letter. What happens? Simultaniously, the cursor jumps to the right by the width of the character, and the character appears on the screen. It's nearly instantanous. It just happens. You don't see it. You don't need to see it. (Yes, I'm assuming you're using left-to-right characters, that you aren't at the end of a line, etc).

    Now do the same thing in Word 2013. What happens?  The cursor slooooowly slides to the right, revealing the letter you typed like a shitty transition in someone's home movie.  Type a whole bunch of letters.  Well, obviously that cursor can't accelerate and decelerate between each letter, so the animation speed adjusts. Sortof. Kindof. As well as any animation trying to show something non-deterministic can be.

    At once point, you had your cursor be nearly invisible (except for the very subtle hint to your eyeballs as to where it was). Simple, clean, not distracting. Words appeared exactly as you fucking expected them to.  Now?  WHO KNOWS!  Maybe your cursor will be at the end of the line by the time you're done typing. Maybe it will speed and slow like a spastic car. Maybe something is pegging your graphic card in the background, and you'll get whatever fucking behaviour you'll get. Can you tell where your cursor will end up? NOPE. When it will end there? GO FUCK YOURSELF.

    What benefit does it serve?  {crickets}

    How do you disable it?  Well, only by a registry hack, obviously: http://lifehacker.com/turn-off-office-2013s-distracting-typing-animation-498701966

     



  • @Lorne Kates said:

    @Maciejasjmj said:

    fft, if you think that's bad, you should see the newest Office 2013 "animation" features. That's right, every time you change something attached to, for example, a plot in Excel, it doesn't reflect automatically, but you have to wait for an animation of a bar going up to complete. It also makes Word fucking unusable, because there's a very noticeable delay between typing the words and actually seeing them on screen.

    Seen it. Just started a new job, got a new machine with Office 2013. For those of you blessed not to have seen this yet, I want you to type a word into any text input in any program installed on any system. Seriously, ANY textbox, any program, any system (except, of course, for Word 2013).

    Type a letter. What happens? Simultaniously, the cursor jumps to the right by the width of the character, and the character appears on the screen. It's nearly instantanous. It just happens. You don't see it. You don't need to see it. (Yes, I'm assuming you're using left-to-right characters, that you aren't at the end of a line, etc).

    Now do the same thing in Word 2013. What happens?  The cursor slooooowly slides to the right, revealing the letter you typed like a shitty transition in someone's home movie.  Type a whole bunch of letters.  Well, obviously that cursor can't accelerate and decelerate between each letter, so the animation speed adjusts. Sortof. Kindof. As well as any animation trying to show something non-deterministic can be.

    At once point, you had your cursor be nearly invisible (except for the very subtle hint to your eyeballs as to where it was). Simple, clean, not distracting. Words appeared exactly as you fucking expected them to.  Now?  WHO KNOWS!  Maybe your cursor will be at the end of the line by the time you're done typing. Maybe it will speed and slow like a spastic car. Maybe something is pegging your graphic card in the background, and you'll get whatever fucking behaviour you'll get. Can you tell where your cursor will end up? NOPE. When it will end there? GO FUCK YOURSELF.

    What benefit does it serve?  {crickets}

    How do you disable it?  Well, only by a registry hack, obviously: http://lifehacker.com/turn-off-office-2013s-distracting-typing-animation-498701966

     


    I remember the presentations people made in elementary school with the abuse of transitions. Mostly it was "animate every letter with some really distracting effect and also make a gunshot sound when each one appears on the screen". Powerpoint has had this problem since transitions were implemented. It's just that it used to require a dumb user to activate.



  • @Ben L. said:

    It's just that it used to require a dumb user to activate.
    Now it only requires a sysadmin dumb enough to think installing Office 2013 is in any way a step forward.



  • I just started Word 2013 and slammed the keyboard as fast as I could, and never managed to see the cursor lag more than a single letter behind. I honestly don't know what bothers you so much. Maybe it's just a problem with your computer, like when the Surface first came out and couldn't cope with the animations.



  • @anonymous234 said:

    like when the Surface first came out and couldn't cope with the anime.
     

    Blakey's kawaii secret.



  • @RangerNS said:

    @henke37 said:
    Someone get this guy a vb script to automatically merge the entries back again.

    Now you have tw... er, fuck. I can't count that high

     

    Sorry, you must count to at least 2 before excel will help you auto-fill the next cells. If you go only up to 1, it'll just repeat the 1 all over the place.

     



  • @Maciejasjmj said:

    Also, it's indeed there in Word, but for some reason not in Excel.
     

    Microsoft is fine tunning each one of their software for its public. Windows got the fisherprice tiles, Word gets animated letters, but Excel is meant to help with real work. (And yes, there is a point here about VS2013.)

    Some people say the software is becomming unusable, that's false, it's just getting more appealing to the actual audience MS measured. (In a related tought, I must find a browser that isn't concerned about grumpy cats.)

     



  • @Mcoder said:

    (In a related tought, I must find a browser that isn't concerned about grumpy cats.)



  • @Mcoder said:

    Microsoft is fine tunning each one of their software for its public. Windows got the fisherprice tiles, Word gets animated letters, but Excel is meant to help with real work.

    And that's why you can disable the animations easily in Word, but not in Excel. Makes sense. Also, why would you even need animations in Word/Excel unless you're doing some hot live spreadsheet action?



  • @Maciejasjmj said:

    Also, why would you even need animations in Word/Excel unless you're doing some hot live spreadsheet action?

    What other kind of work people do on Excel? Maybe you are not at the target audience...



  • What version of Excel are you using? 2007 I'd guess? This probably doesn't help much, but it looks like the problem doesn't exist in Excel 2013.

    Something else you could try is formatting your worksheet as a table, and see if that makes a difference.



  • @LoremIpsumDolorSitAmet said:

    What version of Excel are you using? 2007 I'd guess? This probably doesn't help much, but it looks like the problem doesn't exist in Excel 2013.
    No, the other one (2010).



  • @Mcoder said:

    @Maciejasjmj said:

    Also, why would you even need animations in Word/Excel unless you're doing some hot live spreadsheet action?

    What other kind of work people do on Excel? Maybe you are not at the target audience...

    Used to use it to draw flowcharts.  Why do you ask?

     



  • So I happened to look at the conditional formatting rules in another worksheet (not even the entire workbook -- just one sheet in it)...

    What was originally, and should've still been, these 5 simple rules:

    0_1477683196992_Untitled.png

    had turned into just a few more than that:

    (bear with the 4-part image; I would've posted this as a single 579x54,711 image, but it was just too large to upload here... or to pretty much any other host that I'm aware of that allows free image hosting and hotlinking)

    0_1477683250139_Untitled.png
    0_1477683327676_Untitled.png
    0_1477683376657_Untitled.png
    0_1477683417182_Untitled.png

    1822 rules, by my count...

    So, I wonder if that's why the workbook was taking forever to load in Excel 2013? (probably also because it's an .xls file... Excel 2013 recalculates everything in .xls files when it loads them because it's the old file format; Excel 2007 doesn't recalculate everything when opening them, and had no problems opening the file).

    edit: yes, it was... after cleaning up that sheet and all the other sheets like it, the file opens almost instantly in Excel 2013.


  • Notification Spam Recipient

    You should really stop cutting/pasting cells around in that sheet.


  • Notification Spam Recipient

    @Ben-L. said in Excel mangles conditional formatting:

    "animate every letter with some really distracting effect and also make a gunshot sound when each one appears on the screen".

    :rofl: Yeah, sorry folks, I only did it once though.


  • Notification Spam Recipient

    @anotherusername Daggumit!

    At least it was a somewhat useful Necro, I got a link to how to turn off Word's pretty animations out of it!


  • area_can



  • @bb36e what?



  • @Lorne-Kates said in Excel mangles conditional formatting:

    revealing the letter you typed like a shitty transition in someone's home movie

    https://www.youtube.com/watch?v=72bUheqRE5o



  • @Ben-L. said in Excel mangles conditional formatting:

    I remember the presentations people made in elementary school with the abuse of transitions. Mostly it was "animate every letter with some really distracting effect and also make a gunshot sound when each one appears on the screen".

    MY powerpoint animations were the shit. Presentation on nuclear power? Put four atom symbols spinning around the screen. Bet you other kids didn't even know you could do that!

    Of course, they never worked at all since the school used a slightly different MS Office version.



  • @anonymous234 said in Excel mangles conditional formatting:

    Of course, they never worked at all since the school used a slightly different MS Office version.

    Ha. Yeah, that's one thing that sucked in Powerpoint. It would embed and play videos just fine... if the computer had the right codecs to play them. Converting the file into a format that would play on other computers? Ha, you wish. For that, you needed something like ffmpeg or VLC.

    IIRC I always converted them to mpeg1 video / mp3 audio... pretty much any computer had the codecs to play them then.


  • Notification Spam Recipient

    @anotherusername said in Excel mangles conditional formatting:

    IIRC I always converted them to mpeg1 video / mp3 audio... pretty much any computer had the codecs to play them then.

    Yeah. What's weird is that mpeg2 (dvds) isn't standard included.



  • @Tsaukpaetra patents.


  • Notification Spam Recipient

    @anotherusername said in Excel mangles conditional formatting:

    @Tsaukpaetra patents.

    I didn't say I didn't know why, I said it was weird. :)


  • :belt_onion:

    @Tsaukpaetra said in Excel mangles conditional formatting:

    @anotherusername said in Excel mangles conditional formatting:

    @Tsaukpaetra patents.

    I didn't say I didn't know why, I said it was weird. :)

    NO.

    IT IS NOT WEIRD.
    IT IS PERFECTLY NORMAL.
    NOTHING IS WEIRD HERE.

    posted by MPAAbot v1.12.44


Log in to reply