About 80% of the time spent on any data project goes into cleaning the data before analysis ever begins — not into the analysis itself. That figure holds up consistently across surveys of data professionals, and it explains why the same Excel file that looks like a quick pivot table job can eat an entire afternoon. Power Query, which ships built into Excel every version from 2016 onward, addresses that pain directly: a typical messy spreadsheet that takes forty-five minutes to scrub by hand gets reduced to a five-minute repeatable transformation. The catch is that Power Query has a learning curve shaped like a cliff — the interface offers hundreds of buttons, and most beginners click around for an hour and end up more confused than when they started.
This tutorial ranks the seven most common data-cleaning operations in the order you should perform them. The sequence matters because each step depends on the one before it — fix structural problems first, then handling values, then reshaping the table for analysis. Follow the numbered order below rather than jumping between tasks, and a grubby export from your billing system or CRM will transform into a clean, pivot-ready table without you writing a single formula.
Step 1: Load Your Data Into Power Query, Not Directly Into a Worksheet
Most people skip this step and regret it within minutes. When you click Data → Get Data → From Table/Range, Excel sends the selected range into the Power Query editor. That’s the right move. What trips people up is the final dialog box that appears when they close the editor and load the result: you get two options, Close & Load and Close & Load To…
Select Close & Load To…, then choose “Only Create Connection” instead of loading a new worksheet. This seems counterintuitive — why go through all that work and not load the cleaned data? The reason is reusability. When you load the clean output to a worksheet and the raw source file changes, you must reopen Power Query and re-run everything manually. When you only create a connection, the transformation steps live in the workbook, and one right-click → Refresh updates the entire pipeline. The output table updates in place. This single choice saves more cumulative time than any other step in this guide.
A second loading mistake: skipping the Promote Headers button. Power Query assumes your first row is data unless you tell it otherwise. Click Transform → Use First Row as Headers as soon as your table loads. If your messy file has merged cells or totally blank top rows, fix those in the source before loading — Power Query chokes on merged headers and will guess column names that make no sense.
Step 2: Remove Duplicate Rows Based on the Right Columns
Duplicate detection gets its own step early because duplicates corrupt every subsequent operation — sums double-count, counts inflate, and pivot tables quietly produce wrong numbers that look plausible.
Go to Home → Remove Rows → Remove Duplicates. But before you click, decide which columns define a duplicate. The default behavior removes a row if every single column matches another row exactly. That’s rarely what you want. If your data has a unique ID column, duplicate detection is straightforward: select that column alone and remove rows where the ID repeats. If no ID exists, select the combination of columns that should be unique together — customer name plus order date, for example — and remove duplicates based on that pair.
The most common failure here occurs when someone selects the entire table, distinct-looking rows get dropped because one column differs between two otherwise identical records, and the resulting dataset loses legitimate rows. Always select the minimum number of columns that define uniqueness before removing duplicates.
Step 3: Split Column Combinations Into Their Component Fields
Messy exports routinely pack several fields into one column. A column named “Full Address” containing “123 Main St, Springfield, IL 62701” needs splitting into street, city, state, and ZIP before analysis. A column with “Product-Color-Size” holds three distinct attributes that pivot tables will want separated.
Select the column, go to Transform → Split Column, and choose By Delimiter. Power Query guesses the delimiter — comma, semicolon, space, or custom — and offers a split preview. Click OK and the column splits into multiple columns in one operation. The default splits at every occurrence of the delimiter; you can choose to split only the leftmost or rightmost occurrence if a field contains the delimiter itself (a street address with “St.” followed by a period, for instance, would break in the wrong place under a naive split).
A related cleanup happens here: Trim and Clean. Transform → Format → Trim removes leading and trailing spaces; Clean removes non-printing characters like line breaks and tabs that invisible-ly corrupt matching operations. Run both on every text column after any split, because the split operation often leaves stray spaces around the delimiters.
Step 4: Fix Data Types Column by Column
Power Query displays a small icon next to each column header: ABC for text, 123 for numbers, a calendar for dates, a clock for time. These icons reflect how Power Query interprets the column, and they control everything downstream — sorting, arithmetic, date grouping in pivot tables, and filtering.
Select each column and assign the correct type via Home → Data Type. This matters because Power Query tries to auto-detect types on load, and its guesses fail in three common ways.
First, columns with leading signs or currency symbols load as text even if they’re numeric. “$1,234.56” is text until you strip the “$” and “,” and set the type to Decimal Number. Use Transform → Replace Values to remove “$” and “,” before changing the type. Second, date columns stored as “2026-01-15” load as text when the system regional settings expect “MM/DD/YYYY”; change the type to Date, then set the format via the Locale dropdown if the conversion produces errors. Third, mixed-format columns — some values as numbers, some as text — throw type conversion errors. The error rows appear in the column as “Error” with a red background. Filter those out, inspect them, and fix them in the source data when possible.
Check each column’s type before proceeding to the next step. A column left as text when it should be numeric silently breaks every later sorting and aggregation.
Step 5: Replace, Standardize, and Normalize Values
With structure and types fixed, the remaining cleaning work targets the values themselves. The most common transformations follow a predictable pattern.
Replace bad values: Select a column, go to Transform → Replace Values, and swap “N/A”, “NULL”, “#REF!”, or “unknown” for a blank or a standardized token like “Unspecified”. The same tool handles inconsistent casing — replace “ny” with “New York” across a state column, for example.
Standardize categories: Columns like “Status” often contain variations — “closed”, “Closed”, “CLOSED”, “Clsd.” — that should collapse into one value. Select the column, Transform → Format → Uppercase converts everything to one case; then Replace Values collapses the remaining variants. This step reduces a status column from twelve distinct values to four, and a pivot table suddenly groups correctly.
Split multi-value cells: A single cell containing “red; blue; green” in a column that should have one value per row needs transformation. Select the column, Transform → Split Column → By Delimiter → Split into Rows instead of columns. This duplicates the other row values across the new rows, creating a proper one-value-per-record structure that pivot tables can aggregate correctly.
Step 6: Reshape the Table (Unpivot, Rename, Reorder)
Cleaning isn’t complete until the table’s shape matches what analysis needs. Two operations dominate here.
Unpivot columns: Data exported from dashboards frequently arrives wide — one column per month, one column per product, one column per region. Pivot tables and most analysis prefer long format, where one column holds category names and a neighboring column holds values. Select the columns that should become category names (usually the first column or two), right-click, and choose Unpivot Other Columns. Power Query stacks all remaining columns into two new columns — Attribute (holding the old column headers) and Value (holding the numbers). This converts a wide table with twelve monthly columns into a long table with three columns: Region, Month, Revenue. This single operation is the one most beginners never discover, and it resolves more “my pivot table won’t do what I want” complaints than any other feature.
Rename columns: Give every column a clean, consistent name. Avoid spaces and special characters — “Revenue_2026” instead of “Revenue 2026”. Consistent naming pays off downstream: pivot table field lists become manageable, and formulas that reference these columns stay readable.
Reorder columns: Drag columns into the order that matches your analysis or your destination report. This costs nothing and prevents confusion.
Step 7: Verify, Document, and Refresh
The last step is the one most people skip, and it’s the difference between a one-time fix and a reusable pipeline.
First, verify the transformation. Click Home → Close & Load To… → Only Create Connection, then load the output to a worksheet as described in Step 1. Check the row count against your source — a mismatch means a step dropped rows unintentionally. Spot-check five or six rows against the original data, particularly columns you transformed heavily. Filter for errors — an “Error” status in any column means a type conversion or split produced something unexpected.
Second, document your steps. The Power Query editor shows every transformation in the Applied Steps pane on the right. Rename each step with a short descriptive label — right-click a step, select Rename, and change “Split Column by Delimiter” to “Split address into 4 parts”. This turns a cryptic list into a readable recipe for anyone who opens the file later, including yourself six months from now.
Third, test the refresh path. Change a value in your source data, go to Data → Refresh All, and confirm the output updates. If it doesn’t, inspect the steps that failed — usually a step that references a specific column name that no longer exists in the source, or a type conversion that encounters an unexpected value. Fix the step once, and the pipeline works again.
The Five Most Common Beginner Mistakes (and the Recovery)
| Mistake | Symptom | Recovery |
|---|---|---|
| Loading directly to a worksheet | Cleaned data doesn’t update when source changes | Rebuild as a connection using Close & Load To… |
| Removing duplicates on all columns | Legitimate distinct rows disappear | Re-run with only the uniqueness-defining columns selected |
| Splitting columns without trimming | Address parts contain stray spaces | Add a Trim step after every Split step |
| Changing data types before cleaning values | Type conversion errors flood the column | Clean values first, then change types |
| Closing without verifying row counts | Silent row loss goes unnoticed until analysis | Compare source row count to output row count before trusting the data |
The pattern across all seven steps is consistent: clean structurally first, then values, then shape. Beginners who follow that order find each step straightforward; those who skip ahead — fixing values before duplicates, or changing types before splitting — create problems that surface hours later as mysterious errors in a pivot table.
What to Do Next
Open the messiest spreadsheet that’s been sitting in your Downloads folder. Run it through the seven steps in order, from loading as a connection through verifying the refresh path. Expect the first pass to take twenty to thirty minutes, mostly because you’ll be finding the right buttons rather than fighting the data. Save the file when done — the Applied Steps list means you never need to redo the cleaning again, even when next month’s export arrives with the same problems. The second time, the entire transformation runs in under a minute. That’s the measurable payoff of building the pipeline once instead of hand-scrubbing data every week.