Improve the Conditional Formatting Manager
So that we can easily manage conditional formatting. While we're at it, the way conditional formatting is treated when cutting / copying and pasting etc could be reconsidered.
[Following examples added by the Excel team for clarity]
* name rules
* add a comment to rules
* more easily see what rules apply to what range
* see which rules can be consolidated b/c they are similar
* have an option to LOCK THE RANGES so they never change no matter what cells in the sheet get moved or copied or deleted, either manually or by adjusting pivot tables
* multi-select and drag-to-move-up-down
* improve behavior when copying/inserting cells/rows/columns
* import/export rules
* switch A1<->R1C1
* keep conditional-formatting-manager open while editing cells,
* consolidate-rules wizard
* integration into a worksheet navigator
* group rules
*Resize the dialogs - this is covered by a different suggestion - https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16092793-the-conditional-formatting-rules-manager-badly-nee
Levi Bailey
shared this idea
Thanks for your support of this suggestion and all the great ideas in the comments. We have at least one improvement planned (resizing the dialogs – see link in the description), and we’re looking at some additional improvements.
If you have specific ideas that aren’t already listed, please add them in a comment.
Steve [Microsoft Excel]
102 comments
-
Evan Forcucci
commented
Adding more "date occurring" options would be helpful. Current options are limited and I would have to make separate rules for Next Week, This Month, Next Month, etc. as a workaround which seems like a waste of time. This should be pretty easy to implement.
ex. Past, Future, Next 6 Months, etc.
ideally would get overridden if I add a rule for "less than 7 days from now" on top of it
-
Claire S
commented
I would like CF to work properly within Excel tables, so that rules are not duplicated when I add/remove data. This should be done automatically by using table references in the "Applies to" box, but if this is not possible, then could we have a "Merge rules" button or a "Delete duplicate rules" one, so that we just have to tweak it once.
-
Ian KR
commented
Yes please to all of these! In particular:
1. Resizable CF dialog window and fields;
2. When user copies ranges *within* a range that has CF rules, it doesn't duplicate the new range and carve up the existing range with 'new' rules and create a horrendously bloated workbook over time (that is prone to crashing). I manage user input templates for my organisation and I've had to resort to writing VBA code that cleans & resets CF rules at the click of a button because I'm fed up with having to clean it up manually;
3. When you click in the field to edit the range where the rule applies, please default to Edit Mode so that you don't have to first hit F2 (which I keep forgetting!). Otherwise, you hit the arrow key and it types in the current cell reference. At least, allow the default to be user-set in Options | Advanced? Thank you.
4. Allow copying of cell formatting either WITH or WITHOUT copying Cond Formatting. i.e., keep 'normal' formatting and Cond Formatting as totally distinct animals. I see that there's an option in the Paste Special dialog: "All merging conditional formats - Pastes the contents and conditional formatting options from the copied cells.", but isn't this just one of the single choice options?
Many thanks
-
uu
commented
2020 , still the status is the same
Unbelievable ! -
Futski
commented
Good News here. Resizable Conditional Formatting dialog box is on it's way. It's was released to the Office Insider Beta channel on Oct 30. I know it's not everything on the list above, but it's a start.
See here https://insider.office.com/en-us/releasenotes/windows/fast/windows-beta-channel-release-notes
Yea!!! -
Alfred
commented
It's really nice that you consider doing something about this.
Definitely this one:
* have an option to LOCK THE RANGES so they never change no matter what cells in the sheet get moved or copied or deleted, either manually or by adjusting pivot tablesWhen things have gotten out of control due to copy-pasting of unaware users I have also had the following issues with this dialog:
- Frequent random crashes when removing one of the many many entries.
- Really bad performance, painfully slow to edit when you have many many entries. -
Pian Sa
commented
I've been requesting improvements in conditional formatting for ages now (ir must have been 6 years!) but the problems remain unchanged while the Excel version keeps changing when it's unnecessary!
Like this request here,
* when entering in formula field, default in "edit-mode" not "cell changing mode"
it's been requested over and over! It's very tiring now!
-
VJaggi
commented
* multi-select for delete/reordering rules
* with multi-monitor, when the CF window is opened on the secondary window, the formula fields are emptied when scrolling
* when entering in formula field, default in "edit-mode" not "cell changing mode"
* when copying a cell from one sheet to another, add an option to copy the formating (color, font, border, etc.) but not the associated conditionnal formating, because all the formulas are easily breaked up when copying cells and produce tons of new formulas with cross ranges. -
Vladimir Sveda
commented
... and I would also appreciate VBA support for manipulating with CF ... Add, Remove, Edit
-
Roger Sereinig
commented
* field to enter cell area should be edit mode instead of point mode by default. if I click the arrow right of the field, it should change to point mode or by pressing "F2". Same change should be done within namemanager window.
* changeable column width
* field to enter cell area should be multiline and adjustable in height
* multi-select and delete rules
* multiselect and change cell area for all selected rules
* an easy way to record rules in the macro recorder
* copy rules fom one sheet to another within the Conditional Formatting ManagerI think some of the things could be changed very quickly (window size, column width and change of the cell mode to edit mode. so please do it as soon as possible and check the rest later thanks!
-
Anonymous
commented
reduce the amount of memory used when using Conditional formatting... It makes the file expand exponentially when it is used.
-
Arne Munther commented
I can only say Hear! Hear!
-
Minion Mangled Manager
commented
I would like to be able to change the window size of the rules dialog (in order to see more rules at the same time; scrolling through small windows is so 1995) and also to duplicate rules (when you need many very similar rules; as of now, each rule has to be generated individually).
-
Phil Edwards
commented
Anyone know how we find out from Microsoft if/when they intend to make any of these improvements to conditional formatting?
-
terry
commented
I want it to have an option to LOCK THE RANGES so they never change no matter what cells in the sheet get moved or copied or deleted, either manually or by adjusting pivot tables. I'm so tired of having to go back and redo the conditional formatting.
-
Tim Ellis
commented
Yes, it would be good to see rules consolidated, I know when you add rows, the conditional formatting, is then duplicated.
-
Ionut
commented
The window style is too old, cannot be resized, the arrow-keys makes the imput really difficult. It is not easy to work in this window. There is a lot of place of improvement.
-
CFRMWindow TOOSMALL
commented
Make Conditional Formatting Rules Manager Re-Sizable - Just do it! Do it now!
-
Ewald
commented
There are several posts on conditional formatting, with many good ideas, and I could think of even more: give names to rules, bigger window, multi-select and drag-to-move-up-down, improve behavior when copying/inserting cells/rows/columns, import/export rules, switch A1<->R1C1, keep conditional-formatting-manager open while editing cells, consolidate-rules wizard, integration into a worksheet navigator, group rules, etc...
A big rework of the conditional formatting manager would be welcome. -
Berktan Bestel commented
I still observe the same problems with conditional formatting: Any copy paste creates duplicate rules which you have to keep cleaning manually. The rules management window cannot be resized. The default column widths are too small to show meaningful content. I agree, this basically needs to be redesigned from the ground up with scalability in mind.