Tabellab

Order Export Cleanup: Order Numbers, SKUs and Line Totals

Lesson 1 of 20Lab

All lessons
  1. Order Export Cleanup: Order Numbers, SKUs and Line TotalsLabFree
  2. Profit per Order after Payment Fees and ShippingLab Locked
  3. Product Margin Table: Which SKUs Earn Enough?Lab Locked
  4. Pricing with a Target Margin (Not a Markup)Lab Locked
  5. Ad Spend: ROAS, Cost per Order and Break-even ROASLab Locked
  6. Discount Codes: What 15% Off Really CostsLab Locked
  7. Returns and Breakage: Return Rate and Cost per SKULab Locked
  8. Inventory: Days of Stock and When to ReorderLab Locked
  9. Bestseller Ranking with SORT and LARGELab Locked
  10. Monthly Sales by Channel with SUMIFS (Chart)Lab Locked
  11. Customer Repeat Rate and Revenue from Returning BuyersLab Locked
  12. Half-Year Dashboard: Profit, Margin, Chart and Colour ScaleLab Locked
  13. SKU Codes Decoded: Scent and Size with LEFT, FIND and MID – and New SKUs with TEXTLab Locked
  14. Joining Two Exports: Unit Costs into Order Lines with XLOOKUP and a «missing» Safety NetLab Locked
  15. Customer List Cleanup: Clean Emails, Duplicate Flags, Missing Countries & the Real Customer CountLab Locked
  16. Scent × Size Matrix: One SUMIFS with Mixed References and a Colour-Scale Heat MapLab Locked
  17. Order Values in Bands: COUNTIFS from–to, Median vs AOV and a Column ChartLab Locked
  18. Product Reviews by the Numbers: Average Stars, Low-Star Share and Review Rate per SKULab Locked
  19. Growth over Time: Month-over-Month vs Year-over-Year and a 3-Month Average (Line Chart)Lab Locked
  20. Final Project: Year-End Seller Report – Channels, Profit, Margin, Growth and a Stacked ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Data → From Text/CSV imports the export. Power Query can do the same cleanup with Replace Values, Trim and UPPERCASE.
Loading the spreadsheet…

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 SUBSTITUTE and convert the rest with VALUE: =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. TRIM removes spaces at the start and end (and reduces double spaces inside to one), UPPER makes 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.
Why does "#1041" matter so much? Text and numbers never match each other in a lookup. If your order list has 1041 as a number and your payment report has "1041" as text, XLOOKUP and VLOOKUP return #N/A even though both look identical on screen.

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) ASKU (raw) BQty CPrice DOrder no. ESKU FLine total G
#1041" lav-08 "2$241041LAV-08$48
#1042"CED-12"1$321042CED-12$32
#1043"sea-16 "1$421043SEA-16$42
#1044" Fig-08"3$241044FIG-08$72
#1045"wax-melt"4$121045WAX-MELT$48
#1046"GIFT-SET "1$581046GIFT-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.

From raw export to clean columns: three formulasRaw exportFormulaClean result"#1041"text with a #=VALUE(SUBSTITUTE(A2,"#",""))1041a real number" lav-08 "spaces, lower case=UPPER(TRIM(B2))LAV-08one spelling2 × 24qty and price=C2*D248line totalRaw columns stay untouched, the clean columns are formulas next to them
Figure 1. The three cleanup formulas from the lab. Each raw value on the left goes through one formula and becomes a value that Excel can sort, look up and add.

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.

Line totals after cleanup: six orders, $300 in total#1044 FIG-08$72#1046 GIFT-SET$58#1041 LAV-08$48#1045 WAX-MELT$48#1043 SEA-16$42#1042 CED-12$32$0$20$40$60$80G8: =SUMPRODUCT(C2:C7,D2:D7) = $300 (the same as =SUM(G2:G7))
Figure 2. Line totals of the six cleaned orders, largest first. Only after cleanup can you sort, chart and add them reliably.

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

  1. 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.
  2. Add headers for the clean columns next to the raw ones: Order no. in E1, SKU in F1, Line total in G1.
  3. In E2 type =VALUE(SUBSTITUTE(A2,"#","")) and press Enter.
  4. In F2 type =UPPER(TRIM(B2)).
  5. In G2 type =C2*D2 and format it as currency with Ctrl+Shift+$ or Home → Number → Currency.
  6. 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.
  7. In G8 enter =SUMPRODUCT(C2:C7,D2:D7), or select G8 and press Alt+= for AutoSum of G2:G7.
  8. 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.
  9. 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.
Power Query can do the same cleanup without formulas: after Data → From Text/CSV click Transform Data, then use Transform → Replace Values to remove "#", Transform → Format → Trim and Format → UPPERCASE on the SKU column, and change the order column's data type to Whole Number. Next time, Data → Refresh All repeats every step.

Excel sheet for an online seller with cleaned orders, SKUs and line totals
The course takes a small online shop from a raw order export to a dashboard with profit, margins, ads, stock and repeat customers.

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. CLEAN removes 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, DATEVALUE or 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 shortcutWhat it doesExample
SUBSTITUTEReplaces every occurrence of a text=SUBSTITUTE(A2,"#","") → "1041"
VALUETurns text that looks like a number into a number=VALUE("1041") → 1041
TRIMRemoves leading, trailing and double spaces=TRIM(" lav-08 ") → "lav-08"
UPPERConverts text to capital letters=UPPER("lav-08") → "LAV-08"
CLEANRemoves non-printing characters such as line breaks=TRIM(CLEAN(B2))
SUMPRODUCTMultiplies two ranges row by row and adds the results=SUMPRODUCT(C2:C7,D2:D7) → 300
ISNUMBERChecks whether a cell holds a real number=ISNUMBER(E2) → TRUE
Ctrl+DFill the formula from the top cell downSelect E2:E7, Ctrl+D
Ctrl+HFind and Replace (for one-off fixes on a copy)Replace "#" with nothing
Alt+=AutoSum the cells aboveIn G8
Ctrl+Shift+V or Paste Special → ValuesFreeze formulas as valuesAfter 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.

  1. Profit per Order after Payment Fees and Shipping
  2. Product Margin Table: Which SKUs Earn Enough?
  3. Pricing with a Target Margin (Not a Markup)
  4. Ad Spend: ROAS, Cost per Order and Break-even ROAS
  5. Discount Codes: What 15% Off Really Costs
  6. Returns and Breakage: Return Rate and Cost per SKU
  7. Inventory: Days of Stock and When to Reorder
  8. Bestseller Ranking with SORT and LARGE
  9. Monthly Sales by Channel with SUMIFS (Chart)
  10. Customer Repeat Rate and Revenue from Returning Buyers
  11. Half-Year Dashboard: Profit, Margin, Chart and Colour Scale
  12. SKU Codes Decoded: Scent and Size with LEFT, FIND and MID – and New SKUs with TEXT
  13. Joining Two Exports: Unit Costs into Order Lines with XLOOKUP and a «missing» Safety Net
  14. Customer List Cleanup: Clean Emails, Duplicate Flags, Missing Countries & the Real Customer Count
  15. Scent × Size Matrix: One SUMIFS with Mixed References and a Colour-Scale Heat Map
  16. Order Values in Bands: COUNTIFS from–to, Median vs AOV and a Column Chart
  17. Product Reviews by the Numbers: Average Stars, Low-Star Share and Review Rate per SKU
  18. Growth over Time: Month-over-Month vs Year-over-Year and a 3-Month Average (Line Chart)
  19. Final Project: Year-End Seller Report – Channels, Profit, Margin, Growth and a Stacked Chart

Buy course — $9.90 See the full course