Why shipment data needs cleaning before anything else
If you coordinate shipments, you live in spreadsheets: carrier exports, warehouse management system downloads, booking confirmations, invoices and the log your colleagues type by hand. You are expected to answer simple questions fast. How many shipments did Nordline carry this week? What is our on-time rate per carrier? What did the lane to Ottawa cost per kilogram? The formulas for those answers are not hard. The problem is that the data underneath them is rarely clean, and dirty data gives wrong answers without any error message.
This free first lesson of the course tackles that problem directly. You clean the daily shipment log of Harbor Lane Distribution, a fictional home-goods distributor near Toronto. The raw log contains IDs with spaces before and after them, carrier names in all capitals or all lower case and cities typed in every possible way. With three functions, TRIM, UPPER and PROPER, you turn it into a log that can be counted, looked up and summarised. Then you prove the point with COUNTIF: the raw column undercounts a carrier, the clean column gets it right.
The lesson is for logistics coordinators, dispatchers, transport planners, warehouse supervisors and anyone who receives shipment data from more than one source. You need to know how to type a formula and copy it down; the rest is explained step by step. All companies, carriers, IDs and numbers in this course are fictional.
The core idea: computers compare characters, not meanings
To a person, "nordline ", "NORDLINE" and "Nordline" are obviously the same carrier. To Excel they are strings of characters, and a trailing space is a character like any other. Lookups such as XLOOKUP and conditions in COUNTIF, SUMIFS and PivotTables treat "nordline " and "Nordline" as two different values. The result is a carrier that appears twice in a pivot, a lookup that returns #N/A, or a count that is simply too low.
There are three typical problems in shipment logs, and each has a matching function:
- Extra spaces. Spaces before or after a value, or two spaces between words, come from manual typing, copy and paste from e-mails, and fixed-width exports.
TRIMremoves spaces at the start and end and reduces any run of spaces between words to a single space. - Inconsistent capitals in codes. Shipment IDs like "sh-10421" and "SH-10421" should look identical.
UPPERconverts every letter to a capital, which is the usual convention for IDs, SKUs and codes. - Inconsistent capitals in names. Carrier names and cities read best with a capital at the start of each word.
PROPERdoes exactly that: "swift haul" becomes "Swift Haul", "KINGSTON" becomes "Kingston".
The functions are combined by nesting: =UPPER(TRIM(A2)) first removes the spaces and then capitalises the result. The order does not change the outcome here, but "clean first, then format" is a good habit because later you may add more cleaning steps, such as removing line breaks or replacing special characters, inside the same formula.
=COUNTIF(B2:B7,"Nordline") counts "nordline" and "NORDLINE" as matches. Spaces, however, are never ignored. That is why in this lab the trailing space is what breaks the raw count, and the case cleanup mainly matters for reports, lookups in other tools and plain readability.Worked example: Harbor Lane Distribution's shipment log
The lab sheet has six shipments. Columns A to C hold the raw values exactly as they came from the export; columns D to F are where you build the clean versions. In the table below, quotation marks show where the hidden spaces are:
| Row | Shipment (raw) | Carrier (raw) | City (raw) | Shipment ID (D) | Carrier (E) | City (F) |
|---|---|---|---|---|---|---|
| 2 | " sh-10421" | "nordline " | "mississauga" | SH-10421 | Nordline | Mississauga |
| 3 | "SH-10422 " | "SWIFT HAUL" | " HAMILTON " | SH-10422 | Swift Haul | Hamilton |
| 4 | "sh-10423" | " bluecargo" | "london" | SH-10423 | Bluecargo | London |
| 5 | " Sh-10424" | "Nordline" | "ottawa " | SH-10424 | Nordline | Ottawa |
| 6 | "sh-10425 " | "swift haul" | "KINGSTON" | SH-10425 | Swift Haul | Kingston |
| 7 | "SH-10426" | "nordline" | " barrie" | SH-10426 | Nordline | Barrie |
Look at the carriers in column B. Three rows are Nordline: "nordline " with a trailing space in row 2, "Nordline" in row 5 and "nordline" in row 7. Two are Swift Haul: "SWIFT HAUL" in row 3 and "swift haul" with a double space in row 6. One is Bluecargo with a leading space. After cleaning, column E shows three tidy names, and every count is correct.
The formulas used in the worked example:
| Cell | Task | Formula | Result |
|---|---|---|---|
| D2 (copy to D7) | Clean shipment ID | =UPPER(TRIM(A2)) | SH-10421 |
| E2 (copy to E7) | Clean carrier name | =PROPER(TRIM(B2)) | Nordline |
| F2 (copy to F7) | Clean city name | =PROPER(TRIM(C2)) | Mississauga |
| B9 | Nordline shipments (clean column) | =COUNTIF(E2:E7,"Nordline") | 3 |
| (check) | Same count on the raw column | =COUNTIF(B2:B7,"Nordline") | 2, the trailing space in B2 is missed |
The difference between the two counts is small in a six-row lab, but it scales. If one in twenty rows of a weekly export has a stray space, a carrier report built on the raw column undercounts every carrier by roughly that share, and the error changes from week to week depending on who typed the data. Figure 1 shows the effect on all three carriers in this log.
Figure 2 shows what happens inside the nested formulas for two of the rows. The raw ID has two leading spaces, the raw carrier a double space in the middle. TRIM fixes the spacing, then UPPER or PROPER fixes the capitals.
Step by step: cleaning the log in Excel
Work through these steps in the lab above or in your own shipment export.
- Keep the raw data. Never overwrite the original columns. Add clean columns next to them (here D to F) with clear headers such as "Shipment ID", "Carrier" and "City". If something goes wrong, the source is still there.
- Clean the shipment IDs. Click D2 and type
=UPPER(TRIM(A2)). Press Enter. D2 shows SH-10421. - Clean the carriers. In E2 type
=PROPER(TRIM(B2)). E2 shows Nordline. - Clean the cities. In F2 type
=PROPER(TRIM(C2)). F2 shows Mississauga. - Copy the formulas down. Select D2:F7 and press Ctrl+D (Cmd+D on a Mac). Alternatively, double-click the fill handle at the bottom right of the selection, which fills down as far as the neighbouring data goes.
- Prove that it worked. In a spare cell type
=LEN(A2)and=LEN(D2): 10 characters raw, 8 clean. The difference is the two hidden spaces. - Count on the clean column. In B9 type
=COUNTIF(E2:E7,"Nordline"). The result is 3. For comparison,=COUNTIF(B2:B7,"Nordline")returns 2. - Freeze the result if needed. If you want to delete the raw columns, first select D2:F7, copy with Ctrl+C, then use Paste Special → Values (Ctrl+Alt+V on Windows, Ctrl+Cmd+V on a Mac). Only then delete columns A to C; otherwise the formulas turn into #REF! errors.
- Reuse it tomorrow. Better still, keep the formulas and paste tomorrow's raw export into columns A to C. Turn the range into a table with Ctrl+T and the formulas extend to new rows automatically.
TRIM seems to do nothing, the culprit is often a non-breaking space (character 160) from a web page or e-mail. TRIM only removes the normal space character. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to convert those spaces first, and wrap the whole thing in CLEAN to remove line breaks: =UPPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))).Beyond formulas: other ways to clean
Formulas are the most transparent method, and the one used in the lab, but you should know the alternatives:
- Find and Replace (Ctrl+H). Quick for one-off fixes, such as replacing "Swift Haul" with "Swift Haul". It changes the data in place and must be repeated on every new export.
- Flash Fill (Ctrl+E). Type one clean example next to the raw value and press Ctrl+E; Excel guesses the pattern. Handy, but check the results, because it does not update when the source changes.
- Power Query. Data → From Table/Range opens the Power Query editor, where Transform → Format offers Trim, Clean, UPPERCASE and Capitalize Each Word. The steps are recorded and repeat with one click on Refresh, which suits a daily export.
- Data validation at the source. The best cleanup is the one you never need. A drop-down list of carriers (Data → Data Validation → List) in the input sheet stops new variants from being typed.

Common mistakes and how to spot them
Data cleaning goes wrong in predictable ways:
- Counting on the raw column. The most common error, and the one the lab is built around. Spot it: compare
COUNTIFon raw and clean columns, or=SUM(COUNTIF(E2:E7,{"Nordline","Swift Haul","Bluecargo"}))against the number of rows. - PROPER on IDs.
=PROPER(TRIM(A2))turns "sh-10421" into "Sh-10421", which still differs from "SH-10421" in a case-sensitive tool. UseUPPERfor codes. - PROPER on names with special capitals. PROPER turns "McAllister" into "Mcallister" and an abbreviation such as "ABC Freight" into "Abc Freight". For brand names with unusual capitals, keep a small mapping table and look them up instead.
- Deleting the raw columns too early. The clean formulas refer to the raw cells; deleting them gives #REF!. Paste values first, or keep the raw columns hidden.
- Thinking TRIM removes all spaces. It keeps one space between words, which is what you want for "Swift Haul". To remove every space, for example in a postal code, use
=SUBSTITUTE(A2," ",""). - Invisible characters left over. If
LENof a clean value is still larger than the visible characters, look for non-breaking spaces or line breaks and useSUBSTITUTEandCLEAN.
Quick reference: functions and shortcuts
| Function / shortcut | What it does |
|---|---|
TRIM(text) | Removes leading and trailing spaces and reduces runs of spaces between words to one. |
UPPER(text) | Converts all letters to capitals, good for IDs and codes. |
PROPER(text) | Capitalises the first letter of each word, good for names and cities. |
LOWER(text) | Converts all letters to lower case, useful for e-mail addresses. |
COUNTIF(range, criteria) | Counts matching cells; not case-sensitive, but spaces count. |
LEN(text) | Counts characters; LEN of raw vs clean shows hidden spaces. |
SUBSTITUTE(text, old, new) | Replaces characters, for example non-breaking spaces CHAR(160) that TRIM ignores. |
CLEAN(text) | Removes non-printing control characters such as line breaks from exports. |
EXACT(a, b) | Case-sensitive comparison; TRUE only if two texts are identical. |
| Ctrl+D / Cmd+D | Fills a formula down the selected range. |
| Ctrl+Alt+V (Win) / Ctrl+Cmd+V (Mac) | Opens Paste Special, then choose Values to freeze the cleaned text. |
| Ctrl+H | Find and Replace, a manual alternative for one-off fixes. |
Real-world uses in logistics
Clean text columns are the foundation of almost every logistics report:
- Carrier performance reports. On-time rates and cost per kilogram per carrier only work if every carrier name is written one way.
- Matching invoices to shipments. A freight invoice lists "SH-10424" while the log says " Sh-10424"; a lookup on the cleaned ID finds the match.
- Lane analysis. Grouping cost and transit time by destination city needs "Ottawa", not "ottawa " and "OTTAWA" as three cities.
- Merging exports from two systems. A warehouse system and a carrier portal format names differently; cleaning both to the same convention makes them joinable.
- Preparing data for a PivotTable or Power BI. Duplicated categories in a pivot are almost always a cleaning problem upstream.
What you will build in the rest of this course
Lesson 1 is free. The other nineteen lessons are part of the paid course (a one-time $9.90) and use the same fictional distributor to build a full logistics toolkit.
Freight Cost per Shipment, per kg and per Pallet. You turn freight invoices into cost per kilogram and per pallet and learn why the true average cost per kg is a weighted average, not the average of the rates.
Carrier Rate Card: Weight Brackets with XLOOKUP. You price shipments from a rate card by finding the right weight bracket and applying the minimum charge.
Dimensional Weight vs Actual Weight with MAX. You calculate the volumetric weight of parcels, take the higher of actual and volumetric weight and see how bulky light items get priced.
On-Time Delivery Rate per Carrier with COUNTIFS. You mark deliveries as on time or late and calculate each carrier's OTD rate, with the clean carrier names from today.
Transit Days in Working Days: NETWORKDAYS and Holidays. You measure transit times in working days, skip a public holiday and compare each shipment with its lane target.
Pallets, Trailers and Fill Rate with ROUNDUP. You convert store orders into pallets and trailers and work out how full each trailer will be.
Fuel Surcharge per Kilometre on Truckload Invoices. You derive a weekly surcharge per kilometre from a fuel price table and check invoices against it.
Warehouse Pick Productivity: Lines per Hour. You measure pickers' lines per hour against a target and highlight who needs support.
Stock Movements: Running Balance, Reorder Flag and Cycle Count. You keep a stock card with a running balance, a reorder flag and a cycle-count check.
Carrier Scorecard: OTD, Cost per kg and Ranking. You combine service and cost into a quarterly carrier scorecard with a ranking and a bar chart for the review meeting.
Logistics Dashboard: OTD Trend, Cost per Shipment and Highlights. You finish with a half-year dashboard of on-time performance and cost per shipment, a trend chart and automatic highlights for months below target.
Summary
Every logistics report rests on its text columns. In this lesson you kept the raw shipment log untouched and built clean columns next to it: =UPPER(TRIM(A2)) for IDs, =PROPER(TRIM(B2)) for carriers and cities. The payoff showed up immediately: COUNTIF finds 3 Nordline shipments in the clean column against 2 in the raw one. You also know what to do when TRIM is not enough, how to freeze values safely and how to stop dirty data at the source.
Now scroll up to the lab and clean the Harbor Lane log yourself. The Coach checks the last row of each clean column and the Nordline count in B9, and tells you if you counted the raw column by mistake.
Frequently asked questions
How do I remove extra spaces in Excel?
Use =TRIM(A2). It removes spaces at the start and end of the text and reduces several spaces between words to one. Copy the formula down and paste as values if you want to replace the original.
Why does TRIM not remove some spaces?
Text copied from web pages often contains non-breaking spaces (character 160), which TRIM ignores. Replace them first with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
What is the difference between UPPER and PROPER in Excel?
UPPER converts every letter to a capital, which suits IDs and codes. PROPER capitalises the first letter of each word and lowers the rest, which suits names and cities.
Is COUNTIF case-sensitive?
No. COUNTIF counts "nordline" and "NORDLINE" as the same, but it does not ignore spaces, so "nordline " with a trailing space is not counted. Use EXACT inside SUMPRODUCT if you need a case-sensitive count.
How can I combine TRIM and UPPER in one formula?
Nest them: =UPPER(TRIM(A2)). Excel first removes the extra spaces and then converts the result to capitals.
How do I replace formulas with their cleaned values?
Select the clean cells, copy them with Ctrl+C and use Paste Special and Values (Ctrl+Alt+V on Windows). After that you can delete the raw columns without getting #REF! errors.
Keep going: Excel for Logistics Coordinators: Shipments, Freight Costs & On-Time Delivery
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.
- Freight Cost per Shipment, per kg and per Pallet
- Carrier Rate Card: Weight Brackets with XLOOKUP
- Dimensional Weight vs Actual Weight with MAX
- On-Time Delivery Rate per Carrier with COUNTIFS
- Transit Days in Working Days: NETWORKDAYS and Holidays
- Pallets, Trailers and Fill Rate with ROUNDUP
- Fuel Surcharge per Kilometre on Truckload Invoices
- Warehouse Pick Productivity: Lines per Hour
- Stock Movements: Running Balance, Reorder Flag and Cycle Count
- Carrier Scorecard: OTD, Cost per kg and Ranking
- Logistics Dashboard: OTD Trend, Cost per Shipment and Highlights
- Dock Appointments: Late Arrivals, Unload Times & Door Hours
- Freight Invoice Audit: Expected Charges, Overbilling & Disputes
- Open Shipments Report: Days Since Pickup, Overdue Flags & Counts by Status
- Freight Claims: Claims per 100 Shipments, Amounts Paid & Recovery Rate
- LTL or Full Truckload? Comparing Two Quotes per Order & the Break-Even Weight
- Shipping Volume by Weekday: Day Names, AVERAGEIFS & Dock Staffing
- Customer Delivery Profiles: XLOOKUP, Not-Found Values & Special Requirements
- Final Project: Monthly Lane Report with SUMIFS, OTD Targets, Flags and a Chart