How can I ignore zero-values in an Excel graph?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.
I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?
Thanks.
microsoft-excel worksheet-function
asked Feb 9 at 19:35
bloomersbloomers
1164
add a comment |
I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.
I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?
Thanks.
microsoft-excel worksheet-function
asked Feb 9 at 19:35
bloomersbloomers
1164
Brilliant, clear well worded question. +1
– Dave
Feb 9 at 20:13
What is actually in the cells displaying as zero (number0, blank cell, formula that returns a null (""), something else?
– fixer1234
Feb 10 at 1:26
add a comment |
I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.
I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?
Thanks.
microsoft-excel worksheet-function
asked Feb 9 at 19:35
bloomersbloomers
1164
I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.
I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?
Thanks.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Feb 9 at 19:35
bloomersbloomers
1164
asked Feb 9 at 19:35
bloomersbloomers
1164
edited Feb 10 at 3:36
bloomers
asked Feb 9 at 19:35
bloomersbloomers
1164
asked Feb 9 at 19:35
bloomersbloomers
1164
asked Feb 9 at 19:35
bloomersbloomers
1164
1164
Brilliant, clear well worded question. +1
– Dave
Feb 9 at 20:13
What is actually in the cells displaying as zero (number0, blank cell, formula that returns a null (""), something else?
– fixer1234
Feb 10 at 1:26
add a comment |
Brilliant, clear well worded question. +1
– Dave
Feb 9 at 20:13
What is actually in the cells displaying as zero (number0, blank cell, formula that returns a null (""), something else?
– fixer1234
Feb 10 at 1:26
Brilliant, clear well worded question. +1
– Dave
Feb 9 at 20:13
Brilliant, clear well worded question. +1
– Dave
Feb 9 at 20:13
What is actually in the cells displaying as zero (number
0, blank cell, formula that returns a null (""), something else?– fixer1234
Feb 10 at 1:26
What is actually in the cells displaying as zero (number
0, blank cell, formula that returns a null (""), something else?– fixer1234
Feb 10 at 1:26
add a comment |
1 Answer
1
active
oldest
votes
There are various methods for avoiding the zeroes, none of them are perfect.
Below are a couple of such methods.
Filtering the data set
- Select the data range.
- On the Data tab, click Filter in the Sort & Filter group,
to add a filter to all of the columns. - Click the drop-down arrow on the column's first row and uncheck
0. - Click OK to filter the column, which will filter the entire row.
Be sure to remove the filter when you're done.
Replace 0s with NA()
- Select the data set
- Click Find & Select in the Editing group on the Home tab and choose Replace,
or type Ctrl+H. - Enter
0in Find what. - Enter
=NA()in Replace
- Click Options to display additional settings and
check Match entire cell contents. - Click Replace All.
- Click Close and OK.
For more information see the article
How to suppress 0 values in an Excel chart.
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
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%2f1403967%2fhow-can-i-ignore-zero-values-in-an-excel-graph%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
There are various methods for avoiding the zeroes, none of them are perfect.
Below are a couple of such methods.
Filtering the data set
- Select the data range.
- On the Data tab, click Filter in the Sort & Filter group,
to add a filter to all of the columns. - Click the drop-down arrow on the column's first row and uncheck
0. - Click OK to filter the column, which will filter the entire row.
Be sure to remove the filter when you're done.
Replace 0s with NA()
- Select the data set
- Click Find & Select in the Editing group on the Home tab and choose Replace,
or type Ctrl+H. - Enter
0in Find what. - Enter
=NA()in Replace
- Click Options to display additional settings and
check Match entire cell contents. - Click Replace All.
- Click Close and OK.
For more information see the article
How to suppress 0 values in an Excel chart.
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
add a comment |
There are various methods for avoiding the zeroes, none of them are perfect.
Below are a couple of such methods.
Filtering the data set
- Select the data range.
- On the Data tab, click Filter in the Sort & Filter group,
to add a filter to all of the columns. - Click the drop-down arrow on the column's first row and uncheck
0. - Click OK to filter the column, which will filter the entire row.
Be sure to remove the filter when you're done.
Replace 0s with NA()
- Select the data set
- Click Find & Select in the Editing group on the Home tab and choose Replace,
or type Ctrl+H. - Enter
0in Find what. - Enter
=NA()in Replace
- Click Options to display additional settings and
check Match entire cell contents. - Click Replace All.
- Click Close and OK.
For more information see the article
How to suppress 0 values in an Excel chart.
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
add a comment |
There are various methods for avoiding the zeroes, none of them are perfect.
Below are a couple of such methods.
Filtering the data set
- Select the data range.
- On the Data tab, click Filter in the Sort & Filter group,
to add a filter to all of the columns. - Click the drop-down arrow on the column's first row and uncheck
0. - Click OK to filter the column, which will filter the entire row.
Be sure to remove the filter when you're done.
Replace 0s with NA()
- Select the data set
- Click Find & Select in the Editing group on the Home tab and choose Replace,
or type Ctrl+H. - Enter
0in Find what. - Enter
=NA()in Replace
- Click Options to display additional settings and
check Match entire cell contents. - Click Replace All.
- Click Close and OK.
For more information see the article
How to suppress 0 values in an Excel chart.
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
There are various methods for avoiding the zeroes, none of them are perfect.
Below are a couple of such methods.
Filtering the data set
- Select the data range.
- On the Data tab, click Filter in the Sort & Filter group,
to add a filter to all of the columns. - Click the drop-down arrow on the column's first row and uncheck
0. - Click OK to filter the column, which will filter the entire row.
Be sure to remove the filter when you're done.
Replace 0s with NA()
- Select the data set
- Click Find & Select in the Editing group on the Home tab and choose Replace,
or type Ctrl+H. - Enter
0in Find what. - Enter
=NA()in Replace
- Click Options to display additional settings and
check Match entire cell contents. - Click Replace All.
- Click Close and OK.
For more information see the article
How to suppress 0 values in an Excel chart.
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
answered Feb 9 at 20:09
harrymcharrymc
265k14274583
265k14274583
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%2f1403967%2fhow-can-i-ignore-zero-values-in-an-excel-graph%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
Brilliant, clear well worded question. +1
– Dave
Feb 9 at 20:13
What is actually in the cells displaying as zero (number
0, blank cell, formula that returns a null (""), something else?– fixer1234
Feb 10 at 1:26