How to Remove Duplicates and Pivot Exported CSV Data in Excel
Question details
The user needs to import a frequently exported CSV file, remove duplicate records, and pivot the values into columns within a separate workbook, while keeping the original source file completely unchanged.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating the cleanup and reshaping of a frequently updated CSV data export to generate clean reports.
- Observed behavior
- The goal is to establish a repeatable query or formula that outputs a cleaned, pivoted dataset in a separate workbook without altering the raw CSV data.
Ensure you know the exact file path of your exported CSV file and verify that its column headers remain consistent across different export instances.
Use Power Query to Clean and Pivot the Data
Power Query is the most robust method for importing, cleaning, and pivoting data because it creates a repeatable, automated process that can be refreshed with a single click.
This method creates a connection to your CSV file rather than copying the data. Any transformations applied in Power Query are stored as a sequence of steps, leaving your original CSV file completely untouched.
Open a new blank workbook in Excel. Navigate to the 'Data' tab, click 'Get Data' > 'From File' > 'From Text/CSV', locate your exported CSV, and click 'Import'. When the preview window appears, select 'Transform Data' to open the Power Query Editor.
In the Power Query Editor, select the column or combination of columns that uniquely identify each record. Right-click the column header and choose 'Remove Duplicates' from the context menu.
Select the column containing the categories you want to turn into new columns (e.g., industry or category). Go to the 'Transform' tab and click 'Pivot Column'. In the dialog box, select the column that contains your numerical values to populate the new columns, then click 'OK'.
Once the data looks correct, go to the 'Home' tab and click 'Close & Load'. This will output the cleaned and pivoted data into a new worksheet in your Excel workbook.

Use Microsoft 365 Dynamic Arrays (PIVOTBY)
For users on Microsoft 365, modern dynamic array functions can cross-tabulate and format the data directly in the grid without entering Power Query.
Easily Manage CSV Data and Pivot Tables with WPS Office
If you are looking for a lightweight, cost-effective way to manage large CSV exports and build powerful Pivot Tables, WPS Office is a highly compatible, free alternative to Microsoft Excel.
- 1. Import Your CSV: Open WPS Spreadsheet, go to the 'Data' tab, and select 'Import Data' to securely load your external CSV file into a fresh workbook.
- 2. Remove Duplicates: Highlight your imported dataset, navigate to the 'Data' menu, click 'Remove Duplicates', and confirm the unique identifying columns.
- 3. Create a PivotTable: Click the 'Insert' tab and choose 'PivotTable'. Drag your category fields to Columns and your value fields to the Values area to instantly pivot the data.

Frequently Asked Questions
Will removing duplicates in Power Query alter my original CSV file?
No. Power Query only establishes a one-way connection to the original CSV file. All transformations, including removing duplicates and pivoting, are applied virtually and outputted to a new Excel worksheet, leaving your source file completely untouched.
How do I update my report when a new CSV is exported?
If you set up the process using Power Query, simply overwrite the old CSV file in your computer's directory with the newly exported one (ensure it has the exact same file name and location). Then, open your Excel workbook, go to the 'Data' tab, and click 'Refresh All'.
Why is the PIVOTBY formula returning a #NAME? error?
The PIVOTBY function is a modern dynamic array function exclusively available to users on the latest versions of Microsoft 365. If you are using an older version of Excel (like Excel 2019 or 2021), the function will not be recognized. In this case, use the Power Query method instead.
Can I automate this entire CSV cleaning process without using VBA macros?
Yes. Power Query is built specifically for macro-free automation. It records every click and data transformation step as a query. Once set up, repeating the entire cleaning and reshaping process only requires clicking the Refresh button.




