How to Display Selected Excel Columns on Separate Worksheets
Question details
The user wants to extract specific columns from a master Excel worksheet to display them on separate worksheets based on different project phases.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing a large master spreadsheet by displaying common project columns alongside phase-specific columns on individual worksheets.
- Observed behavior
- The user needs a dynamic or efficient way to view customized column combinations on separate sheets without manually copying and pasting data from the master worksheet.
Ensure your master worksheet has clear, distinct column headers, and make a note of the exact column index numbers you want to extract for each specific project phase.
Use the CHOOSECOLS Function
The most dynamic and efficient way to extract and display specific columns from a master sheet into a new sheet.
The CHOOSECOLS function is specifically designed for this scenario. It allows you to return specific columns from a range or array based on their index number. Because it operates dynamically, any updates made to the master worksheet will automatically populate into your separate phase worksheets.
Review your master worksheet and identify the numerical position (index) of the common project columns and the specific phase columns you wish to display (e.g., Column A is 1, Column B is 2, etc.).
Navigate to the separate worksheet where you want the phase-specific data to be displayed and select the top-left cell, typically cell A1.
Type the formula `=CHOOSECOLS(Master!A:Z, 1, 2, 3, 8, 9)`. Replace 'Master!A:Z' with your actual master data range, and change the numbers to match the column indices you identified earlier.
Press Enter. The specified columns will immediately spill into the new worksheet as a dynamic array.

Create Custom Views to Hide Columns
A simpler alternative that keeps all data on one sheet but allows you to toggle between different column visibility layouts.
Use Power Query to Split Data
An excellent method for older Excel versions or highly complex datasets that require automated transformation.
Manage Master Worksheets Efficiently with WPS Spreadsheet
WPS Spreadsheet provides powerful dynamic array functions, advanced filtering, and seamless data management features to help you extract and organize massive project datasets effortlessly.
- 1. Open your master workbook: Launch WPS Office and open your master project workbook using the WPS Spreadsheet application.
- 2. Create phase sheets: Click the '+' icon next to your sheet tabs at the bottom to create blank worksheets for your individual project phases.
- 3. Extract specific columns: Use array formulas or built-in reference features to pull the exact columns you need from the master sheet into the new phase sheets.
- 4. Save your progress: Adjust the formatting as needed and save the document. Your files remain perfectly compatible with standard Excel formats.

Frequently Asked Questions
Will the separate worksheets update automatically if the master sheet changes?
Yes, if you use dynamic formula references like the CHOOSECOLS function, any changes or additions made to the master worksheet will instantly be reflected on the separate worksheets.
Can I edit the data directly on the separate phase worksheets?
No, if the data is generated via a formula (like CHOOSECOLS) or Power Query, the separate worksheets act as a read-only mirror. You must edit the original data on the master worksheet to make changes.
What if I have an older version of Excel that doesn't support the CHOOSECOLS function?
For older versions, you can use Power Query to filter and load specific columns onto a new sheet. Alternatively, you can use a combination of INDEX and MATCH functions, or create a VBA macro to extract the required columns.




