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 Dan [MS] for clarity on 10/20/2015]
* 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
* etc.
Levi Bailey
shared this idea
Thanks for the suggestion Levi! We’ll be taking a look at this along with some other asks around conditional formatting. It’s a big help to see the things with the most votes, particularly within areas like formatting. So please keep the votes coming for things you want us to do sooner!
Thanks,
John [MS XL]
70 comments
-
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!
-
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.
-
Anonymous
commented
Very good list Andreas. Fully agree with it 100%
come on excel! listen to us!also, if you could toggle 're-order by selected formatting if true' then it'd be easier to rejoin fragmented rules into 1 again.
-
Andreas
commented
Fully agree with Kevin:
CF is very powerful but seems left behind. Here is my list of needs for CF.
- Make window resizable
- Don't fragment rules when cut/copy/paste/insert/delete rows
- Make formulas work like regular formulas (error checking, auto complete, result preview, volatile only if formula used is volatile)
- Allow referencing of named ranges and structured table reference
- Make search/find feature look inside CF formulasmaintaining many CF is a nightmare!
-
Eckhard
commented
Errors (--) and deficits (-)
-- Conditional format causes crashes when applied within a structured table.
- Applies to" allows the specification of name ranges, but converts the named range to coordinates.
The loss of the Named Area is very annoying because you can no longer model efficiently. - In contrast, the reference to the named area remains the same during data validation.
- Styles (Custom styles) are very helpful for the systematic formatting of Excel. However, they are missing in the "Conditional Format" function. There, the benefit would also be very great. -
Stephan Keller
commented
At least make the dialog window resizeable asap, yes, I mean ASAP!
It can't be that hard to implement that.
When I see all the other changes MicroSoft implements to the UI of Office365 (of which, not all of them makes Office365 more user friendly), it just seems to me, that they set wrong priorities on their backlog of tasks.
How many votes must a suggestion receive, until it will be implemented? -
Kevin Osborn
commented
CF is very powerful but seems left behind. Here is my list of needs for CF.
- Make window resizable
- Don't fragment rules when cut/copy/paste/insert/delete rows
- Make formulas work like regular formulas (error checking, auto complete, result preview, volatile only if formula used is volatile)
- Allow referencing of named ranges and structured table reference
- Make search/find feature look inside CF formulas -
Phil
commented
Please also include protection of conditional formatting from copy/paste actions in protected cells
-
Anonymous
commented
why do we bother?
this will clearly never get done, it seems.
even the simple bits that would be so quick (e.g. window re-size, or re-order by format)...
yours frustrated
me