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
Best Answer
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.