Others

How to Display Selected Excel Columns on Separate Worksheets

Muhammad TalhaMuhammad Talha Oct 10, 2026 871 views

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.

How to Display Selected Excel Columns on Separate Worksheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify column numbers

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.).

2
Open the target worksheet

Navigate to the separate worksheet where you want the phase-specific data to be displayed and select the top-left cell, typically cell A1.

3
Input the formula

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.

4
Execute the function

Press Enter. The specified columns will immediately spill into the new worksheet as a dynamic array.

Use the CHOOSECOLS Function
Version Compatibility: The CHOOSECOLS function is available in Microsoft 365 and Excel for the Web. If you are using an older version, this function will return an error.

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. 1. Open your master workbook: Launch WPS Office and open your master project workbook using the WPS Spreadsheet application.
  2. 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. 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. 4. Save your progress: Adjust the formatting as needed and save the document. Your files remain perfectly compatible with standard Excel formats.
Fully compatible with Microsoft Excel (.xlsx) formats and formulasSupports dynamic data extraction to keep your worksheets syncedLightweight, fast, and completely free to useIntuitive interface familiar to Office users
QA img-9

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.