Keep Original Sorting of Labels in Excel Pivot Table
I have a list of projects and the quarter that they are to be completed in. I used this data to create a PivotTable that puts a "1" under the column for the quarter when the project is to be completed.
How can I keep the sorting of the projects from the original table where they are listed by quarter, then by name?
My real data is being pulled from a SharePoint 2013 list and displayed in an Excel web part on the site, so I can't change it to manipulate the names and there could be more projects added at any time.
microsoft-excel
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
add a comment |
I have a list of projects and the quarter that they are to be completed in. I used this data to create a PivotTable that puts a "1" under the column for the quarter when the project is to be completed.
How can I keep the sorting of the projects from the original table where they are listed by quarter, then by name?
My real data is being pulled from a SharePoint 2013 list and displayed in an Excel web part on the site, so I can't change it to manipulate the names and there could be more projects added at any time.
microsoft-excel
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
add a comment |
I have a list of projects and the quarter that they are to be completed in. I used this data to create a PivotTable that puts a "1" under the column for the quarter when the project is to be completed.
How can I keep the sorting of the projects from the original table where they are listed by quarter, then by name?
My real data is being pulled from a SharePoint 2013 list and displayed in an Excel web part on the site, so I can't change it to manipulate the names and there could be more projects added at any time.
microsoft-excel
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
I have a list of projects and the quarter that they are to be completed in. I used this data to create a PivotTable that puts a "1" under the column for the quarter when the project is to be completed.
How can I keep the sorting of the projects from the original table where they are listed by quarter, then by name?
My real data is being pulled from a SharePoint 2013 list and displayed in an Excel web part on the site, so I can't change it to manipulate the names and there could be more projects added at any time.
microsoft-excel
microsoft-excel
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
edited Jan 13 '15 at 14:00
Blaise Alicki
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
asked Jan 12 '15 at 21:48
Blaise AlickiBlaise Alicki
10113
10113
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There is one option that I can think of which works just out-of-the-box and it's explained by Microsoft in this article. The trick is to create a custom field and use that as a sorting key. A custom field is an ordered list, such as the days of the week or the months of the year.
One significant disadvantage of this method is that it does not automatically handle new items in your list. In your example: if there is a new project, you'll have to re-create the custom list with the new item in it.
I'll walk you through it (since you don't state the version of Excel you're using, I'll follow the default menu's for my version, which is Excel 2010):
- Go to
File,Options,Advancedand all the way at the bottom clickEdit Custom Lists...
- In the 'Import list from cells' input box, select the original list of projects (A2..A7 in your example) and click
Importand clickOK. Now exit the options dialog. - In the sorting options of the project field in the Pivot table, select
More sort options...and clickMore options...in the next dialog. Deselect the checkbox at the top and select the custom list in the dropdown (First key sort order) and clickOKtwice.
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
Thanks, but the pivot table is going to live in an Excel web part, so I need it to handle new items automatically.
– Blaise Alicki
Jan 13 '15 at 13:59
Is it possible in your scenario to use Power Query? In that case, you could add an incementing index, and sort on the minimum value of that index and make that value invisible in the resulting pivot table.
– agtoever
Jan 16 '15 at 18:11
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%2f863905%2fkeep-original-sorting-of-labels-in-excel-pivot-table%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 is one option that I can think of which works just out-of-the-box and it's explained by Microsoft in this article. The trick is to create a custom field and use that as a sorting key. A custom field is an ordered list, such as the days of the week or the months of the year.
One significant disadvantage of this method is that it does not automatically handle new items in your list. In your example: if there is a new project, you'll have to re-create the custom list with the new item in it.
I'll walk you through it (since you don't state the version of Excel you're using, I'll follow the default menu's for my version, which is Excel 2010):
- Go to
File,Options,Advancedand all the way at the bottom clickEdit Custom Lists...
- In the 'Import list from cells' input box, select the original list of projects (A2..A7 in your example) and click
Importand clickOK. Now exit the options dialog. - In the sorting options of the project field in the Pivot table, select
More sort options...and clickMore options...in the next dialog. Deselect the checkbox at the top and select the custom list in the dropdown (First key sort order) and clickOKtwice.
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
Thanks, but the pivot table is going to live in an Excel web part, so I need it to handle new items automatically.
– Blaise Alicki
Jan 13 '15 at 13:59
Is it possible in your scenario to use Power Query? In that case, you could add an incementing index, and sort on the minimum value of that index and make that value invisible in the resulting pivot table.
– agtoever
Jan 16 '15 at 18:11
add a comment |
There is one option that I can think of which works just out-of-the-box and it's explained by Microsoft in this article. The trick is to create a custom field and use that as a sorting key. A custom field is an ordered list, such as the days of the week or the months of the year.
One significant disadvantage of this method is that it does not automatically handle new items in your list. In your example: if there is a new project, you'll have to re-create the custom list with the new item in it.
I'll walk you through it (since you don't state the version of Excel you're using, I'll follow the default menu's for my version, which is Excel 2010):
- Go to
File,Options,Advancedand all the way at the bottom clickEdit Custom Lists...
- In the 'Import list from cells' input box, select the original list of projects (A2..A7 in your example) and click
Importand clickOK. Now exit the options dialog. - In the sorting options of the project field in the Pivot table, select
More sort options...and clickMore options...in the next dialog. Deselect the checkbox at the top and select the custom list in the dropdown (First key sort order) and clickOKtwice.
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
Thanks, but the pivot table is going to live in an Excel web part, so I need it to handle new items automatically.
– Blaise Alicki
Jan 13 '15 at 13:59
Is it possible in your scenario to use Power Query? In that case, you could add an incementing index, and sort on the minimum value of that index and make that value invisible in the resulting pivot table.
– agtoever
Jan 16 '15 at 18:11
add a comment |
There is one option that I can think of which works just out-of-the-box and it's explained by Microsoft in this article. The trick is to create a custom field and use that as a sorting key. A custom field is an ordered list, such as the days of the week or the months of the year.
One significant disadvantage of this method is that it does not automatically handle new items in your list. In your example: if there is a new project, you'll have to re-create the custom list with the new item in it.
I'll walk you through it (since you don't state the version of Excel you're using, I'll follow the default menu's for my version, which is Excel 2010):
- Go to
File,Options,Advancedand all the way at the bottom clickEdit Custom Lists...
- In the 'Import list from cells' input box, select the original list of projects (A2..A7 in your example) and click
Importand clickOK. Now exit the options dialog. - In the sorting options of the project field in the Pivot table, select
More sort options...and clickMore options...in the next dialog. Deselect the checkbox at the top and select the custom list in the dropdown (First key sort order) and clickOKtwice.
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
There is one option that I can think of which works just out-of-the-box and it's explained by Microsoft in this article. The trick is to create a custom field and use that as a sorting key. A custom field is an ordered list, such as the days of the week or the months of the year.
One significant disadvantage of this method is that it does not automatically handle new items in your list. In your example: if there is a new project, you'll have to re-create the custom list with the new item in it.
I'll walk you through it (since you don't state the version of Excel you're using, I'll follow the default menu's for my version, which is Excel 2010):
- Go to
File,Options,Advancedand all the way at the bottom clickEdit Custom Lists...
- In the 'Import list from cells' input box, select the original list of projects (A2..A7 in your example) and click
Importand clickOK. Now exit the options dialog. - In the sorting options of the project field in the Pivot table, select
More sort options...and clickMore options...in the next dialog. Deselect the checkbox at the top and select the custom list in the dropdown (First key sort order) and clickOKtwice.
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
edited Jan 13 '15 at 11:05
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
answered Jan 13 '15 at 10:59
agtoeveragtoever
5,13911431
5,13911431
Thanks, but the pivot table is going to live in an Excel web part, so I need it to handle new items automatically.
– Blaise Alicki
Jan 13 '15 at 13:59
Is it possible in your scenario to use Power Query? In that case, you could add an incementing index, and sort on the minimum value of that index and make that value invisible in the resulting pivot table.
– agtoever
Jan 16 '15 at 18:11
add a comment |
Thanks, but the pivot table is going to live in an Excel web part, so I need it to handle new items automatically.
– Blaise Alicki
Jan 13 '15 at 13:59
Is it possible in your scenario to use Power Query? In that case, you could add an incementing index, and sort on the minimum value of that index and make that value invisible in the resulting pivot table.
– agtoever
Jan 16 '15 at 18:11
Thanks, but the pivot table is going to live in an Excel web part, so I need it to handle new items automatically.
– Blaise Alicki
Jan 13 '15 at 13:59
Thanks, but the pivot table is going to live in an Excel web part, so I need it to handle new items automatically.
– Blaise Alicki
Jan 13 '15 at 13:59
Is it possible in your scenario to use Power Query? In that case, you could add an incementing index, and sort on the minimum value of that index and make that value invisible in the resulting pivot table.
– agtoever
Jan 16 '15 at 18:11
Is it possible in your scenario to use Power Query? In that case, you could add an incementing index, and sort on the minimum value of that index and make that value invisible in the resulting pivot table.
– agtoever
Jan 16 '15 at 18:11
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%2f863905%2fkeep-original-sorting-of-labels-in-excel-pivot-table%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
