Why every online seller needs a clean order export
You sell on your own online store, maybe on Etsy and Amazon too, and at some point you want answers that the platform dashboards do not give you: which products really make money, how much each channel earns after fees, which customers come back. The data for all of that is in your order export. The problem is that the export is almost never ready to use. This lesson is for small shop owners, freelancers who do the books for sellers and anyone who has opened a CSV export and wondered why SUM returns zero or why the same product shows up three times.
Order exports are made for machines and for record keeping, not for analysis. Order numbers often come with a prefix such as "#", which makes them text. SKUs were typed by hand over the years, sometimes in lower case, sometimes with a space before or after. Quantity and price sit in separate columns, so there is no line total to add up. None of this looks wrong on screen, yet every lookup, every SUMIF and every pivot table built on it will quietly give the wrong answer.
The fix takes three formulas and fifteen minutes, and it is the foundation for everything else in this course. We use the same fictional shop as the lab above: Fernhill Candle Co., a small candle brand that sells through its own store, Etsy and Amazon. The orders, SKUs and prices are invented for learning.
The core idea: never edit the raw data, clean it next to it
The most important habit in data cleanup is not a formula, it is a rule: keep the raw columns exactly as they came out of the shop and build clean columns beside them with formulas. That way you can always see what the export said, you can paste next month's export over the raw columns and the clean columns update by themselves, and if a formula is wrong you fix one cell instead of retyping hundreds of rows.
Three kinds of problem appear in almost every export, and each has a classic Excel answer:
- Numbers stored as text. "#1041" is text because of the "#". Remove the character with
SUBSTITUTEand convert the rest withVALUE:=VALUE(SUBSTITUTE(A2,"#",""))returns the number 1041, which sorts correctly and can be looked up. - Inconsistent text keys. " lav-08 " and "LAV-08" are the same candle, but Excel treats them as different values.
TRIMremoves spaces at the start and end (and reduces double spaces inside to one),UPPERmakes every letter a capital:=UPPER(TRIM(B2)). - Missing calculated fields. The export lists quantity and unit price, but you need the line total:
=C2*D2.
Worked example: Fernhill Candle Co.'s six orders
Here is the export from the lab with the clean columns you will produce. The raw SKUs are shown in quotes so you can see the hidden spaces.
| Order (raw) A | SKU (raw) B | Qty C | Price D | Order no. E | SKU F | Line total G |
|---|---|---|---|---|---|---|
| #1041 | " lav-08 " | 2 | $24 | 1041 | LAV-08 | $48 |
| #1042 | "CED-12" | 1 | $32 | 1042 | CED-12 | $32 |
| #1043 | "sea-16 " | 1 | $42 | 1043 | SEA-16 | $42 |
| #1044 | " Fig-08" | 3 | $24 | 1044 | FIG-08 | $72 |
| #1045 | "wax-melt" | 4 | $12 | 1045 | WAX-MELT | $48 |
| #1046 | "GIFT-SET " | 1 | $58 | 1046 | GIFT-SET | $58 |
| Total | $300 |
Order #1041 becomes the number 1041 in E2. The SKU " lav-08 " has a leading and a trailing space and is written in lower case; =UPPER(TRIM(B2)) turns it into LAV-08. " Fig-08" loses its leading space and becomes FIG-08, "sea-16 " with two trailing spaces becomes SEA-16. The line totals are quantity times price: 2 × $24 = $48 for the lavender candle, 3 × $24 = $72 for the fig candle, 4 × $12 = $48 for the wax melts.
The total in G8 is $300. You can get it two ways. =SUM(G2:G7) adds the line totals you already calculated. =SUMPRODUCT(C2:C7,D2:D7) multiplies quantity and price row by row and adds the results in one step, without needing column G at all. Both give $300. SUMPRODUCT is worth knowing because it lets you check a total independently: if SUM of the line totals and SUMPRODUCT of the raw columns ever disagree, a line-total formula somewhere was overwritten or not copied down.
With six orders you could do all this by hand. With six hundred, or with a new export every week, you cannot. That is the real point of the formulas: they turn a messy file into a dependable table in seconds, every time.
Step by step: cleaning an order export in Excel
- Import the file with Data → From Text/CSV (in older versions Data → Get Data → From File → From Text/CSV), check the preview and click Load. Importing is safer than double-clicking a CSV, because Excel shows you how each column will be read.
- Add headers for the clean columns next to the raw ones: Order no. in E1, SKU in F1, Line total in G1.
- In E2 type
=VALUE(SUBSTITUTE(A2,"#",""))and press Enter. - In F2 type
=UPPER(TRIM(B2)). - In G2 type
=C2*D2and format it as currency with Ctrl+Shift+$ or Home → Number → Currency. - Select E2:G7 and press Ctrl+D to fill all three formulas down. With a long export, double-click the fill handle (the small square at the bottom right of the selection) instead: it fills down as far as the neighbouring column has data.
- In G8 enter
=SUMPRODUCT(C2:C7,D2:D7), or select G8 and press Alt+= for AutoSum of G2:G7. - Check the result:
=ISNUMBER(E2)should return TRUE, and numbers in column E should be right-aligned by default, while text is left-aligned. A quick Data → Sort on column E should put 1041 to 1046 in order. - Optional: turn the range into a table with Ctrl+T. Formulas in a table extend automatically to new rows when you paste next week's orders below.

Beyond the lab: other things exports do to your data
The three problems in the lab are the most common, but not the only ones. Once you know the pattern of "clean in a new column with a formula", the following fixes follow naturally.
- Non-breaking spaces. Text copied from web pages can contain a special space character (code 160) that TRIM does not remove.
=TRIM(SUBSTITUTE(B2,CHAR(160)," "))converts it to a normal space first. - Line breaks and invisible characters.
CLEANremoves non-printing characters:=UPPER(TRIM(CLEAN(B2))). - Prices with a currency symbol stored as text. If a price column says "$24.00" as text,
=VALUE(SUBSTITUTE(D2,"$",""))works the same way as the order number. Be careful with decimal and thousands separators when your Excel uses a different regional setting. - Several products in one order. Many exports, for example, repeat the order number on one row per product. That is fine: the line total belongs to the row, and SUMIF on the cleaned order number gives the order total.
- Dates as text. If dates are left-aligned and do not sort,
DATEVALUEor Power Query's data type change converts them.
Control formulas: prove the cleanup worked
Cleaning data is only half the job. The other half is proving to yourself that nothing slipped through, especially when the export has hundreds of rows you will never read one by one. A few small control formulas in cells below or beside the table do that in seconds.
- Did every order number become a number?
=COUNT(E2:E7)counts numeric cells only,=COUNTA(A2:A7)counts all filled cells. Both should return 6. If COUNT is lower, some rows are still text or show an error. - Are there duplicate orders?
=ROWS(E2:E7)-ROWS(UNIQUE(E2:E7))returns 0 when every order number appears once (UNIQUE needs Microsoft 365 or Excel 2021). For exports with one row per product, duplicates are expected and this check is not needed. - How many SKUs were actually changed?
=SUMPRODUCT(--EXACT(B2:B7,F2:F7))counts the rows where raw and clean SKU are identical. In the lab only CED-12 was already clean, so the result is 1, and five SKUs were fixed. - Does the money add up?
=SUM(G2:G7)=SUMPRODUCT(C2:C7,D2:D7)returns TRUE when every line total is in place. - How many distinct products are there?
=COUNTA(UNIQUE(F2:F7))on the clean column compared with the same formula on the raw column shows how many fake "extra products" the cleanup removed.
Put these checks in a small block with a heading such as Checks and colour the results with conditional formatting, green for TRUE and red for FALSE. When next month's export arrives, you paste it in, glance at the block and know immediately whether the data is safe to use. Professional analysts work exactly like this: every model has a few cells whose only job is to say "the numbers reconcile".
Common mistakes and how to spot them
- SUBSTITUTE without VALUE.
=SUBSTITUTE(A2,"#","")shows 1041 but it is still text: left-aligned, ISNUMBER returns FALSE and lookups against numbers fail. Wrap it in VALUE. - UPPER without TRIM. " LAV-08 " in capitals still has its spaces. SUMIF on "LAV-08" will miss it. Use both.
- Overwriting the raw data. If you run Find and Replace on column A, you cannot check later what the export said, and next month's file needs the same manual work again. Clean in new columns.
- Formula not copied down. If E7 is empty or G6 still shows a number you typed, the total is wrong. Compare SUM(G2:G7) with SUMPRODUCT(C2:C7,D2:D7): they must match.
- Summing text. SUM ignores text, so a column of numbers stored as text adds up to 0. A small green triangle in the cell corner is Excel's hint that a number is stored as text.
- Total row inside the range. If you later add a SUM over the whole column, including row 8 doubles the revenue. Keep totals out of the data range or use a table with a total row.
Quick reference: functions and shortcuts used
Everything from this lesson in one table.
| Function or shortcut | What it does | Example |
|---|---|---|
SUBSTITUTE | Replaces every occurrence of a text | =SUBSTITUTE(A2,"#","") → "1041" |
VALUE | Turns text that looks like a number into a number | =VALUE("1041") → 1041 |
TRIM | Removes leading, trailing and double spaces | =TRIM(" lav-08 ") → "lav-08" |
UPPER | Converts text to capital letters | =UPPER("lav-08") → "LAV-08" |
CLEAN | Removes non-printing characters such as line breaks | =TRIM(CLEAN(B2)) |
SUMPRODUCT | Multiplies two ranges row by row and adds the results | =SUMPRODUCT(C2:C7,D2:D7) → 300 |
ISNUMBER | Checks whether a cell holds a real number | =ISNUMBER(E2) → TRUE |
| Ctrl+D | Fill the formula from the top cell down | Select E2:E7, Ctrl+D |
| Ctrl+H | Find and Replace (for one-off fixes on a copy) | Replace "#" with nothing |
| Alt+= | AutoSum the cells above | In G8 |
| Ctrl+Shift+V or Paste Special → Values | Freeze formulas as values | After the cleanup, if needed |
Real-world uses
- Monthly bookkeeping. Clean the order export before matching it with payouts from your payment provider, so the order numbers in both files are real numbers and match.
- Sales per product across channels. Once every SKU has one spelling, SUMIFS can add sales per product across your own store, Etsy and Amazon exports.
- Stock counts. Inventory sheets look up products by SKU. A single trailing space makes a product appear as "not found" and distorts your reorder decisions.
- Preparing data for a pivot table. Pivot tables group by exact value, so "lav-08" and "LAV-08" would appear as two products. Clean first, pivot second.
- Sharing data with an accountant. A tidy sheet with raw and clean columns shows exactly what was changed and how.
What you will build in the rest of this course
Lesson 1 is free. The other nineteen lessons are part of the full course, a one-time purchase of $9.90. Each is a hands-on lab with every cell checked as you go, built around the same kind of small online shop, so the skills stack up into a complete seller dashboard.
Profit per Order after Payment Fees and Shipping. Revenue is not what you keep. You subtract payment fees and shipping from each order and see what is left as profit per order.
Product Margin Table: Which SKUs Earn Enough?. You build a margin table per SKU and find out which products earn enough to be worth keeping, and which ones only look busy.
Pricing with a Target Margin (Not a Markup). You learn why a 40% markup is not a 40% margin and set prices from a target margin with the formula that gets it right.
Ad Spend: ROAS, Cost per Order and Break-even ROAS. You calculate ROAS and cost per order for your ads and work out the break-even ROAS below which an ad loses money.
Discount Codes: What 15% Off Really Costs. You find out what a 15% discount code really costs once you account for your margin, and how many extra orders it needs to pay for itself.
Returns and Breakage: Return Rate and Cost per SKU. You measure return rates and breakage per SKU and turn them into a cost per product that belongs in your margin.
Inventory: Days of Stock and When to Reorder. You calculate days of stock from your sales rate and set a reorder point so you stop running out of bestsellers.
Bestseller Ranking with SORT and LARGE. You rank your bestsellers automatically with SORT and LARGE, so the list updates when new orders arrive.
Monthly Sales by Channel with SUMIFS (Chart). You add up monthly sales per channel with SUMIFS and present them in a chart that shows where your shop is growing.
Customer Repeat Rate and Revenue from Returning Buyers. You measure how many customers buy again and how much revenue comes from returning buyers.
Half-Year Dashboard: Profit, Margin, Chart and Colour Scale. Finally you bring everything together in a seller dashboard with profit, margin, a chart and a colour scale.
Summary
An order export is raw material, not a report. Keep the raw columns untouched and add clean ones next to them: =VALUE(SUBSTITUTE(A2,"#","")) for order numbers, =UPPER(TRIM(B2)) for SKUs and =C2*D2 for line totals. Check the total two ways with SUM and SUMPRODUCT. With those few formulas every later analysis, from profit per order to a full dashboard, rests on data you can trust.
Scroll up and try the lab now: clean Fernhill Candle Co.'s export and let the Coach check every cell. It takes about fifteen minutes, and you can use the same formulas on your own shop's export today.
Frequently asked questions
How do I remove the # from order numbers in Excel?
Use =VALUE(SUBSTITUTE(A2,"#","")). SUBSTITUTE removes the character and VALUE turns the remaining text into a real number.
Why does my SUM return 0 on an imported column?
The numbers are stored as text, which SUM ignores. Convert them with VALUE or with Data, Text to Columns, or change the data type in Power Query.
How do I clean SKUs with extra spaces and mixed case?
Use =UPPER(TRIM(B2)). TRIM removes extra spaces and UPPER makes all letters capitals, so different spellings of the same SKU match.
Why does TRIM not remove some spaces?
Text copied from the web can contain non-breaking spaces (character 160). Replace them first with =TRIM(SUBSTITUTE(B2,CHAR(160)," ")).
What is the difference between SUM and SUMPRODUCT for order totals?
SUM adds line totals you already calculated. SUMPRODUCT multiplies quantity and price row by row and adds the results in one formula, which is a useful cross-check.
Should I clean an order export with formulas or Power Query?
Formulas are quick and transparent for small files. Power Query is better for a recurring export, because you record the steps once and refresh them each time.
Keep going: Excel for Online Sellers: Profit per Order, Fees, Ads & Stock
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Profit per Order after Payment Fees and Shipping
- Product Margin Table: Which SKUs Earn Enough?
- Pricing with a Target Margin (Not a Markup)
- Ad Spend: ROAS, Cost per Order and Break-even ROAS
- Discount Codes: What 15% Off Really Costs
- Returns and Breakage: Return Rate and Cost per SKU
- Inventory: Days of Stock and When to Reorder
- Bestseller Ranking with SORT and LARGE
- Monthly Sales by Channel with SUMIFS (Chart)
- Customer Repeat Rate and Revenue from Returning Buyers
- Half-Year Dashboard: Profit, Margin, Chart and Colour Scale
- SKU Codes Decoded: Scent and Size with LEFT, FIND and MID – and New SKUs with TEXT
- Joining Two Exports: Unit Costs into Order Lines with XLOOKUP and a «missing» Safety Net
- Customer List Cleanup: Clean Emails, Duplicate Flags, Missing Countries & the Real Customer Count
- Scent × Size Matrix: One SUMIFS with Mixed References and a Colour-Scale Heat Map
- Order Values in Bands: COUNTIFS from–to, Median vs AOV and a Column Chart
- Product Reviews by the Numbers: Average Stars, Low-Star Share and Review Rate per SKU
- Growth over Time: Month-over-Month vs Year-over-Year and a 3-Month Average (Line Chart)
- Final Project: Year-End Seller Report – Channels, Profit, Margin, Growth and a Stacked Chart