Excel 2013: Adding a formula / function to a chart legend
I have a chart in Excel with three legend entries. Each of them contains the word "Umsatz" and all of them are linked to cell values. Now I would like to remove that word from the entries with a function since it's also the title of my chart. However, I can't and don't want to edit the cell values accordingly.
So I right-clicked the legend, clicked on "select data" and then tried to edit a legend entry and enter a formula to replace the word with nothing but it doesn't accept it claiming the formula might be wrong or whatever.
When I try the same forumla in a cell in my worksheet it works fine.
=SUBSTITUTE('00000_Musterkunde'!Z5S1;"Umsatz ";"")
Am I doing something wrong or does Excel really not accept formulas in chart legends?
Best Regards
microsoft-excel-2013 charts
edited Sep 18 '15 at 14:13
dav
7,73532143
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
add a comment |
I have a chart in Excel with three legend entries. Each of them contains the word "Umsatz" and all of them are linked to cell values. Now I would like to remove that word from the entries with a function since it's also the title of my chart. However, I can't and don't want to edit the cell values accordingly.
So I right-clicked the legend, clicked on "select data" and then tried to edit a legend entry and enter a formula to replace the word with nothing but it doesn't accept it claiming the formula might be wrong or whatever.
When I try the same forumla in a cell in my worksheet it works fine.
=SUBSTITUTE('00000_Musterkunde'!Z5S1;"Umsatz ";"")
Am I doing something wrong or does Excel really not accept formulas in chart legends?
Best Regards
microsoft-excel-2013 charts
edited Sep 18 '15 at 14:13
dav
7,73532143
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
add a comment |
I have a chart in Excel with three legend entries. Each of them contains the word "Umsatz" and all of them are linked to cell values. Now I would like to remove that word from the entries with a function since it's also the title of my chart. However, I can't and don't want to edit the cell values accordingly.
So I right-clicked the legend, clicked on "select data" and then tried to edit a legend entry and enter a formula to replace the word with nothing but it doesn't accept it claiming the formula might be wrong or whatever.
When I try the same forumla in a cell in my worksheet it works fine.
=SUBSTITUTE('00000_Musterkunde'!Z5S1;"Umsatz ";"")
Am I doing something wrong or does Excel really not accept formulas in chart legends?
Best Regards
microsoft-excel-2013 charts
edited Sep 18 '15 at 14:13
dav
7,73532143
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
I have a chart in Excel with three legend entries. Each of them contains the word "Umsatz" and all of them are linked to cell values. Now I would like to remove that word from the entries with a function since it's also the title of my chart. However, I can't and don't want to edit the cell values accordingly.
So I right-clicked the legend, clicked on "select data" and then tried to edit a legend entry and enter a formula to replace the word with nothing but it doesn't accept it claiming the formula might be wrong or whatever.
When I try the same forumla in a cell in my worksheet it works fine.
=SUBSTITUTE('00000_Musterkunde'!Z5S1;"Umsatz ";"")
Am I doing something wrong or does Excel really not accept formulas in chart legends?
Best Regards
microsoft-excel-2013 charts
microsoft-excel-2013 charts
edited Sep 18 '15 at 14:13
dav
7,73532143
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
edited Sep 18 '15 at 14:13
dav
7,73532143
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
edited Sep 18 '15 at 14:13
dav
7,73532143
edited Sep 18 '15 at 14:13
dav
7,73532143
edited Sep 18 '15 at 14:13
dav
7,73532143
7,73532143
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
asked Sep 18 '15 at 13:10
mariu5mariu5
2115
2115
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
mariu5, please take a look at How to Show a Formula Result in an Excel Chart Legend, HTH.
The point is, you need to create first the formulas in the cells of the table, then you can reference to them!
Here is one way of making your legend follow the years in row 1 and
the averages in row 14.
First of all, create this formula in a blank cell, eg. A17:
=TEXT(B1,"0000")&" - Average "&TEXT(B14,"0.00")
Then create this formula in say A18:
=TEXT(C1,"0000")&" - Average "&TEXT(C14,"0.00")
These two formulae generate the legend you want to achieve, making use
of the TEXT function to determine the formatting of the numbers
involved.
Now go to your chart and choose the Source Data command from the Chart
menu. In the Source Data dialog box, make sure the Series tab is
selected.
Select the first series in the Series list. Click the Collapse Dialog
button to the right of the Name box. Click on cell A17 in the
worksheet. Click the Collapse Dialog button again to return to the
Source Data dialog box. Repeat the process for your second series
and you should have a legend that looks like this:
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
1
Yeah I found the same post along my research and it seems like there is no other way I guess. I wanted to avoid this solution though and tried putting my function directly into the input box where you can choose your cell with the text for the legend entry. Thanks for the help! I think I just write my desired text directly into the legend.
– mariu5
Sep 18 '15 at 13:37
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%2f974811%2fexcel-2013-adding-a-formula-function-to-a-chart-legend%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
mariu5, please take a look at How to Show a Formula Result in an Excel Chart Legend, HTH.
The point is, you need to create first the formulas in the cells of the table, then you can reference to them!
Here is one way of making your legend follow the years in row 1 and
the averages in row 14.
First of all, create this formula in a blank cell, eg. A17:
=TEXT(B1,"0000")&" - Average "&TEXT(B14,"0.00")
Then create this formula in say A18:
=TEXT(C1,"0000")&" - Average "&TEXT(C14,"0.00")
These two formulae generate the legend you want to achieve, making use
of the TEXT function to determine the formatting of the numbers
involved.
Now go to your chart and choose the Source Data command from the Chart
menu. In the Source Data dialog box, make sure the Series tab is
selected.
Select the first series in the Series list. Click the Collapse Dialog
button to the right of the Name box. Click on cell A17 in the
worksheet. Click the Collapse Dialog button again to return to the
Source Data dialog box. Repeat the process for your second series
and you should have a legend that looks like this:
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
1
Yeah I found the same post along my research and it seems like there is no other way I guess. I wanted to avoid this solution though and tried putting my function directly into the input box where you can choose your cell with the text for the legend entry. Thanks for the help! I think I just write my desired text directly into the legend.
– mariu5
Sep 18 '15 at 13:37
add a comment |
mariu5, please take a look at How to Show a Formula Result in an Excel Chart Legend, HTH.
The point is, you need to create first the formulas in the cells of the table, then you can reference to them!
Here is one way of making your legend follow the years in row 1 and
the averages in row 14.
First of all, create this formula in a blank cell, eg. A17:
=TEXT(B1,"0000")&" - Average "&TEXT(B14,"0.00")
Then create this formula in say A18:
=TEXT(C1,"0000")&" - Average "&TEXT(C14,"0.00")
These two formulae generate the legend you want to achieve, making use
of the TEXT function to determine the formatting of the numbers
involved.
Now go to your chart and choose the Source Data command from the Chart
menu. In the Source Data dialog box, make sure the Series tab is
selected.
Select the first series in the Series list. Click the Collapse Dialog
button to the right of the Name box. Click on cell A17 in the
worksheet. Click the Collapse Dialog button again to return to the
Source Data dialog box. Repeat the process for your second series
and you should have a legend that looks like this:
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
1
Yeah I found the same post along my research and it seems like there is no other way I guess. I wanted to avoid this solution though and tried putting my function directly into the input box where you can choose your cell with the text for the legend entry. Thanks for the help! I think I just write my desired text directly into the legend.
– mariu5
Sep 18 '15 at 13:37
add a comment |
mariu5, please take a look at How to Show a Formula Result in an Excel Chart Legend, HTH.
The point is, you need to create first the formulas in the cells of the table, then you can reference to them!
Here is one way of making your legend follow the years in row 1 and
the averages in row 14.
First of all, create this formula in a blank cell, eg. A17:
=TEXT(B1,"0000")&" - Average "&TEXT(B14,"0.00")
Then create this formula in say A18:
=TEXT(C1,"0000")&" - Average "&TEXT(C14,"0.00")
These two formulae generate the legend you want to achieve, making use
of the TEXT function to determine the formatting of the numbers
involved.
Now go to your chart and choose the Source Data command from the Chart
menu. In the Source Data dialog box, make sure the Series tab is
selected.
Select the first series in the Series list. Click the Collapse Dialog
button to the right of the Name box. Click on cell A17 in the
worksheet. Click the Collapse Dialog button again to return to the
Source Data dialog box. Repeat the process for your second series
and you should have a legend that looks like this:
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
mariu5, please take a look at How to Show a Formula Result in an Excel Chart Legend, HTH.
The point is, you need to create first the formulas in the cells of the table, then you can reference to them!
Here is one way of making your legend follow the years in row 1 and
the averages in row 14.
First of all, create this formula in a blank cell, eg. A17:
=TEXT(B1,"0000")&" - Average "&TEXT(B14,"0.00")
Then create this formula in say A18:
=TEXT(C1,"0000")&" - Average "&TEXT(C14,"0.00")
These two formulae generate the legend you want to achieve, making use
of the TEXT function to determine the formatting of the numbers
involved.
Now go to your chart and choose the Source Data command from the Chart
menu. In the Source Data dialog box, make sure the Series tab is
selected.
Select the first series in the Series list. Click the Collapse Dialog
button to the right of the Name box. Click on cell A17 in the
worksheet. Click the Collapse Dialog button again to return to the
Source Data dialog box. Repeat the process for your second series
and you should have a legend that looks like this:
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
edited Sep 18 '15 at 13:34
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
answered Sep 18 '15 at 13:20
duDEduDE
13.3k53138
13.3k53138
1
Yeah I found the same post along my research and it seems like there is no other way I guess. I wanted to avoid this solution though and tried putting my function directly into the input box where you can choose your cell with the text for the legend entry. Thanks for the help! I think I just write my desired text directly into the legend.
– mariu5
Sep 18 '15 at 13:37
add a comment |
1
Yeah I found the same post along my research and it seems like there is no other way I guess. I wanted to avoid this solution though and tried putting my function directly into the input box where you can choose your cell with the text for the legend entry. Thanks for the help! I think I just write my desired text directly into the legend.
– mariu5
Sep 18 '15 at 13:37
1
1
Yeah I found the same post along my research and it seems like there is no other way I guess. I wanted to avoid this solution though and tried putting my function directly into the input box where you can choose your cell with the text for the legend entry. Thanks for the help! I think I just write my desired text directly into the legend.
– mariu5
Sep 18 '15 at 13:37
Yeah I found the same post along my research and it seems like there is no other way I guess. I wanted to avoid this solution though and tried putting my function directly into the input box where you can choose your cell with the text for the legend entry. Thanks for the help! I think I just write my desired text directly into the legend.
– mariu5
Sep 18 '15 at 13:37
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%2f974811%2fexcel-2013-adding-a-formula-function-to-a-chart-legend%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
