Why is Excel butchering my Conditional Formatting?
I have this conditional formatting set
but when I move/delete/copy/cut/paste/insert a cell/row/column my conditional formatting becomes like this
The rules have duplicated themselves, the "Applies to" now is broken up and from the little bit you can see one of my blue rules has an altered Rule.
Sometimes I make mistakes when making an Excel spreadsheet requiring me to move and or delete stuff from a single cell to entire rows. Why does Excel think it's necessary to butcher my conditional formatting ? Especially since by creating these duplicate rules it gives me more work to do if I need to refine them later on which is what I use conditional formatting to avoid doing i.e. more work
microsoft-excel-2010 worksheet-function
edited Jan 4 at 22:55
Dave M
12.7k92838
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
|
show 2 more comments
I have this conditional formatting set
but when I move/delete/copy/cut/paste/insert a cell/row/column my conditional formatting becomes like this
The rules have duplicated themselves, the "Applies to" now is broken up and from the little bit you can see one of my blue rules has an altered Rule.
Sometimes I make mistakes when making an Excel spreadsheet requiring me to move and or delete stuff from a single cell to entire rows. Why does Excel think it's necessary to butcher my conditional formatting ? Especially since by creating these duplicate rules it gives me more work to do if I need to refine them later on which is what I use conditional formatting to avoid doing i.e. more work
microsoft-excel-2010 worksheet-function
edited Jan 4 at 22:55
Dave M
12.7k92838
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
FWIW, I've just did some basic testing in my Excel 2016, and no matter how I copy/pasted, inserted, or deleted, my range stayed "=$A:$D", except when I inserted or deleted a column within my range, and then it just expanded or contracted my range as expected. As with most weird app behaviour, I have to ask if you have all the latest service packs and Office updates installed?
– DarkMoon
Aug 14 '16 at 23:59
@DarkMoon well my PC is Windows 7 SP1 and Windows Update does say it's all up to date (Excel points me to Windows Update when i go File > Help > Check for Updates) so i would assume it's all up to date. as i have tagged 2010 it could be sometime between after 2010 and 2016 versions of Office this was fixed up as being a bug
– Memor-X
Aug 15 '16 at 0:04
Yeah, I was more pointing out that that behaviour isn't happening for me, so it's not "by design". :-P Hence the question about updates. When you're in the Windows Update screen, there should be an option to enable "updates for other Microsoft products", which is off by default in Win7; have you enabled that? If not, you won't see Office updates in Windows Update. One other thought; can you reproduce this behaviour in a new workbook? Might be something wrong within the file rather than Excel itself?
– DarkMoon
Aug 15 '16 at 0:09
What's in lines 73-74? What happens if you delete lines 73-74 before performing the operations that cause the rules to change?
– techraf
Aug 15 '16 at 0:16
1
Possible duplicate of Excel conditional formatting fragmentation
– Fabian Schmied
Jan 3 at 8:22
|
show 2 more comments
I have this conditional formatting set
but when I move/delete/copy/cut/paste/insert a cell/row/column my conditional formatting becomes like this
The rules have duplicated themselves, the "Applies to" now is broken up and from the little bit you can see one of my blue rules has an altered Rule.
Sometimes I make mistakes when making an Excel spreadsheet requiring me to move and or delete stuff from a single cell to entire rows. Why does Excel think it's necessary to butcher my conditional formatting ? Especially since by creating these duplicate rules it gives me more work to do if I need to refine them later on which is what I use conditional formatting to avoid doing i.e. more work
microsoft-excel-2010 worksheet-function
edited Jan 4 at 22:55
Dave M
12.7k92838
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
I have this conditional formatting set
but when I move/delete/copy/cut/paste/insert a cell/row/column my conditional formatting becomes like this
The rules have duplicated themselves, the "Applies to" now is broken up and from the little bit you can see one of my blue rules has an altered Rule.
Sometimes I make mistakes when making an Excel spreadsheet requiring me to move and or delete stuff from a single cell to entire rows. Why does Excel think it's necessary to butcher my conditional formatting ? Especially since by creating these duplicate rules it gives me more work to do if I need to refine them later on which is what I use conditional formatting to avoid doing i.e. more work
microsoft-excel-2010 worksheet-function
microsoft-excel-2010 worksheet-function
edited Jan 4 at 22:55
Dave M
12.7k92838
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
edited Jan 4 at 22:55
Dave M
12.7k92838
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
edited Jan 4 at 22:55
Dave M
12.7k92838
edited Jan 4 at 22:55
Dave M
12.7k92838
edited Jan 4 at 22:55
Dave M
12.7k92838
12.7k92838
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
asked Aug 14 '16 at 23:49
Memor-XMemor-X
3192731
3192731
FWIW, I've just did some basic testing in my Excel 2016, and no matter how I copy/pasted, inserted, or deleted, my range stayed "=$A:$D", except when I inserted or deleted a column within my range, and then it just expanded or contracted my range as expected. As with most weird app behaviour, I have to ask if you have all the latest service packs and Office updates installed?
– DarkMoon
Aug 14 '16 at 23:59
@DarkMoon well my PC is Windows 7 SP1 and Windows Update does say it's all up to date (Excel points me to Windows Update when i go File > Help > Check for Updates) so i would assume it's all up to date. as i have tagged 2010 it could be sometime between after 2010 and 2016 versions of Office this was fixed up as being a bug
– Memor-X
Aug 15 '16 at 0:04
Yeah, I was more pointing out that that behaviour isn't happening for me, so it's not "by design". :-P Hence the question about updates. When you're in the Windows Update screen, there should be an option to enable "updates for other Microsoft products", which is off by default in Win7; have you enabled that? If not, you won't see Office updates in Windows Update. One other thought; can you reproduce this behaviour in a new workbook? Might be something wrong within the file rather than Excel itself?
– DarkMoon
Aug 15 '16 at 0:09
What's in lines 73-74? What happens if you delete lines 73-74 before performing the operations that cause the rules to change?
– techraf
Aug 15 '16 at 0:16
1
Possible duplicate of Excel conditional formatting fragmentation
– Fabian Schmied
Jan 3 at 8:22
|
show 2 more comments
FWIW, I've just did some basic testing in my Excel 2016, and no matter how I copy/pasted, inserted, or deleted, my range stayed "=$A:$D", except when I inserted or deleted a column within my range, and then it just expanded or contracted my range as expected. As with most weird app behaviour, I have to ask if you have all the latest service packs and Office updates installed?
– DarkMoon
Aug 14 '16 at 23:59
@DarkMoon well my PC is Windows 7 SP1 and Windows Update does say it's all up to date (Excel points me to Windows Update when i go File > Help > Check for Updates) so i would assume it's all up to date. as i have tagged 2010 it could be sometime between after 2010 and 2016 versions of Office this was fixed up as being a bug
– Memor-X
Aug 15 '16 at 0:04
Yeah, I was more pointing out that that behaviour isn't happening for me, so it's not "by design". :-P Hence the question about updates. When you're in the Windows Update screen, there should be an option to enable "updates for other Microsoft products", which is off by default in Win7; have you enabled that? If not, you won't see Office updates in Windows Update. One other thought; can you reproduce this behaviour in a new workbook? Might be something wrong within the file rather than Excel itself?
– DarkMoon
Aug 15 '16 at 0:09
What's in lines 73-74? What happens if you delete lines 73-74 before performing the operations that cause the rules to change?
– techraf
Aug 15 '16 at 0:16
1
Possible duplicate of Excel conditional formatting fragmentation
– Fabian Schmied
Jan 3 at 8:22
FWIW, I've just did some basic testing in my Excel 2016, and no matter how I copy/pasted, inserted, or deleted, my range stayed "=$A:$D", except when I inserted or deleted a column within my range, and then it just expanded or contracted my range as expected. As with most weird app behaviour, I have to ask if you have all the latest service packs and Office updates installed?
– DarkMoon
Aug 14 '16 at 23:59
FWIW, I've just did some basic testing in my Excel 2016, and no matter how I copy/pasted, inserted, or deleted, my range stayed "=$A:$D", except when I inserted or deleted a column within my range, and then it just expanded or contracted my range as expected. As with most weird app behaviour, I have to ask if you have all the latest service packs and Office updates installed?
– DarkMoon
Aug 14 '16 at 23:59
@DarkMoon well my PC is Windows 7 SP1 and Windows Update does say it's all up to date (Excel points me to Windows Update when i go File > Help > Check for Updates) so i would assume it's all up to date. as i have tagged 2010 it could be sometime between after 2010 and 2016 versions of Office this was fixed up as being a bug
– Memor-X
Aug 15 '16 at 0:04
@DarkMoon well my PC is Windows 7 SP1 and Windows Update does say it's all up to date (Excel points me to Windows Update when i go File > Help > Check for Updates) so i would assume it's all up to date. as i have tagged 2010 it could be sometime between after 2010 and 2016 versions of Office this was fixed up as being a bug
– Memor-X
Aug 15 '16 at 0:04
Yeah, I was more pointing out that that behaviour isn't happening for me, so it's not "by design". :-P Hence the question about updates. When you're in the Windows Update screen, there should be an option to enable "updates for other Microsoft products", which is off by default in Win7; have you enabled that? If not, you won't see Office updates in Windows Update. One other thought; can you reproduce this behaviour in a new workbook? Might be something wrong within the file rather than Excel itself?
– DarkMoon
Aug 15 '16 at 0:09
Yeah, I was more pointing out that that behaviour isn't happening for me, so it's not "by design". :-P Hence the question about updates. When you're in the Windows Update screen, there should be an option to enable "updates for other Microsoft products", which is off by default in Win7; have you enabled that? If not, you won't see Office updates in Windows Update. One other thought; can you reproduce this behaviour in a new workbook? Might be something wrong within the file rather than Excel itself?
– DarkMoon
Aug 15 '16 at 0:09
What's in lines 73-74? What happens if you delete lines 73-74 before performing the operations that cause the rules to change?
– techraf
Aug 15 '16 at 0:16
What's in lines 73-74? What happens if you delete lines 73-74 before performing the operations that cause the rules to change?
– techraf
Aug 15 '16 at 0:16
1
1
Possible duplicate of Excel conditional formatting fragmentation
– Fabian Schmied
Jan 3 at 8:22
Possible duplicate of Excel conditional formatting fragmentation
– Fabian Schmied
Jan 3 at 8:22
|
show 2 more comments
1 Answer
1
active
oldest
votes
This happens when you Cut/Copy and then Paste lines within the applied range, in Excel 2000, 2003, 2007, and 2010 (I don't know about others).
it is still fully functional; if you consider then first and fifth line shown, they together define the complete range (just stupidly split), and the formula is identical for both (note that each formula is shown relative to the first cell it applies to; as the formula in the fifth line applies to $D$74, it contains ...$D$74... in it).
I have not found a way (looking for several years) to change that behavior. Whenever you copy or cut and then insert lines several times, Excel cuts the respective conditional formulas' ranges in pieces like this.
A manual workaround (to repair the formulas) is to
- Select all lines except the first, chose 'Clear Rules from Selected Cells'. You will now have your conditional formatting only in the first line.
- Open the Conditional Formatting manager, chose 'This worksheet', and extend all formulas to all lines (by typing
:$nnnnover:$2). - Yes you will have to repeat that ever so often. As I said, I have not found a better solution in years.
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
"it is still fully functional" - unfortunately not. I've the same issue with a pivot table, upon filter changes rules disappear from the range of the table. However thanks for the tip
– Máté Juhász
Aug 19 '16 at 12:32
See also superuser.com/a/991431/79488
– Fabian Schmied
Jan 3 at 8:22
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1113555%2fwhy-is-excel-butchering-my-conditional-formatting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This happens when you Cut/Copy and then Paste lines within the applied range, in Excel 2000, 2003, 2007, and 2010 (I don't know about others).
it is still fully functional; if you consider then first and fifth line shown, they together define the complete range (just stupidly split), and the formula is identical for both (note that each formula is shown relative to the first cell it applies to; as the formula in the fifth line applies to $D$74, it contains ...$D$74... in it).
I have not found a way (looking for several years) to change that behavior. Whenever you copy or cut and then insert lines several times, Excel cuts the respective conditional formulas' ranges in pieces like this.
A manual workaround (to repair the formulas) is to
- Select all lines except the first, chose 'Clear Rules from Selected Cells'. You will now have your conditional formatting only in the first line.
- Open the Conditional Formatting manager, chose 'This worksheet', and extend all formulas to all lines (by typing
:$nnnnover:$2). - Yes you will have to repeat that ever so often. As I said, I have not found a better solution in years.
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
"it is still fully functional" - unfortunately not. I've the same issue with a pivot table, upon filter changes rules disappear from the range of the table. However thanks for the tip
– Máté Juhász
Aug 19 '16 at 12:32
See also superuser.com/a/991431/79488
– Fabian Schmied
Jan 3 at 8:22
add a comment |
This happens when you Cut/Copy and then Paste lines within the applied range, in Excel 2000, 2003, 2007, and 2010 (I don't know about others).
it is still fully functional; if you consider then first and fifth line shown, they together define the complete range (just stupidly split), and the formula is identical for both (note that each formula is shown relative to the first cell it applies to; as the formula in the fifth line applies to $D$74, it contains ...$D$74... in it).
I have not found a way (looking for several years) to change that behavior. Whenever you copy or cut and then insert lines several times, Excel cuts the respective conditional formulas' ranges in pieces like this.
A manual workaround (to repair the formulas) is to
- Select all lines except the first, chose 'Clear Rules from Selected Cells'. You will now have your conditional formatting only in the first line.
- Open the Conditional Formatting manager, chose 'This worksheet', and extend all formulas to all lines (by typing
:$nnnnover:$2). - Yes you will have to repeat that ever so often. As I said, I have not found a better solution in years.
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
"it is still fully functional" - unfortunately not. I've the same issue with a pivot table, upon filter changes rules disappear from the range of the table. However thanks for the tip
– Máté Juhász
Aug 19 '16 at 12:32
See also superuser.com/a/991431/79488
– Fabian Schmied
Jan 3 at 8:22
add a comment |
This happens when you Cut/Copy and then Paste lines within the applied range, in Excel 2000, 2003, 2007, and 2010 (I don't know about others).
it is still fully functional; if you consider then first and fifth line shown, they together define the complete range (just stupidly split), and the formula is identical for both (note that each formula is shown relative to the first cell it applies to; as the formula in the fifth line applies to $D$74, it contains ...$D$74... in it).
I have not found a way (looking for several years) to change that behavior. Whenever you copy or cut and then insert lines several times, Excel cuts the respective conditional formulas' ranges in pieces like this.
A manual workaround (to repair the formulas) is to
- Select all lines except the first, chose 'Clear Rules from Selected Cells'. You will now have your conditional formatting only in the first line.
- Open the Conditional Formatting manager, chose 'This worksheet', and extend all formulas to all lines (by typing
:$nnnnover:$2). - Yes you will have to repeat that ever so often. As I said, I have not found a better solution in years.
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
This happens when you Cut/Copy and then Paste lines within the applied range, in Excel 2000, 2003, 2007, and 2010 (I don't know about others).
it is still fully functional; if you consider then first and fifth line shown, they together define the complete range (just stupidly split), and the formula is identical for both (note that each formula is shown relative to the first cell it applies to; as the formula in the fifth line applies to $D$74, it contains ...$D$74... in it).
I have not found a way (looking for several years) to change that behavior. Whenever you copy or cut and then insert lines several times, Excel cuts the respective conditional formulas' ranges in pieces like this.
A manual workaround (to repair the formulas) is to
- Select all lines except the first, chose 'Clear Rules from Selected Cells'. You will now have your conditional formatting only in the first line.
- Open the Conditional Formatting manager, chose 'This worksheet', and extend all formulas to all lines (by typing
:$nnnnover:$2). - Yes you will have to repeat that ever so often. As I said, I have not found a better solution in years.
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
answered Aug 15 '16 at 2:26
AganjuAganju
8,47731335
8,47731335
"it is still fully functional" - unfortunately not. I've the same issue with a pivot table, upon filter changes rules disappear from the range of the table. However thanks for the tip
– Máté Juhász
Aug 19 '16 at 12:32
See also superuser.com/a/991431/79488
– Fabian Schmied
Jan 3 at 8:22
add a comment |
"it is still fully functional" - unfortunately not. I've the same issue with a pivot table, upon filter changes rules disappear from the range of the table. However thanks for the tip
– Máté Juhász
Aug 19 '16 at 12:32
See also superuser.com/a/991431/79488
– Fabian Schmied
Jan 3 at 8:22
"it is still fully functional" - unfortunately not. I've the same issue with a pivot table, upon filter changes rules disappear from the range of the table. However thanks for the tip
– Máté Juhász
Aug 19 '16 at 12:32
"it is still fully functional" - unfortunately not. I've the same issue with a pivot table, upon filter changes rules disappear from the range of the table. However thanks for the tip
– Máté Juhász
Aug 19 '16 at 12:32
See also superuser.com/a/991431/79488
– Fabian Schmied
Jan 3 at 8:22
See also superuser.com/a/991431/79488
– Fabian Schmied
Jan 3 at 8:22
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1113555%2fwhy-is-excel-butchering-my-conditional-formatting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
FWIW, I've just did some basic testing in my Excel 2016, and no matter how I copy/pasted, inserted, or deleted, my range stayed "=$A:$D", except when I inserted or deleted a column within my range, and then it just expanded or contracted my range as expected. As with most weird app behaviour, I have to ask if you have all the latest service packs and Office updates installed?
– DarkMoon
Aug 14 '16 at 23:59
@DarkMoon well my PC is Windows 7 SP1 and Windows Update does say it's all up to date (Excel points me to Windows Update when i go File > Help > Check for Updates) so i would assume it's all up to date. as i have tagged 2010 it could be sometime between after 2010 and 2016 versions of Office this was fixed up as being a bug
– Memor-X
Aug 15 '16 at 0:04
Yeah, I was more pointing out that that behaviour isn't happening for me, so it's not "by design". :-P Hence the question about updates. When you're in the Windows Update screen, there should be an option to enable "updates for other Microsoft products", which is off by default in Win7; have you enabled that? If not, you won't see Office updates in Windows Update. One other thought; can you reproduce this behaviour in a new workbook? Might be something wrong within the file rather than Excel itself?
– DarkMoon
Aug 15 '16 at 0:09
What's in lines 73-74? What happens if you delete lines 73-74 before performing the operations that cause the rules to change?
– techraf
Aug 15 '16 at 0:16
1
Possible duplicate of Excel conditional formatting fragmentation
– Fabian Schmied
Jan 3 at 8:22