Excel – How to create extra Excel sheets showing data sorted differently

microsoft excelmicrosoft-excel-2013

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.

Related Question