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]
100 comments
-
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.
-
Jesse
commented
Can we get a stagnant applies to range? I input a range I want, yet it takes very little for it to get fragmented. It's ridiculous, how about don't change my range?
-
Roy
commented
It'd be nice to be able to select a style vs. building the formats for every rule piece by piece each rule. Would cut down on the proliferation of ever so slightly different formats in a spreadsheet, if nothing else.
Be nice too, to be able to link a list (a "series" I guess) to those built-in CF's, rather than building a several rule set for every variation that arises. Grading scales, for instance. Soooo many different scales, and a big lower end compared to the slices for grades higher than "F"... my bet is that applies to a few million non-grading data sets.
You know, about that F2 thing, if editing a cell formula (normal editing) required us to hit F2 a second time after entering the edit mode or seeing it act like it does in Conditional Formatting or Named Ranges, we'd still be using 1-2-3 for DOS and Quattro Pro for Windows. Although, conceptually, in a way, we DO have to do that... arrowing around from cell to cell, then hitting F2 to make the mode change to arrowing about inisde a cell... that's actually precisely what we do do isn't it? For the love of all things holy... well, I guess those two programs were doomed to die at the feet of the monopolist.
-
Tina
commented
We have been asking for movement on the conditional formatting manager for 4.5 years now. I honestly don't think MS is reading any of this .... "excel.uservoice" isn't being listened to.
-
shan
commented
PROVIDE AN UPDATE MS TEAM!!
This is ridiculous. As many, many, many people have commented, at least implement the 1 thing that SHOULD be EASY:
1. ALLOW RE-SIZE OF THE CR WINDOW.
I cannot believe I have subjected myself to typing in CAPS LOCK since this causes so much grief for us all on a daily basis.
2. ALLOW FOR COLUMN RE-SIZING within the CF window.
3. ALLOW FOR USING ARROWS TO NAVIGATE THE FORMULA BAR in the CF window without having to remember to press F2 first. - seriously, why the **** is this even a thing in the first place?
Additional asks:
5. Allow for duplicating of rules
6. Allow for multiple conditions in a formula in teh CF window rather than having to set 5 different rules with 5 different formulas to have a range of success display (a pretty common business need) - e.g. Red, Orange, Yellow, Green, depending on what data displays in the cell.
- People of the internet, if there is a way to do this, please let me know as I would be forever greatful! -
Roy
commented
@Ryan Jewell:
Actually, you CAN edit normally in their little box. You just have to know something they don't really stress anywhere: if you have entered the box you can press F2 and suddenly all the normal movement becomes possible. You can mouse about if desired as well, but the arrow keys now work. The F2 key is a toggle in this situation, so hitting it again restores the horrid mouse-only approach.
Most of us likely do do any halfway long or complicated formulas outside, then copy in. However, that presents some issues one must be aware of. The first is that unless you carefully go through the formula first, you probably don't have all the absolute addressing you could want. You must consider that before pasting in. The second is that you may not have the relative addressing you need unless you carefully consider two things: 1) partial absolute addressing, and 2) precisely where you were (what cell) when you wrote the formula. The first of those is fairly obvious when you think about it, it's just you have to think about it. The second is more complicated.
The second can also affect the first, by the way. The second creates trouble in the following manner. Say you want to affect cells starting with A1. You write the formula in C2. Any relative addressing in the formula will affect a cell offset from the current cell by -1 row and -2 columns. So when you paste it into the CF formula box and save, then expect it to apply to A1, you'll find it applies to something like XFC1048575. The trick here is to write it where convenient, C2 in this example, but then select the cell that precise writing should apply to, THEN enter the CF manager and paste that into the formula box. Then the references should be fine.
With thoughts like that in mind, yeah, why ever write anything other than simple conditions in that miserable little box, eh?
-
Ryan Jewell commented
Seems that someone forgot to finish CF in Excel. Did the OG coder keel over?
Know how I edit my long conditional formatting equations? Here is what I have to do:
1) Copy what's in the "small" entry box.
2) Paste into a blank worksheet. Do all the editing there.
3) Recopy and paste back into the CF entry box.Why? Well, among other things, you cannot edit the middle part of a long CF equation! Seriously...the box is so small. ARROWS do not work to move the cursor (it types cell references!!!!), so you have to do it surgically with your mouse, which is about impossible. Guess what happens...it zips RIGHT past the middle of the equation, so it's as if you can only edit the beginning and ending parts! Ridiculous.