Calculation Issues

How to Create Multiple Sorted Views from One Excel Data Source

Kushani Nimanthika Sep 30, 2026 868 views

Question details

The user wants to display the same master dataset across three different worksheets, sorting each copy differently, and ensuring all views update automatically when the source data changes.

How to Create Multiple Sorted Views from One Excel Data Source

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating synchronized, independently sorted reports or views from a master dataset across multiple worksheets.
Observed behavior: Standard copy-pasting does not sync updates, and older software versions lack modern dynamic array features to automatically create sorted copies without manual workarounds.

Before you start

Ensure your master dataset is formatted as a continuous table with clear column headers and no blank rows, which makes referencing and sorting the data in secondary views much easier.

Solution 1Recommended

Use PivotTables to Create Independent Sorted Views

PivotTables are the most reliable method in older and modern Excel versions to create multiple views of the same data that can be sorted independently and refreshed easily.

By inserting multiple PivotTables based on the same source range, you can present the data in various sort orders on different worksheets. When the source data is modified, a simple refresh updates all views.

Select the source data

Highlight your master dataset, including the column headers.

Insert a PivotTable

Go to the Insert tab on the ribbon and click PivotTable. Choose to place the PivotTable in a New Worksheet and click OK.

Configure the fields

In the PivotTable Field List, check the boxes for the columns you want to display, ensuring they appear in the Rows or Values areas as needed.

Apply sorting

Click the drop-down arrow next to the Row Labels in the PivotTable, select 'Sort A to Z' or 'More Sort Options', and define the specific sorting rule for this worksheet.

Repeat and refresh

Repeat these steps to create additional PivotTables on other sheets with different sort orders. To sync changes from the master sheet, go to the Data tab and click Refresh All.

Use PivotTables to Create Independent Sorted Views

Pro Tip: Format your source data as an Official Excel Table (Ctrl+T) before creating PivotTables. This ensures that new rows added later are automatically included in the PivotTable data source.

Solution 2

Create a Workbook-Scoped Named Range

Use named ranges to link data across sheets so that cell values update instantly when the master data changes.

Create Dynamic Sorted Views Easily with WPS Spreadsheet

WPS Spreadsheet offers seamless support for advanced data management, including PivotTables, named ranges, and modern dynamic array formulas, allowing you to create multiple synchronized views of your master dataset effortlessly.

1. Open your dataset: Launch WPS Spreadsheet and open the master file containing your source data.
2. Insert a PivotTable: Highlight the data, navigate to the Insert tab, and select PivotTable to create a new view on a separate worksheet.
3. Sort the view: Configure your rows and apply your desired custom sorting rule directly from the PivotTable headers.
4. Duplicate and refresh: Create as many PivotTables as needed for different views. Use the 'Refresh All' button in the Data tab whenever your source data updates.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) ensuring seamless data migration.Powerful PivotTable functionality for creating multiple independently sorted reports in clicks.Support for modern functions allowing for dynamic data synchronization.Free, lightweight, and features an intuitive tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why don't standard formulas automatically sort my data in Excel 2007?

Older versions of Excel, like Excel 2007, do not support modern dynamic array functions such as SORT or FILTER. In these versions, formulas can pull data but cannot dynamically reorder it, requiring the use of PivotTables or manual sorting of referenced ranges.

Will my PivotTable update automatically the moment I type in the main spreadsheet?

No, PivotTables do not update in real-time as you type. To sync the data, you must right-click anywhere inside the PivotTable and select 'Refresh', or go to the Data tab on the ribbon and click 'Refresh All'.

Can I use the SORT function to create different views?

Yes, if you are using newer spreadsheet software like modern Microsoft 365 or updated WPS Office, you can use the =SORT() dynamic array formula. Placing this formula in a new sheet will instantly create a dynamically updating and automatically sorted view of your source data without needing a PivotTable.

Excel & Data Problems