Power Query Problems

How to Transform Country and Company Data into a Cross-Tab Table

Phi Hung Vo Oct 7, 2026 870 views

Question details

The user needs to convert a dataset where each country appears only once with subsequent rows containing company values into a cross-tab table with one country per row and companies as columns.

How to Transform Country and Company Data into a Cross-Tab Table

Product: Excel
Device & OS: not provided
Scenario: Reshaping and pivoting tabular data for better analysis, reporting, and visualization.
Observed behavior: The current data is stacked in rows with missing country labels, requiring filling down of the labels and pivoting the company data into separate columns.

Before you start

Ensure your dataset is organized in a continuous range without completely empty rows, and verify that your column headers are clearly defined.

Solution 1Recommended

Transform Data Using Power Query

Power Query is the most robust and dynamic way to clean your data, allowing you to fill down missing labels and pivot columns effortlessly.

This method is highly recommended if you expect your source data to be updated frequently. Once the query is built, you can simply refresh it to apply the same transformations to new data.

Load data to Power Query

Select any cell inside your dataset, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Fill down missing Country labels

Click on the header of the 'Country' column to select it. Go to the Transform tab, click on 'Fill', and choose 'Down'. This populates the blank cells with the country name above them.

Pivot the Company column

Select the 'Company' column. On the Transform tab, click 'Pivot Column'. In the dialog box that appears, set the Values column to your numeric data column (e.g., 'Number').

Set the aggregation method

In the Pivot Column dialog box, expand the Advanced options and ensure the aggregation method is set appropriately (such as Sum or Don't Aggregate, depending on your data).

Load the transformed table

Go to the Home tab and click 'Close & Load' to output the transformed cross-tab table into a new worksheet.

Dynamic Refresh: If you add new rows to your original table, you only need to right-click the Power Query output table and select 'Refresh' to update the cross-tab view.

Solution 2

Use Go To Special and PivotTable for a One-Time Fix

If you only need to perform this transformation once and prefer not to use Power Query, you can manually fill blank cells and summarize the data with a PivotTable.

Efficient Data Transformation in WPS

Easily Pivot and Analyze Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including advanced Go To functions and intuitive PivotTables, allowing you to quickly transform stacked datasets into professional cross-tab tables without complex coding.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your stacked country and company data.
2. Fill blank cells: Select the Country column, press Ctrl+G, select 'Blanks', type '=', click the cell above, and press Ctrl+Enter.
3. Convert formulas to values: Copy the filled column and paste it as 'Values' to lock in the country names.
4. Insert a PivotTable: Navigate to the Insert tab on the top ribbon and click 'PivotTable'.
5. Build the cross-tab view: Drag 'Country' to the Rows field, 'Company' to the Columns field, and your numerical data to the Values field.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls) filesIntuitive PivotTable creation and drag-and-drop customizationAdvanced data cleaning tools like Go To Blanks for quick formattingFree and lightweight alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

What is a cross-tab table?

A cross-tabulation (or cross-tab) table is a two-dimensional matrix that displays the relationship between two or more variables. In this scenario, it summarizes data by displaying Countries in rows and Companies in columns, making complex stacked data easier to read.

Why are there blank cells in my exported dataset?

Many reporting systems and ERPs export data in an outline or grouped layout to make it visually readable for humans. They list a primary category like 'Country' only once per group rather than repeating it on every single row.

Can I refresh my Power Query cross-tab if the source data changes?

Yes, one of the main advantages of using Power Query is that it saves your transformation steps. If your source data is updated or expanded, simply right-click anywhere on the resulting cross-tab table and select 'Refresh' to apply the changes instantly.

Excel & Data Problems