Resizable Conditional Formatting dialogs
Published Mar 02 2021 05:19 PM 17.9K 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.  If you are using the Monthly Enterprise Channel to get your updates, this feature will be available in version 16.0.140 or greater.

 

 

39 Comments
Platinum 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.

Platinum Contributor

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

Copper Contributor

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".

Copper Contributor

Wow.. Excellent

Brass Contributor

Thank You Steve.  Much appreciated.  

Microsoft

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

Brass Contributor

@Steve_K_Excel- 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. 

Brass Contributor

@Steve_K_Excel 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!

Brass Contributor

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

Brass Contributor

@Steve_K_Excel  

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.

Brass 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.

Silver 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.

Silver Contributor

@Steve_K_Excel 

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.

Brass Contributor

@Steve_K_Excel 

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.

Silver 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.

Silver 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.  

Brass 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.

Copper Contributor

@Steve_K_Excel this is top!

We're on the MonthlyEnterprise update channel. Any idea when this update will become available for us?

I would expect that the MonthlyEnterprise channel's updates should be max 1 month behind the Current channel?

https://support.microsoft.com/en-us/office/what-s-new-in-microsoft-365-95c8d81d-08ba-42c1-914f-bca46... shows that the version of the Current channel is available since December.

ErikBru_1-1620836515499.png

 

So, I would have expected this to be available in MonthlyEnterprise channel as from January/February?

I am on version 13901.20516, but this resizing feature doesn't seem to be available.

ErikBru_0-1620836446184.png

 

@ErikBru , as a comment

- resizable conditional formatting was introduced in Current channel in March

image.png

- repeating functionality from Current to Monthly in a month is not so straightforward. Some functionality could be repeated, some could be missed. Build number is not very important. It only means the code for this or that functionality is included into the build. But it doesn't mean this functionality will be activated for the channel.

You may check and compare release notes at Release notes for Current Channel releases - Office release notes | Microsoft Docs

and Release notes for Monthly Enterprise Channel releases - Office release notes | Microsoft Docs  

Again, above is only comment, I have no idea when to expect this functionality on your channel.

Copper Contributor

Hi

 

RE: Version 2106, New Formatting Rule dialog box.

 

This works for me in my version, but box only expands left or right when you use the sizing handle at the bottom right (No other handles can expand the box vertically). Would this be a bug?

 

I cant see an option to upload a screen video recording to demonstrate.

Silver Contributor

Without wishing to appear ungrateful for the user interface improvements on offer, I would draw attention to the curiously designed 'Evaluate Formulas' dialogue box.  In these days with array formulas becoming the norm, the postage-stamp sized window needs to be expandable to full screen.

 

@Kevin Mouncher The CF window expands as shown for me.

Brass 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.

Brass Contributor

@Kevin Mouncher I understand what you are talking about in that the "New Rule" dialog box does not expand down.  But when you look at the data in the box, expanding down gains nothing anyway.  None of the fields would gain anything by expanding down because they're not scrollable.

The "Rules Manager" Dialog does, however,  expands both Horizontally and Vertically.

Copper Contributor

@davheili 

 

Thanks for explaining that.

Microsoft

@ErikBru - I updated the original post to mention the version in which we plan to enable the feature if you get your updates via the Monthly Enterprise Channel.

Copper Contributor

@Steve_K_Excel Thanks; I'll keep an eye on it.

Also thanks for explaining the different channels.

 

PS: must be a mistake in the documentation then at: https://support.microsoft.com/en-us/office/what-s-new-in-microsoft-365-95c8d81d-08ba-42c1-914f-bca46... ; which mentioned that the feature would have been included in December release. (no problem for me anymore; just mentioning)

Copper Contributor

Now my question is, why has it taken so many years and so many versions for both the resizing and the duplicate button to not have come sooner? My research suggests it's been Excel since at least 2000, and the software itself started 1985.

 

Why has it taken at least 20 years for such basic features?

Copper Contributor

Hi @Steve_K_Excel,

 

I'm not a Microsoft 365 subscriber.

I bought an Office 2021 license and I'm currently on Excel 2021 MSO (Version 2304 Build 16.0.16327.20200) 64-bit.

Is there any way for me to get the features?

 

Thank you,

Victor

Copper Contributor

Hey @Steve_K_Excel , 

Following up on the last comment. Is this a feature that can be applied to Office Professional Plus 2021 version too? 

Unsure why its limited to Office 365 users only.

 

Thank You 

Full Send

Copper Contributor

@FullSend @Victor_Kain - the features that are added to M365 are generally added to the next version of the "one-time purchase" product. That means this feature will show up in Office 202x (whatever comes after 2021).

@Steve K , I guess the key word is "generally"

Copper Contributor

I can clarify "generally".  Features that require the use of M365 services, such as Analyze Data, Data Types, and Data From Picture are not typically added to the one-time purchase (perpetual licensed) versions of Excel, because they incur a cost each time they're used. If you're a subscriber, then you get the benefits of those features, because you're paying for it. If you're not a subscriber, then you get features that run completely locally on your computer, but not features that have an ongoing cost.

Co-Authors
Version history
Last update:
‎May 17 2021 09:27 AM
Updated by: