Conditional Formatting in excel based on another column
I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0.
Now how can I highlight the second column cells based on the first columns that are highlighted?
See the image below :
Now I want to make my worksheet like this image:
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 worksheet-function
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
add a comment |
I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0.
Now how can I highlight the second column cells based on the first columns that are highlighted?
See the image below :
Now I want to make my worksheet like this image:
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 worksheet-function
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
add a comment |
I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0.
Now how can I highlight the second column cells based on the first columns that are highlighted?
See the image below :
Now I want to make my worksheet like this image:
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 worksheet-function
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0.
Now how can I highlight the second column cells based on the first columns that are highlighted?
See the image below :
Now I want to make my worksheet like this image:
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 worksheet-function
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 worksheet-function
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
asked Jan 1 '14 at 12:34
Pooya YazdaniPooya Yazdani
130114
130114
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):
Select the range of
A1toA10.
Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula
=B1>0and pick the formatting with font as green.
Press OK and repeat for the negative values, except using
=B1<0for the formula and the font as red for the formatting.
The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.
If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0 for green and =$B1<0 for red:
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1,
– Pooya Yazdani
Jan 1 '14 at 13:05
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is=B2>0.
– Jerry
Jan 1 '14 at 13:20
1
yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table.
– Pooya Yazdani
Jan 1 '14 at 14:53
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see!
– Jerry
Jan 1 '14 at 15:24
what do you exactly mean by step 2? which option? where should I put the formula?
– Pooya Yazdani
Jan 1 '14 at 16:06
|
show 2 more comments
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%2f695640%2fconditional-formatting-in-excel-based-on-another-column%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
You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):
Select the range of
A1toA10.
Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula
=B1>0and pick the formatting with font as green.
Press OK and repeat for the negative values, except using
=B1<0for the formula and the font as red for the formatting.
The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.
If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0 for green and =$B1<0 for red:
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1,
– Pooya Yazdani
Jan 1 '14 at 13:05
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is=B2>0.
– Jerry
Jan 1 '14 at 13:20
1
yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table.
– Pooya Yazdani
Jan 1 '14 at 14:53
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see!
– Jerry
Jan 1 '14 at 15:24
what do you exactly mean by step 2? which option? where should I put the formula?
– Pooya Yazdani
Jan 1 '14 at 16:06
|
show 2 more comments
You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):
Select the range of
A1toA10.
Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula
=B1>0and pick the formatting with font as green.
Press OK and repeat for the negative values, except using
=B1<0for the formula and the font as red for the formatting.
The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.
If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0 for green and =$B1<0 for red:
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1,
– Pooya Yazdani
Jan 1 '14 at 13:05
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is=B2>0.
– Jerry
Jan 1 '14 at 13:20
1
yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table.
– Pooya Yazdani
Jan 1 '14 at 14:53
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see!
– Jerry
Jan 1 '14 at 15:24
what do you exactly mean by step 2? which option? where should I put the formula?
– Pooya Yazdani
Jan 1 '14 at 16:06
|
show 2 more comments
You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):
Select the range of
A1toA10.
Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula
=B1>0and pick the formatting with font as green.
Press OK and repeat for the negative values, except using
=B1<0for the formula and the font as red for the formatting.
The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.
If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0 for green and =$B1<0 for red:
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):
Select the range of
A1toA10.
Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula
=B1>0and pick the formatting with font as green.
Press OK and repeat for the negative values, except using
=B1<0for the formula and the font as red for the formatting.
The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.
If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0 for green and =$B1<0 for red:
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
edited Jan 1 '14 at 16:21
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
answered Jan 1 '14 at 12:47
JerryJerry
4,642827
4,642827
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1,
– Pooya Yazdani
Jan 1 '14 at 13:05
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is=B2>0.
– Jerry
Jan 1 '14 at 13:20
1
yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table.
– Pooya Yazdani
Jan 1 '14 at 14:53
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see!
– Jerry
Jan 1 '14 at 15:24
what do you exactly mean by step 2? which option? where should I put the formula?
– Pooya Yazdani
Jan 1 '14 at 16:06
|
show 2 more comments
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1,
– Pooya Yazdani
Jan 1 '14 at 13:05
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is=B2>0.
– Jerry
Jan 1 '14 at 13:20
1
yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table.
– Pooya Yazdani
Jan 1 '14 at 14:53
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see!
– Jerry
Jan 1 '14 at 15:24
what do you exactly mean by step 2? which option? where should I put the formula?
– Pooya Yazdani
Jan 1 '14 at 16:06
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1,
– Pooya Yazdani
Jan 1 '14 at 13:05
How can I apply to whole column, not one by one. You said "Select the range" and in step 3 you said "Put the formula =B1>0". I want to highlight A1 to A10 cells according to B1 to B10, but this formula highlights A1 to A10 according to B1,
– Pooya Yazdani
Jan 1 '14 at 13:05
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is
=B2>0.– Jerry
Jan 1 '14 at 13:20
@PooyaYazdani Did you try it? The conditional formatting adapts to the ranges, so that on A2, the actual formula that applies is
=B2>0.– Jerry
Jan 1 '14 at 13:20
1
1
yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table.
– Pooya Yazdani
Jan 1 '14 at 14:53
yes I tried it, it doesn't adapt a range to another range! you can simulate this table, I select the first range (A1-A10) an then choose highlight rule, then I select more rules, then Use a formula to determine which cells to format, and then I select second range (B1-B10), but the first range highlights according to first cell of second cell (B1), Is it true? you can simulate this table.
– Pooya Yazdani
Jan 1 '14 at 14:53
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see!
– Jerry
Jan 1 '14 at 15:24
@PooyaYazdani I didn't make any mention of selecting the second range! Just put the formula I told in my answer in the box and you'll see!
– Jerry
Jan 1 '14 at 15:24
what do you exactly mean by step 2? which option? where should I put the formula?
– Pooya Yazdani
Jan 1 '14 at 16:06
what do you exactly mean by step 2? which option? where should I put the formula?
– Pooya Yazdani
Jan 1 '14 at 16:06
|
show 2 more comments
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%2f695640%2fconditional-formatting-in-excel-based-on-another-column%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
