Alternative to cell highlighting using conditional formatting in Excel without causing freeze/crash
In column A, I have a value and I want to highlight if it doesn't match the same value in column B.
I'm using the formula below in conditional formatting which works great.
=ISERROR(MATCH(A1,$B:$B,0))
After highlighting them, I need to be able to sort/filter by cell colour so I can easily find non-matching rows.
However as my data is often 60k+ rows, this often makes Excel freeze up and/or crash.
I know conditional formatting adds a lot of weight to a spreadsheet, so what lighter alternative is there for what I'm trying to do?
microsoft-excel freeze crash microsoft-excel-2013 conditional-formatting
asked Mar 16 '15 at 10:20
moobotmoobot
359512
add a comment |
In column A, I have a value and I want to highlight if it doesn't match the same value in column B.
I'm using the formula below in conditional formatting which works great.
=ISERROR(MATCH(A1,$B:$B,0))
After highlighting them, I need to be able to sort/filter by cell colour so I can easily find non-matching rows.
However as my data is often 60k+ rows, this often makes Excel freeze up and/or crash.
I know conditional formatting adds a lot of weight to a spreadsheet, so what lighter alternative is there for what I'm trying to do?
microsoft-excel freeze crash microsoft-excel-2013 conditional-formatting
asked Mar 16 '15 at 10:20
moobotmoobot
359512
add a comment |
In column A, I have a value and I want to highlight if it doesn't match the same value in column B.
I'm using the formula below in conditional formatting which works great.
=ISERROR(MATCH(A1,$B:$B,0))
After highlighting them, I need to be able to sort/filter by cell colour so I can easily find non-matching rows.
However as my data is often 60k+ rows, this often makes Excel freeze up and/or crash.
I know conditional formatting adds a lot of weight to a spreadsheet, so what lighter alternative is there for what I'm trying to do?
microsoft-excel freeze crash microsoft-excel-2013 conditional-formatting
asked Mar 16 '15 at 10:20
moobotmoobot
359512
In column A, I have a value and I want to highlight if it doesn't match the same value in column B.
I'm using the formula below in conditional formatting which works great.
=ISERROR(MATCH(A1,$B:$B,0))
After highlighting them, I need to be able to sort/filter by cell colour so I can easily find non-matching rows.
However as my data is often 60k+ rows, this often makes Excel freeze up and/or crash.
I know conditional formatting adds a lot of weight to a spreadsheet, so what lighter alternative is there for what I'm trying to do?
microsoft-excel freeze crash microsoft-excel-2013 conditional-formatting
microsoft-excel freeze crash microsoft-excel-2013 conditional-formatting
asked Mar 16 '15 at 10:20
moobotmoobot
359512
asked Mar 16 '15 at 10:20
moobotmoobot
359512
edited Mar 16 '15 at 10:51
user375162
asked Mar 16 '15 at 10:20
moobotmoobot
359512
asked Mar 16 '15 at 10:20
moobotmoobot
359512
asked Mar 16 '15 at 10:20
moobotmoobot
359512
359512
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Adding the same formula in column C instead of (/ additionally to) conditional formatting should have the best performance. If that still isn't good then consider converting these formula to static values (of course you'll need to update it manually, but this way you can add conditional formatting on column C).
Are you sure you need to keep columns A and B next to each other? If you could move column B to a different sheet and sort it that would be again quicker.
Other options would be VBA but it's surely slower than using built in formulae.
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
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%2f890131%2falternative-to-cell-highlighting-using-conditional-formatting-in-excel-without-c%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
Adding the same formula in column C instead of (/ additionally to) conditional formatting should have the best performance. If that still isn't good then consider converting these formula to static values (of course you'll need to update it manually, but this way you can add conditional formatting on column C).
Are you sure you need to keep columns A and B next to each other? If you could move column B to a different sheet and sort it that would be again quicker.
Other options would be VBA but it's surely slower than using built in formulae.
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
add a comment |
Adding the same formula in column C instead of (/ additionally to) conditional formatting should have the best performance. If that still isn't good then consider converting these formula to static values (of course you'll need to update it manually, but this way you can add conditional formatting on column C).
Are you sure you need to keep columns A and B next to each other? If you could move column B to a different sheet and sort it that would be again quicker.
Other options would be VBA but it's surely slower than using built in formulae.
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
add a comment |
Adding the same formula in column C instead of (/ additionally to) conditional formatting should have the best performance. If that still isn't good then consider converting these formula to static values (of course you'll need to update it manually, but this way you can add conditional formatting on column C).
Are you sure you need to keep columns A and B next to each other? If you could move column B to a different sheet and sort it that would be again quicker.
Other options would be VBA but it's surely slower than using built in formulae.
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
Adding the same formula in column C instead of (/ additionally to) conditional formatting should have the best performance. If that still isn't good then consider converting these formula to static values (of course you'll need to update it manually, but this way you can add conditional formatting on column C).
Are you sure you need to keep columns A and B next to each other? If you could move column B to a different sheet and sort it that would be again quicker.
Other options would be VBA but it's surely slower than using built in formulae.
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
answered Mar 16 '15 at 11:10
Máté JuhászMáté Juhász
14.7k63452
14.7k63452
add a comment |
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%2f890131%2falternative-to-cell-highlighting-using-conditional-formatting-in-excel-without-c%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
