How to create extra Excel sheets showing data sorted differently

up vote
1
down vote

favorite

I have an Excel spreadsheet with around 10 columns and 100 rows...

Depending on context, I want to see the data in different orders, e.g.

Scenario A
- Sort spreadsheet by Column 1

Scenario B
- Sort spreadsheet by Column 3, 6, 2 (descending)

Scenario C
- Sort spreadsheet by Column 1, 2, 4, 3

etc...

It's driving me nuts keeping changing the sort order...

Is there a way to create additional Sheets that each show the data from the first sheet, just sorted in different orders

Then I could have sheets called e.g.

Master Data

Scenario A

Scenario B

Scenario C

edited Dec 1 '15 at 9:02

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

Is the data dynamic? In other words, are you adding, removing, or changing the master data regularly?
– Excellll
Dec 1 '15 at 20:14

Yes, the data is dynamic, changing regularly, and I was hoping that the views would update their content and sort order automatically, or at least with as few steps as possible.
– Clare Macrae
Dec 3 '15 at 8:08

It is frustrating, but Excel does not seem to support this functionality, at least not without writing a script.
– Sam Buss
Jun 24 at 16:25

add a comment |

up vote
1
down vote

favorite

I have an Excel spreadsheet with around 10 columns and 100 rows...

Depending on context, I want to see the data in different orders, e.g.

Scenario A
- Sort spreadsheet by Column 1

Scenario B
- Sort spreadsheet by Column 3, 6, 2 (descending)

Scenario C
- Sort spreadsheet by Column 1, 2, 4, 3

etc...

It's driving me nuts keeping changing the sort order...

Is there a way to create additional Sheets that each show the data from the first sheet, just sorted in different orders

Then I could have sheets called e.g.

Master Data

Scenario A

Scenario B

Scenario C

edited Dec 1 '15 at 9:02

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

Is the data dynamic? In other words, are you adding, removing, or changing the master data regularly?
– Excellll
Dec 1 '15 at 20:14

Yes, the data is dynamic, changing regularly, and I was hoping that the views would update their content and sort order automatically, or at least with as few steps as possible.
– Clare Macrae
Dec 3 '15 at 8:08

It is frustrating, but Excel does not seem to support this functionality, at least not without writing a script.
– Sam Buss
Jun 24 at 16:25

add a comment |

up vote
1
down vote

favorite

I have an Excel spreadsheet with around 10 columns and 100 rows...

Depending on context, I want to see the data in different orders, e.g.

Scenario A
- Sort spreadsheet by Column 1

Scenario B
- Sort spreadsheet by Column 3, 6, 2 (descending)

Scenario C
- Sort spreadsheet by Column 1, 2, 4, 3

etc...

It's driving me nuts keeping changing the sort order...

Is there a way to create additional Sheets that each show the data from the first sheet, just sorted in different orders

Then I could have sheets called e.g.

Master Data

Scenario A

Scenario B

Scenario C

edited Dec 1 '15 at 9:02

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

I have an Excel spreadsheet with around 10 columns and 100 rows...

Depending on context, I want to see the data in different orders, e.g.

Scenario A
- Sort spreadsheet by Column 1

Scenario B
- Sort spreadsheet by Column 3, 6, 2 (descending)

Scenario C
- Sort spreadsheet by Column 1, 2, 4, 3

etc...

It's driving me nuts keeping changing the sort order...

Is there a way to create additional Sheets that each show the data from the first sheet, just sorted in different orders

Then I could have sheets called e.g.

Master Data

Scenario A

Scenario B

Scenario C

microsoft-excel microsoft-excel-2013

edited Dec 1 '15 at 9:02

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

edited Dec 1 '15 at 9:02

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

edited Dec 1 '15 at 9:02

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

asked Dec 1 '15 at 8:41

Clare Macrae

1,34041633

Is the data dynamic? In other words, are you adding, removing, or changing the master data regularly?
– Excellll
Dec 1 '15 at 20:14

Yes, the data is dynamic, changing regularly, and I was hoping that the views would update their content and sort order automatically, or at least with as few steps as possible.
– Clare Macrae
Dec 3 '15 at 8:08

It is frustrating, but Excel does not seem to support this functionality, at least not without writing a script.
– Sam Buss
Jun 24 at 16:25

add a comment |

Is the data dynamic? In other words, are you adding, removing, or changing the master data regularly?
– Excellll
Dec 1 '15 at 20:14

Yes, the data is dynamic, changing regularly, and I was hoping that the views would update their content and sort order automatically, or at least with as few steps as possible.
– Clare Macrae
Dec 3 '15 at 8:08

It is frustrating, but Excel does not seem to support this functionality, at least not without writing a script.
– Sam Buss
Jun 24 at 16:25

Is the data dynamic? In other words, are you adding, removing, or changing the master data regularly?
– Excellll
Dec 1 '15 at 20:14

Yes, the data is dynamic, changing regularly, and I was hoping that the views would update their content and sort order automatically, or at least with as few steps as possible.
– Clare Macrae
Dec 3 '15 at 8:08

It is frustrating, but Excel does not seem to support this functionality, at least not without writing a script.
– Sam Buss
Jun 24 at 16:25

add a comment |

2 Answers
2

active

oldest

votes

up vote
0
down vote

Yes you can! It's hacky but easy to do:

1. Open a ScenarioA sheet. Do a "formula copy" of the whole sheet. For example:
Put "=MasterData!A1" in cell A1 of ScenarioA sheet and drag that across.

Now, you have a copy of the MasterData but that's not robust for moving around!

2. Find/Replace: =MasterData! with =MasterData!$

3. Find/Replace: =MasterData!$A with =MasterData!$A$ (do this for all columns)

Now, the references in the scenario sheet are fixed and if you sort, they will not re-evaluate to something else.

edited Dec 1 '15 at 13:50

answered Dec 1 '15 at 10:32

DraxDomax

1139

Doesn't step 2 break the process? If you search for =MasterData!A after you've already added the first $ you won't find anything.
– Kyle
Dec 1 '15 at 13:45

Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix!
– DraxDomax
Dec 1 '15 at 13:50

add a comment |

up vote
0
down vote

You can use data connections. First, format your master data as a table by clicking anywhere inside the data and pressing ctrl+T. Then on each of your three sheets go to the Data Tab >> Get External Data Group >> Existing Connections >> Tables tab and select your table.

When you add or change your data you must refresh your connections by going to the Data Tab >> Connections group >> Refresh All.

answered Dec 1 '15 at 13:53

Kyle

2,2462612

Thanks. I've done the Ctrl+T bit, and created the table. When I got to Data Tab >> Get External Data Group >> Existing Connections, there is no Tables tab. When I click on 'Show:' dropdown, and choose 'Connections in this Workbook' it says '<No connections found>'. Any ideas, please?
– Clare Macrae
Dec 1 '15 at 15:23

@ClareMacrae From the Existing Connections dialog, click on Browse for More... then navigate to where your workbook is saved and select it. Then you can select the Master Data worksheet.
– Kyle
Dec 1 '15 at 15:42

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',
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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1007535%2fhow-to-create-extra-excel-sheets-showing-data-sorted-differently%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
0
down vote

Now, the references in the scenario sheet are fixed and if you sort, they will not re-evaluate to something else.

edited Dec 1 '15 at 13:50

answered Dec 1 '15 at 10:32

DraxDomax

1139

Doesn't step 2 break the process? If you search for =MasterData!A after you've already added the first $ you won't find anything.
– Kyle
Dec 1 '15 at 13:45

Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix!
– DraxDomax
Dec 1 '15 at 13:50

add a comment |

up vote
0
down vote

Now, the references in the scenario sheet are fixed and if you sort, they will not re-evaluate to something else.

edited Dec 1 '15 at 13:50

answered Dec 1 '15 at 10:32

DraxDomax

1139

Doesn't step 2 break the process? If you search for =MasterData!A after you've already added the first $ you won't find anything.
– Kyle
Dec 1 '15 at 13:45

Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix!
– DraxDomax
Dec 1 '15 at 13:50

add a comment |

up vote
0
down vote

Now, the references in the scenario sheet are fixed and if you sort, they will not re-evaluate to something else.

edited Dec 1 '15 at 13:50

answered Dec 1 '15 at 10:32

DraxDomax

1139

Now, the references in the scenario sheet are fixed and if you sort, they will not re-evaluate to something else.

edited Dec 1 '15 at 13:50

answered Dec 1 '15 at 10:32

DraxDomax

1139

edited Dec 1 '15 at 13:50

answered Dec 1 '15 at 10:32

DraxDomax

1139

answered Dec 1 '15 at 10:32

DraxDomax

1139

answered Dec 1 '15 at 10:32

DraxDomax

1139

Doesn't step 2 break the process? If you search for =MasterData!A after you've already added the first $ you won't find anything.
– Kyle
Dec 1 '15 at 13:45

Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix!
– DraxDomax
Dec 1 '15 at 13:50

add a comment |

Doesn't step 2 break the process? If you search for =MasterData!A after you've already added the first $ you won't find anything.
– Kyle
Dec 1 '15 at 13:45

Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix!
– DraxDomax
Dec 1 '15 at 13:50

Doesn't step 2 break the process? If you search for =MasterData!A after you've already added the first $ you won't find anything.
– Kyle
Dec 1 '15 at 13:45

Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix!
– DraxDomax
Dec 1 '15 at 13:50

add a comment |

up vote
0
down vote

When you add or change your data you must refresh your connections by going to the Data Tab >> Connections group >> Refresh All.

answered Dec 1 '15 at 13:53

Kyle

2,2462612

Thanks. I've done the Ctrl+T bit, and created the table. When I got to Data Tab >> Get External Data Group >> Existing Connections, there is no Tables tab. When I click on 'Show:' dropdown, and choose 'Connections in this Workbook' it says '<No connections found>'. Any ideas, please?
– Clare Macrae
Dec 1 '15 at 15:23

@ClareMacrae From the Existing Connections dialog, click on Browse for More... then navigate to where your workbook is saved and select it. Then you can select the Master Data worksheet.
– Kyle
Dec 1 '15 at 15:42

add a comment |

up vote
0
down vote

When you add or change your data you must refresh your connections by going to the Data Tab >> Connections group >> Refresh All.

answered Dec 1 '15 at 13:53

Kyle

2,2462612

Thanks. I've done the Ctrl+T bit, and created the table. When I got to Data Tab >> Get External Data Group >> Existing Connections, there is no Tables tab. When I click on 'Show:' dropdown, and choose 'Connections in this Workbook' it says '<No connections found>'. Any ideas, please?
– Clare Macrae
Dec 1 '15 at 15:23

@ClareMacrae From the Existing Connections dialog, click on Browse for More... then navigate to where your workbook is saved and select it. Then you can select the Master Data worksheet.
– Kyle
Dec 1 '15 at 15:42

add a comment |

up vote
0
down vote

When you add or change your data you must refresh your connections by going to the Data Tab >> Connections group >> Refresh All.

answered Dec 1 '15 at 13:53

Kyle

2,2462612

When you add or change your data you must refresh your connections by going to the Data Tab >> Connections group >> Refresh All.

answered Dec 1 '15 at 13:53

Kyle

2,2462612

answered Dec 1 '15 at 13:53

Kyle

2,2462612

answered Dec 1 '15 at 13:53

Kyle

2,2462612

answered Dec 1 '15 at 13:53

Kyle

2,2462612

Thanks. I've done the Ctrl+T bit, and created the table. When I got to Data Tab >> Get External Data Group >> Existing Connections, there is no Tables tab. When I click on 'Show:' dropdown, and choose 'Connections in this Workbook' it says '<No connections found>'. Any ideas, please?
– Clare Macrae
Dec 1 '15 at 15:23

@ClareMacrae From the Existing Connections dialog, click on Browse for More... then navigate to where your workbook is saved and select it. Then you can select the Master Data worksheet.
– Kyle
Dec 1 '15 at 15:42

add a comment |

Thanks. I've done the Ctrl+T bit, and created the table. When I got to Data Tab >> Get External Data Group >> Existing Connections, there is no Tables tab. When I click on 'Show:' dropdown, and choose 'Connections in this Workbook' it says '<No connections found>'. Any ideas, please?
– Clare Macrae
Dec 1 '15 at 15:23

@ClareMacrae From the Existing Connections dialog, click on Browse for More... then navigate to where your workbook is saved and select it. Then you can select the Master Data worksheet.
– Kyle
Dec 1 '15 at 15:42

Thanks. I've done the Ctrl+T bit, and created the table. When I got to Data Tab >> Get External Data Group >> Existing Connections, there is no Tables tab. When I click on 'Show:' dropdown, and choose 'Connections in this Workbook' it says '<No connections found>'. Any ideas, please?
– Clare Macrae
Dec 1 '15 at 15:23

@ClareMacrae From the Existing Connections dialog, click on Browse for More... then navigate to where your workbook is saved and select it. Then you can select the Master Data worksheet.
– Kyle
Dec 1 '15 at 15:42

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr