Pivot Table Issues

How to Exclude Columns from an Excel PivotTable Source

Anum Mushtaq Oct 14, 2026 870 views

Question details

Users need a way to create a PivotTable using only specific columns from a dataset while excluding unwanted intermediate columns.

How to Exclude Columns from an Excel PivotTable Source

Product: Excel
Device & OS: not provided
Scenario: Selecting the source data range for a new PivotTable when some columns in the middle of the dataset are unnecessary.
Observed behavior: Excel does not accept a comma-separated selection of nonadjacent columns (e.g., A:A, C:C) as a valid PivotTable source range.

Before you start

Identify the exact column index numbers you want to include in your PivotTable, and ensure all selected columns have unique text headers.

Solution 1Recommended

Use the CHOOSECOLS Function to Create a Dynamic Source Range

Generate a new, continuous data array containing only the columns you need by using the CHOOSECOLS function, which easily acts as a valid PivotTable source.

The most efficient way to handle non-adjacent columns is to extract them into a new continuous range. The CHOOSECOLS function creates a dynamic array that automatically updates if your original data values change.

Select a blank starting cell

Click on an empty cell in a new worksheet or an empty space in your current sheet where the new filtered data can expand without overwriting existing data.

Enter the CHOOSECOLS formula

Type =CHOOSECOLS(your_range, 1, 3, 4, 6) into the formula bar. Replace 'your_range' with your actual data range (e.g., A1:G100) and the numbers with the specific column indexes you want to keep.

Generate the new range

Press Enter. Excel will spill the requested columns into a new contiguous block of data.

Insert the PivotTable

Select this newly generated continuous data block. Navigate to the Insert tab on the top ribbon, click PivotTable, and create your report normally.

Use the CHOOSECOLS Function to Create a Dynamic Source Range

Dynamic Updates: Because the new range is driven by a formula, any changes made to the values in the original source columns will be reflected in your CHOOSECOLS range, keeping your PivotTable accurate upon refresh.

Solution 2

Manually Prepare a Separate Source Range

Copy your entire dataset to a new worksheet and manually delete the columns you wish to exclude before creating the PivotTable.

Create and Manage PivotTables Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis capabilities, fully supporting PivotTables and modern dynamic array functions like CHOOSECOLS. You can quickly filter out unwanted columns and summarize your data efficiently.

1. Open your data file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your original dataset.
2. Extract columns with CHOOSECOLS: In a blank worksheet, type =CHOOSECOLS(Sheet1!A1:H100, 1, 3, 5) to dynamically pull only the required columns into a contiguous range.
3. Select the new data range: Highlight the newly generated contiguous data range that spilled from the formula.
4. Insert the PivotTable: Go to the Insert tab on the top ribbon, click PivotTable, select your destination, and click OK.
5. Build your report: Use the PivotTable pane on the right to drag and drop your chosen fields into the Rows, Columns, or Values areas.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls) and features.Supports dynamic array functions like CHOOSECOLS to filter data sources easily.Intuitive PivotTable creation with a user-friendly drag-and-drop interface.Lightweight performance ensuring smooth operation even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I hold Ctrl to select multiple non-adjacent columns for a PivotTable?

Spreadsheet software requires a continuous, contiguous range of cells to define the structural grid for a PivotTable. Non-adjacent selections using the Ctrl key create multiple distinct data areas, which the PivotTable wizard cannot interpret as a single structured database.

Does hiding columns exclude them from the PivotTable source?

No, hiding columns only changes their visibility on the worksheet layout. The hidden data remains structurally part of the selected source range and will still appear as available fields in your PivotTable field list.

Can I hide fields directly inside the PivotTable instead of excluding them from the source?

Yes. You can simply include all columns in your source range and choose not to drag the unwanted fields into the PivotTable layout areas (Rows, Columns, Values, or Filters). However, if you want to strictly prevent certain sensitive or irrelevant columns from even appearing in the field list, you must exclude them from the source data.

Excel & Data Problems