I’m sorry if I have to post my suggestion here as I couldn’t find any means to post a screenshot in the website http://excel.uservoice.com/:
That in the event of making file amendments, like inserting rows/columns, that the existing conditional formats will adjust automatically in a way that the formula wouldn’t be much distorted, like what it does now whenever I insert a column within a range that is conditionally formatted, and using the command paste (and not further choosing pasting via formulas and number formatting), the system would distort the rules, such that if the conditional format captures a range, and you insert a column in between that range and paste the copied column via insert copied cells or any other simple pasting rules, the conditional formatting rule then would be distorted such that a new rule would be created, and the existing rule would show a distorted ‘Applies to’ range, that it would create a range ending before the inserted column, then a new range to depict the column after the inserted column.
For a clearer picture, this is what I’m trying to say:
Here is the range where the conditional format is applied to, before inserting a column, and copying the previous column and pasting it on the added column via simple pasting (instead of further selecting paste via formulas and number formatting.
And this is the distorted range, which has created a new rule for the inserted cell, and the existing rule where the range would end before the inserted column, and an added range after the inserted column