Excel line diagram x-axis labels by week
I have a data series with daily values which I would like to display as a line diagram, like:
| date | value |
|------------|-------|
| 12/12/2018 | 1202 |
| 13/12/2018 | 283 |
| 14/12/2018 | 226 |
| 15/12/2018 | 12 |
| 16/12/2018 | 55 |
| 17/12/2018 | 153 |
| 18/12/2018 | 123 |
The x-axis should, however, not contain the actual dates,
but instead be labeled by week number as shown here:
with the week numbers in the middle of each week. Is there any way to do this? Here is the full sample dataset.
microsoft-excel charts microsoft-powerpoint
edited Feb 11 at 17:01
Scott
16.1k113990
asked Feb 11 at 16:16
ietzietz
11
add a comment |
I have a data series with daily values which I would like to display as a line diagram, like:
| date | value |
|------------|-------|
| 12/12/2018 | 1202 |
| 13/12/2018 | 283 |
| 14/12/2018 | 226 |
| 15/12/2018 | 12 |
| 16/12/2018 | 55 |
| 17/12/2018 | 153 |
| 18/12/2018 | 123 |
The x-axis should, however, not contain the actual dates,
but instead be labeled by week number as shown here:
with the week numbers in the middle of each week. Is there any way to do this? Here is the full sample dataset.
microsoft-excel charts microsoft-powerpoint
edited Feb 11 at 17:01
Scott
16.1k113990
asked Feb 11 at 16:16
ietzietz
11
add a comment |
I have a data series with daily values which I would like to display as a line diagram, like:
| date | value |
|------------|-------|
| 12/12/2018 | 1202 |
| 13/12/2018 | 283 |
| 14/12/2018 | 226 |
| 15/12/2018 | 12 |
| 16/12/2018 | 55 |
| 17/12/2018 | 153 |
| 18/12/2018 | 123 |
The x-axis should, however, not contain the actual dates,
but instead be labeled by week number as shown here:
with the week numbers in the middle of each week. Is there any way to do this? Here is the full sample dataset.
microsoft-excel charts microsoft-powerpoint
edited Feb 11 at 17:01
Scott
16.1k113990
asked Feb 11 at 16:16
ietzietz
11
I have a data series with daily values which I would like to display as a line diagram, like:
| date | value |
|------------|-------|
| 12/12/2018 | 1202 |
| 13/12/2018 | 283 |
| 14/12/2018 | 226 |
| 15/12/2018 | 12 |
| 16/12/2018 | 55 |
| 17/12/2018 | 153 |
| 18/12/2018 | 123 |
The x-axis should, however, not contain the actual dates,
but instead be labeled by week number as shown here:
with the week numbers in the middle of each week. Is there any way to do this? Here is the full sample dataset.
microsoft-excel charts microsoft-powerpoint
microsoft-excel charts microsoft-powerpoint
edited Feb 11 at 17:01
Scott
16.1k113990
asked Feb 11 at 16:16
ietzietz
11
edited Feb 11 at 17:01
Scott
16.1k113990
asked Feb 11 at 16:16
ietzietz
11
edited Feb 11 at 17:01
Scott
16.1k113990
edited Feb 11 at 17:01
Scott
16.1k113990
edited Feb 11 at 17:01
Scott
16.1k113990
16.1k113990
asked Feb 11 at 16:16
ietzietz
11
asked Feb 11 at 16:16
ietzietz
11
asked Feb 11 at 16:16
ietzietz
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This will work best if you have data for every day of the week, if you don't, some labels may be missing and others will not be evenly distributed along the axis.
1) Insert a column to the left of your dates.
2) In this column, insert the following formula:
=IF(WEEKDAY(B2,2)=3,WEEKNUM(B2),"")
and populate down. You should have something that looks like this:
3) Select the three columns and insert a line graph.
4) Remove the date column from the plotted data:
You should then have a chart that looks like this:
If you need the week numbers to be distributed evenly on the axis, you'll need to generate a new tables will all the dates (use the same formula to the left of the dates and a VLOOKUP for your data -- the cells filled with #N/A won't appear on the chart)
If you need different week numbers, you can adjust the formula to taste.
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
This doesn’t look very much like the chart in the question. Did you use the OP’s data?
– Scott
Feb 11 at 19:12
No, I just plugged a few numbers in. The values don't matter the method does
– cybernetic.nomad
Feb 11 at 19:22
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%2f1404495%2fexcel-line-diagram-x-axis-labels-by-week%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
This will work best if you have data for every day of the week, if you don't, some labels may be missing and others will not be evenly distributed along the axis.
1) Insert a column to the left of your dates.
2) In this column, insert the following formula:
=IF(WEEKDAY(B2,2)=3,WEEKNUM(B2),"")
and populate down. You should have something that looks like this:
3) Select the three columns and insert a line graph.
4) Remove the date column from the plotted data:
You should then have a chart that looks like this:
If you need the week numbers to be distributed evenly on the axis, you'll need to generate a new tables will all the dates (use the same formula to the left of the dates and a VLOOKUP for your data -- the cells filled with #N/A won't appear on the chart)
If you need different week numbers, you can adjust the formula to taste.
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
This doesn’t look very much like the chart in the question. Did you use the OP’s data?
– Scott
Feb 11 at 19:12
No, I just plugged a few numbers in. The values don't matter the method does
– cybernetic.nomad
Feb 11 at 19:22
add a comment |
This will work best if you have data for every day of the week, if you don't, some labels may be missing and others will not be evenly distributed along the axis.
1) Insert a column to the left of your dates.
2) In this column, insert the following formula:
=IF(WEEKDAY(B2,2)=3,WEEKNUM(B2),"")
and populate down. You should have something that looks like this:
3) Select the three columns and insert a line graph.
4) Remove the date column from the plotted data:
You should then have a chart that looks like this:
If you need the week numbers to be distributed evenly on the axis, you'll need to generate a new tables will all the dates (use the same formula to the left of the dates and a VLOOKUP for your data -- the cells filled with #N/A won't appear on the chart)
If you need different week numbers, you can adjust the formula to taste.
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
This doesn’t look very much like the chart in the question. Did you use the OP’s data?
– Scott
Feb 11 at 19:12
No, I just plugged a few numbers in. The values don't matter the method does
– cybernetic.nomad
Feb 11 at 19:22
add a comment |
This will work best if you have data for every day of the week, if you don't, some labels may be missing and others will not be evenly distributed along the axis.
1) Insert a column to the left of your dates.
2) In this column, insert the following formula:
=IF(WEEKDAY(B2,2)=3,WEEKNUM(B2),"")
and populate down. You should have something that looks like this:
3) Select the three columns and insert a line graph.
4) Remove the date column from the plotted data:
You should then have a chart that looks like this:
If you need the week numbers to be distributed evenly on the axis, you'll need to generate a new tables will all the dates (use the same formula to the left of the dates and a VLOOKUP for your data -- the cells filled with #N/A won't appear on the chart)
If you need different week numbers, you can adjust the formula to taste.
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
This will work best if you have data for every day of the week, if you don't, some labels may be missing and others will not be evenly distributed along the axis.
1) Insert a column to the left of your dates.
2) In this column, insert the following formula:
=IF(WEEKDAY(B2,2)=3,WEEKNUM(B2),"")
and populate down. You should have something that looks like this:
3) Select the three columns and insert a line graph.
4) Remove the date column from the plotted data:
You should then have a chart that looks like this:
If you need the week numbers to be distributed evenly on the axis, you'll need to generate a new tables will all the dates (use the same formula to the left of the dates and a VLOOKUP for your data -- the cells filled with #N/A won't appear on the chart)
If you need different week numbers, you can adjust the formula to taste.
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
answered Feb 11 at 18:17
cybernetic.nomadcybernetic.nomad
2,591617
2,591617
This doesn’t look very much like the chart in the question. Did you use the OP’s data?
– Scott
Feb 11 at 19:12
No, I just plugged a few numbers in. The values don't matter the method does
– cybernetic.nomad
Feb 11 at 19:22
add a comment |
This doesn’t look very much like the chart in the question. Did you use the OP’s data?
– Scott
Feb 11 at 19:12
No, I just plugged a few numbers in. The values don't matter the method does
– cybernetic.nomad
Feb 11 at 19:22
This doesn’t look very much like the chart in the question. Did you use the OP’s data?
– Scott
Feb 11 at 19:12
This doesn’t look very much like the chart in the question. Did you use the OP’s data?
– Scott
Feb 11 at 19:12
No, I just plugged a few numbers in. The values don't matter the method does
– cybernetic.nomad
Feb 11 at 19:22
No, I just plugged a few numbers in. The values don't matter the method does
– cybernetic.nomad
Feb 11 at 19:22
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%2f1404495%2fexcel-line-diagram-x-axis-labels-by-week%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
