Pivot Table Issues

How to Show Vendor Numbers and Names in Separate Excel PivotTable Columns

Jason MillerJason Miller Oct 29, 2026 869 views

Question details

The user wants to format a PivotTable so that two related row fields (such as vendor numbers and vendor names) are displayed in separate adjacent columns rather than nested within the same column.

How to Show Vendor Numbers and Names in Separate Excel PivotTable Columns
Product
Excel
Device & OS
not provided
Scenario
Organizing and formatting vendor data in a PivotTable for better readability or data export.
Observed behavior
By default, PivotTables group multiple row fields hierarchically into a single column. The goal state is to present each row field in its own distinct column on a single row per vendor.
Before you start

Ensure your PivotTable is already created and both the 'Vendor Number' and 'Vendor Name' fields have been dragged into the 'Rows' area of the PivotTable Field List.

Solution 1Recommended

Change the PivotTable Layout to Tabular Form

The Tabular Form layout forces each row field into its own separate column, making it the ideal setting for displaying numbers and names side-by-side.

By default, Excel PivotTables use the Compact Form layout, which nests all row fields into one column to save space. Switching to Tabular Form separates these fields, resembling a traditional spreadsheet table layout.

1
Select the PivotTable

Click anywhere inside your existing PivotTable to reveal the PivotTable Tools on the ribbon.

2
Navigate to the Design Tab

Look at the top ribbon and click on the 'Design' tab located under PivotTable Tools.

3
Change Report Layout

Click the 'Report Layout' drop-down menu and select 'Show in Tabular Form'.

4
Remove Subtotals (Optional)

To ensure there is only one clean row per vendor without extra aggregate rows, click 'Subtotals' in the Design tab and select 'Do Not Show Subtotals'.

Change the PivotTable Layout to Tabular Form
Layout Updated: Your vendor numbers and names will now appear in neat, separate columns. If the data looks incomplete, ensure you don't have blank values in your original source data.
Advanced Data Analysis

Easily Manage PivotTables with WPS Spreadsheet

WPS Spreadsheet offers powerful and intuitive PivotTable tools that are completely compatible with Microsoft Excel. You can quickly switch layouts, filter complex data, and separate vendor information effortlessly.

  1. 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your PivotTable.
  2. 2. Access PivotTable Tools: Click anywhere inside the PivotTable to activate the specific PivotTable contextual tabs on the top ribbon.
  3. 3. Apply Tabular Form: Go to the 'Design' tab, click on 'Report Layout', and choose 'Show in Tabular Form' to separate your columns.
  4. 4. Clean up subtotals: Click on 'Subtotals' in the same tab and choose 'Do Not Show Subtotals' to keep your vendor rows clean and uncluttered.
Fully compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.One-click report layout adjustments including Tabular, Outline, and Compact forms.Lightweight performance that handles large datasets without freezing.Free and intuitive interface for seamless data analysis and formatting.
microsoft office alternative - wps office

Frequently Asked Questions

Why are my vendor names appearing directly underneath the vendor numbers in the same column?

This happens because the PivotTable is using the default 'Compact Form' layout, which nests multiple row fields into a single column to save horizontal space. Switching your layout to 'Tabular Form' will split them into separate adjacent columns.

How do I remove the extra total rows that appeared after changing layouts?

When you add multiple fields to a PivotTable, it often automatically generates subtotals for each group. To remove these, go to the Design tab, click on the 'Subtotals' button, and select 'Do Not Show Subtotals'.

Can I separate vendor names and numbers if they are combined in one source column?

No, a PivotTable cannot automatically split data that is merged in a single cell of your source data. You must first split the combined column in your source worksheet (using the 'Text to Columns' feature or functions like LEFT/RIGHT), refresh the PivotTable, and then add both new fields to the Rows area.

Will changing the layout to Tabular Form affect my PivotTable calculations?

No. Switching between Compact, Outline, and Tabular layouts only changes how the row and column labels are visually presented. It does not alter your underlying source data or any calculations in the Values area.