Pivot Table Issues

How to Standardize Duplicate Company Names for Excel Pivot Tables

Huma Ashraf Ch Oct 19, 2026 868 views

Question details

The user needs a method to clean and standardize variations in company names so they group correctly within an Excel Pivot Table.

How to Standardize Duplicate Company Names Before Creating an Excel Pivot Table

Product: Excel
Device & OS: not provided
Scenario: Preparing raw data with inconsistent text entries (such as different spellings or abbreviations of company names) for accurate Pivot Table analysis.
Observed behavior: Inconsistent spellings and spacing cause the exact same company to appear as multiple separate items or categories in the Pivot Table.

Before you start

Identify all unique variations of the company names in your dataset to ensure no misspelled versions are missed when building your mapping table.

Solution 1Recommended

Standardize Names Using a Mapping Table and Lookup Formulas

Create a dedicated lookup table to automatically replace incorrect or varied spellings with a single standardized name before running your Pivot Table.

This approach uses Excel's lookup formulas to match the incorrect spellings in your original data against a definitive mapping list. It is much more efficient and less error-prone than manually editing thousands of rows.

Create a mapping table worksheet

Add a new worksheet and name it 'LookupTable'. In Column A, list every spelling variation found in your original data (e.g., 'Appl', 'Apl'). In Column B, type the correct standardized name (e.g., 'Apple').

Insert a helper column in your data

Go back to your original dataset worksheet. Right-click the column header next to your existing company names and select 'Insert' to add a blank column.

Apply the lookup formula

In the first data cell of the new column, enter the formula =XLOOKUP(A2,LookupTable!$A$1:$A$31,LookupTable!$B$1:$B$31,A2). If your version of Excel does not support XLOOKUP, use =IFERROR(VLOOKUP(A2,LookupTable!$A$1:$B$31,2,FALSE),A2) instead.

Fill the formula down

Select the cell containing the formula and double-click the small square fill handle in the bottom-right corner to apply the lookup formula to all rows in your dataset.

Update your Pivot Table

Highlight your dataset including the newly created standardized column. Go to the 'Insert' tab, select 'PivotTable', and use this new column for the Rows or Columns field to group the companies perfectly.

Standardize Names Using a Mapping Table and Lookup Formulas

Preserves Original Data: Using a lookup table preserves your original data while dynamically cleaning it. If new spelling variations are added later, you simply update the lookup table and the formula automatically handles the rest.

Efficient Data Cleaning

Clean and Standardize Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formulas like XLOOKUP and VLOOKUP, allowing you to easily map and standardize inconsistent data before summarizing it with Pivot Tables.

1. Open your data in WPS: Launch WPS Spreadsheet and open the workbook containing your inconsistent company names.
2. Build the mapping table: Create a new sheet to list the spelling variations in one column and their standard names right next to them.
3. Apply VLOOKUP or XLOOKUP: Insert a new column in your main data and use the lookup formula to pull the standardized names automatically.
4. Insert a Pivot Table: Navigate to the Insert tab, select PivotTable, and use your newly standardized column to generate an accurate report.

Fully compatible with Microsoft Excel formulas and Pivot TablesLightweight application with smooth performance for handling massive data logsFree to use with an intuitive, familiar tabbed interface

Frequently Asked Questions

Why are my Pivot Table items not grouping correctly by default?

Pivot Tables group items based on exact text matches. Even minor differences like extra trailing spaces, varied capitalizations, or slight abbreviations cause the Pivot Table to treat them as completely distinct categories.

How do I quickly find all unique spellings in my column to build a mapping table?

You can copy the entire column of raw company names, paste it into a blank worksheet, and click the 'Remove Duplicates' button under the Data tab. This leaves you with a concise list of all unique variations to build your mapping table.

Can I use Power Query to standardize names instead of formulas?

Yes, you can use the Power Query editor to replace specific values or merge your main data query with a mapping table query. This is highly effective for large datasets but requires more initial setup compared to using standard VLOOKUP formulas.

Excel & Data Problems