Excel Chart Series: column has data, but dialog box shows zeros
Excel 2013. My machine is a 64-bit running Windows 7.
I have an .xslm file with a chart. The chart is created from a few columns of data. The data in the columns is populated by reading data from a bunch of other excel files. This is a 2-D line chart with the horizontal axis as dates (in text) and the vertical axis as hours (expressed as h:mm) with the actual values between 541:50 and 757:00. For most of the cases, it works fine. However, for 3 cases, My graph flatlines at zero. I look at the column data (column G, representing Cumulative Hours) - it is correct! When I edit the series data, I can see that it correctly has Series values = "$G$2:$G$100".
That's fine, however the edit series dialog box also shows the values of those cells and the values are all zeros (which is what is graphed) even though I can see the contents of the cells are not zero.
Next bit of weirdness. I have another graph of another column. It shows the graph of the next column (H, which is derived from G), but it is correct. It doesn't flatline.
(Click image to enlarge)
Note:
- The column G2:G100 does have data.
- The chart is flatlined.
- The dialog box for the data series shows the correct range G2:G100, but
- The values to the right of the dialog say 0.00, 0.00, ...
microsoft-excel-2013 charts dialog
edited Dec 9 '15 at 13:21
karel
9,17793138
asked Dec 8 '15 at 19:47
elbillaf
10113
add a comment |
Excel 2013. My machine is a 64-bit running Windows 7.
I have an .xslm file with a chart. The chart is created from a few columns of data. The data in the columns is populated by reading data from a bunch of other excel files. This is a 2-D line chart with the horizontal axis as dates (in text) and the vertical axis as hours (expressed as h:mm) with the actual values between 541:50 and 757:00. For most of the cases, it works fine. However, for 3 cases, My graph flatlines at zero. I look at the column data (column G, representing Cumulative Hours) - it is correct! When I edit the series data, I can see that it correctly has Series values = "$G$2:$G$100".
That's fine, however the edit series dialog box also shows the values of those cells and the values are all zeros (which is what is graphed) even though I can see the contents of the cells are not zero.
Next bit of weirdness. I have another graph of another column. It shows the graph of the next column (H, which is derived from G), but it is correct. It doesn't flatline.
(Click image to enlarge)
Note:
- The column G2:G100 does have data.
- The chart is flatlined.
- The dialog box for the data series shows the correct range G2:G100, but
- The values to the right of the dialog say 0.00, 0.00, ...
microsoft-excel-2013 charts dialog
edited Dec 9 '15 at 13:21
karel
9,17793138
asked Dec 8 '15 at 19:47
elbillaf
10113
Can you post some screengrabs so we can see what you're looking at? Rename labels if you need to make it anonymous.
– Andi Mohr
Dec 9 '15 at 10:02
I added a screen grab that illustrates (I think) all the important facts ... except that the format of column G is custom "[h]:mm" (although I'm not sure that matters).
– elbillaf
Dec 9 '15 at 13:11
1
I see that in column G your values are left-aligned. Have you set this deliberately? Typically a left-aligned number means it's actually a bit of text (eg'541:50) rather than a true number. Occasionally I find Excel gets confused and starts treating a true number as text. To get round this, try copying column G and pasting special as values in an unused column. Then copy and paste the new column over the original (take care to preserve the original calculation so you can reapply it later). This may make your column start working like numbers again. Give it a go!
– Andi Mohr
Dec 9 '15 at 17:44
1
Bizarre. That did not work. What did work is this: recall the hrs col is derived from the "messed up" cumhrs col. I created a NEW col where I manually type the value of the first value of cumhrs and then recreate cumhrs by adding in the hrs...this gives the correct column values. I then copy THAT new column back to the original, pasting values and format .... and THAT works. Very interesting. This is annoying, because this needs to run automatically...but I've got a temporary fix...and a hint..possibly an invisible character in some cell of the source data. Thanks!
– elbillaf
Dec 10 '15 at 14:27
add a comment |
Excel 2013. My machine is a 64-bit running Windows 7.
I have an .xslm file with a chart. The chart is created from a few columns of data. The data in the columns is populated by reading data from a bunch of other excel files. This is a 2-D line chart with the horizontal axis as dates (in text) and the vertical axis as hours (expressed as h:mm) with the actual values between 541:50 and 757:00. For most of the cases, it works fine. However, for 3 cases, My graph flatlines at zero. I look at the column data (column G, representing Cumulative Hours) - it is correct! When I edit the series data, I can see that it correctly has Series values = "$G$2:$G$100".
That's fine, however the edit series dialog box also shows the values of those cells and the values are all zeros (which is what is graphed) even though I can see the contents of the cells are not zero.
Next bit of weirdness. I have another graph of another column. It shows the graph of the next column (H, which is derived from G), but it is correct. It doesn't flatline.
(Click image to enlarge)
Note:
- The column G2:G100 does have data.
- The chart is flatlined.
- The dialog box for the data series shows the correct range G2:G100, but
- The values to the right of the dialog say 0.00, 0.00, ...
microsoft-excel-2013 charts dialog
edited Dec 9 '15 at 13:21
karel
9,17793138
asked Dec 8 '15 at 19:47
elbillaf
10113
Excel 2013. My machine is a 64-bit running Windows 7.
I have an .xslm file with a chart. The chart is created from a few columns of data. The data in the columns is populated by reading data from a bunch of other excel files. This is a 2-D line chart with the horizontal axis as dates (in text) and the vertical axis as hours (expressed as h:mm) with the actual values between 541:50 and 757:00. For most of the cases, it works fine. However, for 3 cases, My graph flatlines at zero. I look at the column data (column G, representing Cumulative Hours) - it is correct! When I edit the series data, I can see that it correctly has Series values = "$G$2:$G$100".
That's fine, however the edit series dialog box also shows the values of those cells and the values are all zeros (which is what is graphed) even though I can see the contents of the cells are not zero.
Next bit of weirdness. I have another graph of another column. It shows the graph of the next column (H, which is derived from G), but it is correct. It doesn't flatline.
(Click image to enlarge)
Note:
- The column G2:G100 does have data.
- The chart is flatlined.
- The dialog box for the data series shows the correct range G2:G100, but
- The values to the right of the dialog say 0.00, 0.00, ...
microsoft-excel-2013 charts dialog
microsoft-excel-2013 charts dialog
edited Dec 9 '15 at 13:21
karel
9,17793138
asked Dec 8 '15 at 19:47
elbillaf
10113
edited Dec 9 '15 at 13:21
karel
9,17793138
asked Dec 8 '15 at 19:47
elbillaf
10113
edited Dec 9 '15 at 13:21
karel
9,17793138
edited Dec 9 '15 at 13:21
karel
9,17793138
edited Dec 9 '15 at 13:21
karel
9,17793138
9,17793138
asked Dec 8 '15 at 19:47
elbillaf
10113
asked Dec 8 '15 at 19:47
elbillaf
10113
asked Dec 8 '15 at 19:47
elbillaf
10113
10113
Can you post some screengrabs so we can see what you're looking at? Rename labels if you need to make it anonymous.
– Andi Mohr
Dec 9 '15 at 10:02
I added a screen grab that illustrates (I think) all the important facts ... except that the format of column G is custom "[h]:mm" (although I'm not sure that matters).
– elbillaf
Dec 9 '15 at 13:11
1
I see that in column G your values are left-aligned. Have you set this deliberately? Typically a left-aligned number means it's actually a bit of text (eg'541:50) rather than a true number. Occasionally I find Excel gets confused and starts treating a true number as text. To get round this, try copying column G and pasting special as values in an unused column. Then copy and paste the new column over the original (take care to preserve the original calculation so you can reapply it later). This may make your column start working like numbers again. Give it a go!
– Andi Mohr
Dec 9 '15 at 17:44
1
Bizarre. That did not work. What did work is this: recall the hrs col is derived from the "messed up" cumhrs col. I created a NEW col where I manually type the value of the first value of cumhrs and then recreate cumhrs by adding in the hrs...this gives the correct column values. I then copy THAT new column back to the original, pasting values and format .... and THAT works. Very interesting. This is annoying, because this needs to run automatically...but I've got a temporary fix...and a hint..possibly an invisible character in some cell of the source data. Thanks!
– elbillaf
Dec 10 '15 at 14:27
add a comment |
Can you post some screengrabs so we can see what you're looking at? Rename labels if you need to make it anonymous.
– Andi Mohr
Dec 9 '15 at 10:02
I added a screen grab that illustrates (I think) all the important facts ... except that the format of column G is custom "[h]:mm" (although I'm not sure that matters).
– elbillaf
Dec 9 '15 at 13:11
1
I see that in column G your values are left-aligned. Have you set this deliberately? Typically a left-aligned number means it's actually a bit of text (eg'541:50) rather than a true number. Occasionally I find Excel gets confused and starts treating a true number as text. To get round this, try copying column G and pasting special as values in an unused column. Then copy and paste the new column over the original (take care to preserve the original calculation so you can reapply it later). This may make your column start working like numbers again. Give it a go!
– Andi Mohr
Dec 9 '15 at 17:44
1
Bizarre. That did not work. What did work is this: recall the hrs col is derived from the "messed up" cumhrs col. I created a NEW col where I manually type the value of the first value of cumhrs and then recreate cumhrs by adding in the hrs...this gives the correct column values. I then copy THAT new column back to the original, pasting values and format .... and THAT works. Very interesting. This is annoying, because this needs to run automatically...but I've got a temporary fix...and a hint..possibly an invisible character in some cell of the source data. Thanks!
– elbillaf
Dec 10 '15 at 14:27
Can you post some screengrabs so we can see what you're looking at? Rename labels if you need to make it anonymous.
– Andi Mohr
Dec 9 '15 at 10:02
Can you post some screengrabs so we can see what you're looking at? Rename labels if you need to make it anonymous.
– Andi Mohr
Dec 9 '15 at 10:02
I added a screen grab that illustrates (I think) all the important facts ... except that the format of column G is custom "[h]:mm" (although I'm not sure that matters).
– elbillaf
Dec 9 '15 at 13:11
I added a screen grab that illustrates (I think) all the important facts ... except that the format of column G is custom "[h]:mm" (although I'm not sure that matters).
– elbillaf
Dec 9 '15 at 13:11
1
1
I see that in column G your values are left-aligned. Have you set this deliberately? Typically a left-aligned number means it's actually a bit of text (eg
'541:50) rather than a true number. Occasionally I find Excel gets confused and starts treating a true number as text. To get round this, try copying column G and pasting special as values in an unused column. Then copy and paste the new column over the original (take care to preserve the original calculation so you can reapply it later). This may make your column start working like numbers again. Give it a go!– Andi Mohr
Dec 9 '15 at 17:44
I see that in column G your values are left-aligned. Have you set this deliberately? Typically a left-aligned number means it's actually a bit of text (eg
'541:50) rather than a true number. Occasionally I find Excel gets confused and starts treating a true number as text. To get round this, try copying column G and pasting special as values in an unused column. Then copy and paste the new column over the original (take care to preserve the original calculation so you can reapply it later). This may make your column start working like numbers again. Give it a go!– Andi Mohr
Dec 9 '15 at 17:44
1
1
Bizarre. That did not work. What did work is this: recall the hrs col is derived from the "messed up" cumhrs col. I created a NEW col where I manually type the value of the first value of cumhrs and then recreate cumhrs by adding in the hrs...this gives the correct column values. I then copy THAT new column back to the original, pasting values and format .... and THAT works. Very interesting. This is annoying, because this needs to run automatically...but I've got a temporary fix...and a hint..possibly an invisible character in some cell of the source data. Thanks!
– elbillaf
Dec 10 '15 at 14:27
Bizarre. That did not work. What did work is this: recall the hrs col is derived from the "messed up" cumhrs col. I created a NEW col where I manually type the value of the first value of cumhrs and then recreate cumhrs by adding in the hrs...this gives the correct column values. I then copy THAT new column back to the original, pasting values and format .... and THAT works. Very interesting. This is annoying, because this needs to run automatically...but I've got a temporary fix...and a hint..possibly an invisible character in some cell of the source data. Thanks!
– elbillaf
Dec 10 '15 at 14:27
add a comment |
2 Answers
2
active
oldest
votes
I figured it out, thanks to a hint from Andi, above.
Recall, I am populating the column I want to chart by going through a bunch of other excel spread sheets and locating the appropriate data. In my VBA code, I had a line that read:
A)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9)
I changed this to:
B)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9).Value
It's interesting, because the data in the column produced by A) was right, visually ... but internally, it was screwed up somehow. I suspect, but I am not sure that there is some non-visible inconsistency amongst the manually generated source data files (about 100 of them). And A) worked okay for charts on some of the data - but not all of it. B) works for everything I tested.
answered Dec 10 '15 at 19:17
elbillaf
10113
add a comment |
so there are probably 3 different issues with this kind of error:
1) there is a space before the number
2) the number is not aligned to the right (because it has a space in front of it?)
3) the numbers are separated by DOTS(.) not commas(,) (might be a language issue)
spent like 3 hours trying to figure this stuff out, lol................................
answered Jul 23 '17 at 23:54
jimmy5
13
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%2f1010762%2fexcel-chart-series-column-has-data-but-dialog-box-shows-zeros%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I figured it out, thanks to a hint from Andi, above.
Recall, I am populating the column I want to chart by going through a bunch of other excel spread sheets and locating the appropriate data. In my VBA code, I had a line that read:
A)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9)
I changed this to:
B)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9).Value
It's interesting, because the data in the column produced by A) was right, visually ... but internally, it was screwed up somehow. I suspect, but I am not sure that there is some non-visible inconsistency amongst the manually generated source data files (about 100 of them). And A) worked okay for charts on some of the data - but not all of it. B) works for everything I tested.
answered Dec 10 '15 at 19:17
elbillaf
10113
add a comment |
I figured it out, thanks to a hint from Andi, above.
Recall, I am populating the column I want to chart by going through a bunch of other excel spread sheets and locating the appropriate data. In my VBA code, I had a line that read:
A)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9)
I changed this to:
B)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9).Value
It's interesting, because the data in the column produced by A) was right, visually ... but internally, it was screwed up somehow. I suspect, but I am not sure that there is some non-visible inconsistency amongst the manually generated source data files (about 100 of them). And A) worked okay for charts on some of the data - but not all of it. B) works for everything I tested.
answered Dec 10 '15 at 19:17
elbillaf
10113
add a comment |
I figured it out, thanks to a hint from Andi, above.
Recall, I am populating the column I want to chart by going through a bunch of other excel spread sheets and locating the appropriate data. In my VBA code, I had a line that read:
A)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9)
I changed this to:
B)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9).Value
It's interesting, because the data in the column produced by A) was right, visually ... but internally, it was screwed up somehow. I suspect, but I am not sure that there is some non-visible inconsistency amongst the manually generated source data files (about 100 of them). And A) worked okay for charts on some of the data - but not all of it. B) works for everything I tested.
answered Dec 10 '15 at 19:17
elbillaf
10113
I figured it out, thanks to a hint from Andi, above.
Recall, I am populating the column I want to chart by going through a bunch of other excel spread sheets and locating the appropriate data. In my VBA code, I had a line that read:
A)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9)
I changed this to:
B)
shtA.Cells(currentRow, 7) = shtSource.Cells(rng.Row - 1, 9).Value
It's interesting, because the data in the column produced by A) was right, visually ... but internally, it was screwed up somehow. I suspect, but I am not sure that there is some non-visible inconsistency amongst the manually generated source data files (about 100 of them). And A) worked okay for charts on some of the data - but not all of it. B) works for everything I tested.
answered Dec 10 '15 at 19:17
elbillaf
10113
answered Dec 10 '15 at 19:17
elbillaf
10113
answered Dec 10 '15 at 19:17
elbillaf
10113
answered Dec 10 '15 at 19:17
elbillaf
10113
10113
add a comment |
add a comment |
so there are probably 3 different issues with this kind of error:
1) there is a space before the number
2) the number is not aligned to the right (because it has a space in front of it?)
3) the numbers are separated by DOTS(.) not commas(,) (might be a language issue)
spent like 3 hours trying to figure this stuff out, lol................................
answered Jul 23 '17 at 23:54
jimmy5
13
add a comment |
so there are probably 3 different issues with this kind of error:
1) there is a space before the number
2) the number is not aligned to the right (because it has a space in front of it?)
3) the numbers are separated by DOTS(.) not commas(,) (might be a language issue)
spent like 3 hours trying to figure this stuff out, lol................................
answered Jul 23 '17 at 23:54
jimmy5
13
add a comment |
so there are probably 3 different issues with this kind of error:
1) there is a space before the number
2) the number is not aligned to the right (because it has a space in front of it?)
3) the numbers are separated by DOTS(.) not commas(,) (might be a language issue)
spent like 3 hours trying to figure this stuff out, lol................................
answered Jul 23 '17 at 23:54
jimmy5
13
so there are probably 3 different issues with this kind of error:
1) there is a space before the number
2) the number is not aligned to the right (because it has a space in front of it?)
3) the numbers are separated by DOTS(.) not commas(,) (might be a language issue)
spent like 3 hours trying to figure this stuff out, lol................................
answered Jul 23 '17 at 23:54
jimmy5
13
edited Jul 24 '17 at 1:56
answered Jul 23 '17 at 23:54
jimmy5
13
answered Jul 23 '17 at 23:54
jimmy5
13
answered Jul 23 '17 at 23:54
jimmy5
13
13
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1010762%2fexcel-chart-series-column-has-data-but-dialog-box-shows-zeros%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
Can you post some screengrabs so we can see what you're looking at? Rename labels if you need to make it anonymous.
– Andi Mohr
Dec 9 '15 at 10:02
I added a screen grab that illustrates (I think) all the important facts ... except that the format of column G is custom "[h]:mm" (although I'm not sure that matters).
– elbillaf
Dec 9 '15 at 13:11
1
I see that in column G your values are left-aligned. Have you set this deliberately? Typically a left-aligned number means it's actually a bit of text (eg
'541:50) rather than a true number. Occasionally I find Excel gets confused and starts treating a true number as text. To get round this, try copying column G and pasting special as values in an unused column. Then copy and paste the new column over the original (take care to preserve the original calculation so you can reapply it later). This may make your column start working like numbers again. Give it a go!– Andi Mohr
Dec 9 '15 at 17:44
1
Bizarre. That did not work. What did work is this: recall the hrs col is derived from the "messed up" cumhrs col. I created a NEW col where I manually type the value of the first value of cumhrs and then recreate cumhrs by adding in the hrs...this gives the correct column values. I then copy THAT new column back to the original, pasting values and format .... and THAT works. Very interesting. This is annoying, because this needs to run automatically...but I've got a temporary fix...and a hint..possibly an invisible character in some cell of the source data. Thanks!
– elbillaf
Dec 10 '15 at 14:27