Resizable Conditional Formatting dialogs

Published 03-02-2021 05:19 PM 7,245 Views
Microsoft

We’re happy to announce a highly requested improvement to the Conditional Formatting dialogs in Excel for Windows. If you're a Microsoft 365 subscriber, they’re now resizable so you can see more rules at a time, and it's easier to see the formula and range that the rules apply to. This includes the Conditional Formatting Rules Manager and New/Edit Rule dialogs.

 

That’s not all. If you're a Microsoft 365 subscriber, in Excel for Windows and coming soon to Excel for Mac, there’s a new button so you can duplicate a selected conditional formatting rule.

Animation showing the Conditional Formatting Rules Manager dialog being resized.Animation showing the Conditional Formatting Rules Manager dialog being resized.

 

To create a copy of a rule, you no longer need to manually recreate the formula and format of the rule you're trying to copy.  Simply select the rule, and click “Duplicate Rule” to create the copy. This should be really useful when you want to create a few rules that are similar, but with some slight modifications.  You can create one rule, then duplicate it and make the necessary tweaks to the new rule. You don’t need to start from scratch for each one.

Screen shot showing the new Duplicate Rule button in the Conditional Formatting Rules Manager dialog.Screen shot showing the new Duplicate Rule button in the Conditional Formatting Rules Manager dialog.

 

The ability to resize the Conditional Formatting dialogs is one of the top requests that we’ve received, and we hope you like it.  If you have additional suggestions, please use the feedback button on the Help tab of the ribbon to let us know.

 

To get these improvements, you must be a Microsoft 365 subscriber, and you need to install the latest updates. For Windows, this is version 16.0.13530 or greater. For Mac this is version 16.47 or greater.

 

 

24 Comments
Respected Contributor

 

Works great on my virtual Windows 10 machine with:

Screenshot 2021-03-03 at 06.15.37.png

 

Works not on my Mac  (Big Sur 11.2.1), running under the same MS 365 for Business subscription with:

Screenshot 2021-03-03 at 06.09.57.png

Any idea why?

Microsoft

As mentioned in the post, the resizing improvement is only for Windows at this time.  Excel for Mac will get the Duplicate Rule button very soon.

Respected Contributor

Sorry! Okay, I overlook the last part of the first sentence. How silly.

Frequent Visitor

Can you please tell us the exact version of Excel, in which this improvement will be available in Europe, so I can tell the SysAdmin of our company. Maybe he can force the update of Excel then. We are using the German localization of Office 365, Info currently says: "Microsoft Excel für Microsoft 365 MSO (16.0.13127.21210) 64-Bit".

Occasional Contributor

Wow.. Excellent

New Contributor

Thank You Steve.  Much appreciated.  

Microsoft

StephanKellerBaerIng - the version number is mentioned at the end of the post.

New Contributor

@Steve Kraynak- Thank you for these updates.  I love them and have already been making use of them. 
I hate to ask for more, but what are the odds that we could get a "Delete Selected Rules" button added to the rules manager.  Reason being that because of the way cond. rules get messed up when copying and pasting data, I've had to delete all the rules and start over just to keep it clean.  And when you have many people copying and pasting, you can easily end up with multiple Thousands of duplicate rules (applying to different ranges).  I know there is "Clear Rules..." on the dropdown menu, but that option would be nice to have in the Rules Manage itself and be able to control, en mass, which rules to delete.  and which to keep.  Deleting them one at a time when you want to keep some of them, just takes way too long.  

Thanks for listening.

Microsoft

@davheili thanks for asking for more. We love to get such feedback, because it helps us to understand what people are trying to do so we can make Excel better. Selecting and being able to delete multiple rules at one time is a great suggestion. 

Occasional Contributor

@Steve Kraynak This is awesome! This will make working with multiple rules and long formulas much easier. Thank you very much!

 

That being said, I do agree with @davheili, we have several online files that serve multiple locations and users. It is ongoing maintenance to clean up the conditional formatting rules that are created from cut/paste. Being able to select a multiple, or select all, and then delete them all at once would be great!

Occasional Contributor

I’d say this is a very cool improvement. Thanks.

New Contributor

@Steve Kraynak  

Are you able to shed any light on when the Conditional Formatting applied to named ranges is likely to be incorporated into future versions?

It would be great to understand where this is in the Excel teams Road-Map as it is one of the most voted items, has been open since 2015 and still has a status of "Not at this time".

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10561194...

Would be nice if Microsoft focus on actual voted items, especially those in the top 10.

Occasional Contributor

Resizable dialogs. Great idea. But instead of limiting it to CF, how about making it a part of the base dialog definition. Let us resize them  There are many other dialog boxes that would benefit from providing expanded/expandable views.

.

Will the new size be remembered?

.

Another dialog I'd REALLY like to see redesigned is the Find/Replace.

On a screen with 1080 vertical resolution, it is reasonable to always open the Find dialog with the "More" button selected by default.

 

Regardless, you should redesign the "More" portion to put the options under the "Format" dropdown as buttons across the bottom of the dialog.

Valued Contributor

In response to @Bleddyn_James, I would like it taken further and for the conditional formatting region to be a dynamic Named Range.  I would also want the criterion to by a dynamic array formula corresponding to the range (it may or may not be the values found within the range).   

Microsoft

In response to an earlier comment, I'll shed some light on how we decide which requests to work on and in what order. The Excel team considers many factors when deciding to invest resources in addressing requests, such as applying Conditional Formatting to Named Ranges. In addition to the number of votes, we consider the complexity and effort to implement (how many people do we need to work on it and for how long?), risk (for example - Does it have performance implications?), backward compatibility (will it cause a problem for people using old versions of Excel?), to name a few. Although Excel has a large team of engineers, we're not able to work on all the projects and features that we'd like to tackle, so we always need to make tough decisions about which things we can't fit into our near-term plan. For the specific case of Conditional Formatting being applied to Named Ranges, even though it seems like a straightforward idea, it is quite complex. Old versions of Excel will not support the new behavior, so there would need to be some hybrid approach that will be suitable for old and new versions. We need to make sure that it doesn't cause a problem if someone with Excel 2019/2016 opened a file with one of these rules. Also, named ranges can be dynamic, meaning that the range might change and conditional formatting rules would have to be adjusted when the workbook is calculated. The complexity of it makes it a lot more time-consuming to implement compared to many other features, which makes it difficult to prioritize over many other requests.

Valued Contributor

@Steve Kraynak 

I see your point, but conditional formatting does give the feeling of being stuck in a time-warp; a homage to end-user computing as originally envisaged.  It is distinctly out of keeping with the world of dynamic arrays and Lambda functions but I can well believe that it is easier to implement Lambda than make CF keep up.  Even with traditional spreadsheets (one that I know kept track of conference paper submissions and rows were forever being moved as manuscripts were received or reassigned) the ranges defining the 10 overlapping CFs become an utter mess and require major repair at the end of each day. 

 

With dynamic arrays it is possible to apply the CF to an oversize region to allow for continual resizing of the array; I have even applied CF to entire worksheets so that the dynamic range is visually defined by the CF, no matter how it changes.  I half expected to crash Excel but it appears to perform OK as a workaround!

 

@Peter Bartholomew , just interesting, did you see that such workaround affects the performance? I guess it shall if only the range is not relatively small.

New Contributor

@Steve Kraynak 

Thank you for the explaining the challenges involved with applying Conditional Formatting to Named Ranges. As a developer myself, I certainly understand them. But as @Peter Bartholomew said, CF does still seem stuck in time.

You mention that performance must be taken into account. But performance doesn't necessarily apply only to the application. Users personal performance should also be taken into consideration. I have personally spent many hours correcting CF rules because named ranges are not supported. In some cases, I've even written VBA to wipeout and redo the rules for a page. But that's way beyond the average users abilities (users in this forum excepted).

As for backward compatibility, consideration needs to be given to todays world of subscription services. This means that old versions of Excel, especially in the business world, are slowly but surely getting fewer and far between. Just like old versions of Windows, old version of Excel need to be deprecated. If that means loosing some backward compatibility with old versions, then that's something the legacy users should need to deal with. As it is, old versions of Excel already cause problems with new, unsupported functions. As much as I hate to say it, it's one more reason to promote subscription services.

@davheili , I don't think that's so easy to deprecate old versions and push people to move on the cloud. Don't know concrete figures and where to find them, checked latest annual report. In Productivity (Office products) consumer products give about 15% of commercial products income. In year 2016 Excel had 750 millions users worldwide, perhaps close to billion now. We may assume major part of them are commercial users, and I believe significant part of enterprises prefer to stay on old stable versions. Most probably we speak about tens millions of seats, if not hundreds millions. Same as Military pays (or paid, not sure about current situation) for not to upgrade from Windows XP, enterprises and public organizations are ready to pay some extra for the support not to upgrade from old versions of Office.

Again, I didn't find the stats and not sure if it is published somewhere, just my filling of the situation.

Valued Contributor

@Sergei Baklan 

I haven't used whole sheet CF on large workbooks.  I had observed that entire column formatting or conditional formatting leaves the used range unaltered whereas a very large formatted range increases the used range.  I just decided to try it. 

 

I think the application was an experiment to colour the active cell along with all cells within the same CSE array formula region, all cells within referenced regions and, within that, the cells directly referenced from the active cell (each with its own lurid colour chosen to match a set of fluorescent marker pens).  The four dynamic range names (specific to the selected cell) were set up by an event handler, so I could have simply formatted the cells using an interior fill.  Conditional formatting was convenient because the sheet would automatically revert to its former state once I removed the event handler and the CF.

 

I am not in a position either to recommend the strategy or to point out any risks.  It is simply a fact that it worked well in the specific instance.

 

p.s. The formatting was done by testing each cell in the workbook for range intersection with each of the VBA-defined ranges (a call to the ISREF function within a defined name}.  Given that there are 10³⁴ cells on a worksheet, it could have gone very, very badly; but it didn't.

@Peter Bartholomew , thank you, that's interesting. Afraid doesn't work in Excel Online.

Valued Contributor

Does anything work on Excel Online?

 

I need to animate a dashboard that provides end users access to conduct what if studies based upon my clients statistical analysis of survey results.  To go online would be attractive but I lose the event handlers that increment or decrement user settings or selections, form controls that could be used instead also disappear.  I think slicers might work and I might be able to use the presence or absence of variables within a hidden pivot table to toggle settings?

 

It might be that the functionality I need has disappeared to Power Automate, but I wouldn't guarantee that and, in any case, neither I nor the end-users have access to Power Automate to the best of my knowledge.

 

Currently my choice of code platform is Excel 365 insider beta and my beloved Office 2010 is consigned to the trash can.

@Peter Bartholomew , Excel 2010 is still standard for many enterprises and so far they are not going to upgrade on 365. At the same time functionality provided in the beta 365 is just test the water one. I believe many things we do with it using more or less complicated workarounds will be natively available in some future. That's not insider information, that's logic of development.  

New Contributor

@Steve Kraynak 

Just because something is difficult does not mean it should not be pursued.
If all companies had that mentality we would never progress.

So I ask my original question yet again.

Are you able to shed any light on when the Conditional Formatting applied to named ranges is likely to be incorporated into future versions?
It would be great to understand where this is in the Excel teams Road-Map as it is one of the most voted items, has been open since 2015 and still has a status of "Not at this time".

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10561194...

Would be nice if Microsoft focus on actual voted items, especially those in the top 10.

Co-Authors
Version history
Last update:
‎Mar 02 2021 05:10 PM
Updated by: