Most Excel trainers tell you to drag-and-drop an XLSX file into a workbook and call it 'importing'. That’s not importing. That’s copying. And if you do that with 12,000 rows of supplier invoices from Shanghai Logistics Co., you’ll break formulas, lose date formatting, and corrupt merged cells before lunch.
The Setup
You’ve just received Q3_Supplier_Deliveries_2024.xlsx from procurement. It’s 9,842 rows. No headers in row 1. Column A is raw timestamps like 2024-07-12 08:32:15, but Excel reads them as text. Column B contains vendor IDs like VL-8821-CHN, but some are missing dashes. Column C has delivery weights in kg—but some entries say "N/A", others "--", and one says "~12.4". Column D is cost in USD—but formatted as text with commas and dollar signs: $4,281.60.
Here’s a realistic slice (rows 1–9 of the source file):
| A | B | C | D |
|---|---|---|---|
| 2024-07-12 08:32:15 | VL-8821-CHN | 12.4 | $4,281.60 |
| 2024-07-12 09:14:02 | VL8822CHN | -- | $1,029.50 |
| 2024-07-12 10:05:33 | VL-8823-CHN | N/A | $742.25 |
| 2024-07-12 11:22:47 | VL-8824-CHN | ~15.1 | $3,199.80 |
| 2024-07-12 12:01:18 | VL8825CHN | 9.7 | $2,011.40 |
| 2024-07-12 13:44:55 | VL-8826-CHN | 11.3 | $2,755.00 |
| 2024-07-12 14:30:22 | VL8827CHN | N/A | $1,893.65 |
| 2024-07-12 15:19:08 | VL-8828-CHN | 13.9 | $3,502.75 |
| 2024-07-12 16:07:31 | VL8829CHN | -- | $987.30 |
The Challenge
You need this data in your master workbook SupplyChain_Master.xlsm on Sheet2, starting at cell A1. But you can’t just paste it.
- Date/time in column A must become proper Excel serial numbers — not text — so you can filter by week or calculate lead time.
- Vendor IDs must be standardized: all
VL-XXXX-CHN, no exceptions. - Weight values must be numeric.
--,N/A, and~15.1all need conversion or flagging. - Costs must be numbers, not text — or
SUM()will return zero.
And here’s what most people miss: Importing Excel into Excel isn’t about getting data in — it’s about keeping control over how Excel interprets each column. Drag-and-drop ignores data types. Paste Special ignores structure. You need the Data tab’s Power Query engine — even for a single file.
Walking Through It
How to import Excel into Excel (the right way)
Open SupplyChain_Master.xlsm. Go to the Data tab. Click Get Data → From File → From Workbook.
Find and select Q3_Supplier_Deliveries_2024.xlsx. Click Import.
You’ll land in Power Query Editor. The preview shows columns A–D. Notice column A is labeled Text — correct, because raw timestamps are text until parsed.
Step 1: Right-click column A → Change Type → Using Locale… → Select Date/Time, locale English (United States). Click OK.
Step 2: Right-click column B → Transform → Format → Replace Values. Find: VL(\d{4})CHN (enable Use regular expressions). Replace with: VL-$1-CHN. This fixes VL8822CHN → VL-8822-CHN. Do same for VL(\d{4})CHN without dash — use regex to catch both patterns.
Step 3: Select column C. Go to Transform → Replace Values. Replace -- and N/A with null. Then click Transform → Data Type → Decimal Number. For ~15.1, Power Query auto-trims the tilde — no extra step needed.
Step 4: Select column D. Click Transform → Format → Remove Characters. Choose $ and ,. Then Change Type → Decimal Number.
Click Close & Load To…. Choose Table, place in Sheet2!A1. Done.
Keyboard shortcut: Alt+A, T, W — opens “From Workbook” directly. Save 3 seconds per import. Do it every time.
How to import Excel into R (when you need stats or modeling)
You don’t need RStudio open to prep for this. First, save your cleaned Excel file as Q3_Cleaned.csv from Excel (File → Save As → CSV UTF-8). Why? Because readxl::read_excel() works — but it’s slower, less reproducible, and fails silently on encoding issues with Chinese vendor names like “Shenzhen Tongyi Ltd.”
In R, run:
library(readr)
deliveries <- read_csv("Q3_Cleaned.csv",
col_types = cols(
Delivery_Time = col_datetime(format = "%Y-%m-%d %H:%M:%S"),
Vendor_ID = col_character(),
Weight_kg = col_double(),
Cost_USD = col_double()
))
Notice: col_types forces data types. Without it, R guesses Weight_kg as character if first 10 rows contain --. That breaks lm() and ggplot(). Always specify.
Pro tip: If you *must* use readxl, never do read_excel("file.xlsx"). Always add skip = 0, n_max = 10000 — prevents hanging on corrupted hidden sheets.
The Result
After Power Query finishes, Sheet2 starts at A1 with clean, typed data:
| A | B | C | D |
|---|---|---|---|
| 2024-07-12 08:32:15 | VL-8821-CHN | 12.4 | 4281.6 |
| 2024-07-12 09:14:02 | VL-8822-CHN | null | 1029.5 |
| 2024-07-12 10:05:33 | VL-8823-CHN | null | 742.25 |
| 2024-07-12 11:22:47 | VL-8824-CHN | 15.1 | 3199.8 |
| 2024-07-12 12:01:18 | VL-8825-CHN | 9.7 | 2011.4 |
| 2024-07-12 13:44:55 | VL-8826-CHN | 11.3 | 2755 |
| 2024-07-12 14:30:22 | VL-8827-CHN | null | 1893.65 |
| 2024-07-12 15:19:08 | VL-8828-CHN | 13.9 | 3502.75 |
| 2024-07-12 16:07:31 | VL-8829-CHN | null | 987.3 |
Now =SUM(D1:D9) returns 20,403.25 — not zero. =COUNTA(C1:C9) returns 5, not 9. And =FILTER(A1:D9,C1:C9>10) works instantly.
What Could Go Wrong
Three mistakes I see weekly — with exact symptoms and fixes:
- Mistake #1: Using Paste Special → Text instead of Power Query
Result: Column A stays as text.=YEAR(A1)returns#VALUE!. Fix: Delete pasted range. Use Data → Get Data → From Workbook. Don’t shortcut the engine. - Mistake #2: Skipping column type definition in R
Result:class(deliveries$Weight_kg)returns"character".mean(deliveries$Weight_kg)returnsNAwith warning. Fix: Addcol_types = cols(Weight_kg = col_double())— always. - Mistake #3: Opening the source XLSX in Excel first, then copying
Result: Hidden filters, manual row hides, or background colors get copied — breaking Power Pivot relationships downstream. Fix: Never open the source file. Use Power Query’s native connector. It reads raw cells only.
Your next step: Open Excel right now. Press Alt+A,T,W. Point to any Excel file you’ve been avoiding. Import it. Clean one column. Close & Load. That’s it — no theory, no setup, no waiting.
| Method | Speed | Data Integrity | Reproducible? | R-Ready? |
|---|---|---|---|---|
| Drag & Drop | ★★★☆☆ | ★☆☆☆☆ | No | No |
| Paste Special → Text | ★★★☆☆ | ★★☆☆☆ | No | No |
| Power Query (From Workbook) | ★★★☆☆ | ★★★★★ | Yes | Yes (via CSV export) |
| R read_csv() + col_types | ★★★★☆ | ★★★★★ | Yes | Yes |
