📊 Lesson 6.2: Import/Export & a Taste of Power Query

Data In, Data Out — the Big Picture

Think back to our course through-line: Data → Formulas → Analysis → Visualization → Dashboard. Everything downstream depends on that very first word, Data — and most useful data doesn't originate in your workbook. Your bank exports transactions as a CSV. Your online store downloads sales as a text file. A colleague sends you a report from a different system entirely. Being able to bring that data in cleanly, and to push finished work back out in a format others can use, is an everyday professional skill.

There are two directions to master. Importing is pulling outside data into Excel so you can work with it. Exporting is saving your Excel data in a different format so another person or program can use it. Between them sits a crucial idea we'll build toward: importing well isn't just "open the file" — it's often "open the file and clean it up," and Power Query exists to make that cleanup repeatable so you never have to do it by hand twice.

Importing CSV, Text & Other Formats

The single most common data format you'll meet is the CSV — "comma-separated values." It's a plain-text file where each line is a row and commas separate the columns, like this:

Date,Category,Description,Amount
2026-01-03,Groceries,Supermarket,54.20
2026-01-05,Transport,Bus pass,30.00
2026-01-06,Groceries,Farmers market,18.75

CSV is beloved because almost every program on earth can produce and read it — it's the universal handshake between systems. It's also stripped-down: it carries only raw text and numbers, with no formulas, no formatting, no colors, no multiple sheets. That simplicity is both its strength and, as we'll see, the source of its pitfalls.

Ways to bring data in

• Just open it. The quickest route: File > Open and pick the CSV. Excel parses the commas into columns and drops the data onto a sheet. Fast, but it's a one-time snapshot with no cleanup steps remembered.
• Get & Transform (Power Query). On the Data tab, use Get Data / From Text/CSV. This opens a preview where you confirm the delimiter and data types before loading, and — crucially — it records the import as refreshable steps. This is the grown-up way, covered in detail below.
• Paste. For a quick one-off you can copy text and paste it in, then use Text to Columns (Data tab) to split a single column on commas.

Other formats Excel opens

Beyond CSV, Excel opens plain text files (.txt, often tab-delimited), other Excel formats, and — through Get Data on the desktop — a wide world of sources like databases, the web, and more. For this introductory lesson we'll focus on the everyday case: a CSV or text file you've downloaded.

The Pitfalls of CSV — Types, Dates, Encoding

Here's the honest truth about CSVs: because they carry only raw text with no type information, Excel has to guess what each value is meant to be — and sometimes it guesses wrong, silently. Knowing the three classic traps turns "why does my data look weird?" into a problem you can fix in seconds.

1. Data types — numbers that should be text

Excel eagerly interprets values. A ZIP code like 02139 gets read as the number 2139 — the leading zero vanishes. A long product code or credit-card-like number may be shown in scientific notation (1.23E+15) or have its final digits rounded off, because Excel treated it as a number when it was really an identifier. The fix is to tell Excel a column is text, not a number — which the Get & Transform preview (or Text Import) lets you do per column before the data lands.

2. Dates — the ambiguity trap

Dates are the most notorious. Is 03/04/2026 the 3rd of April or the 4th of March? It depends on the file's origin and your computer's regional settings, and Excel will pick one interpretation — sometimes the wrong one — without asking. Values it doesn't recognize as dates may stay as plain text, so they won't sort or calculate. The defenses: prefer sources that use the unambiguous ISO format (2026-04-03, year-month-day), and set the date column's type and locale explicitly on import rather than trusting the guess.

3. Encoding — the garbled-characters trap

A CSV is text, and text has an encoding — the scheme that maps bytes to characters. If a file was saved as UTF-8 (the modern standard, which handles every language and emoji) but Excel opens it assuming an older encoding, accented letters and symbols turn to gibberish — café becomes café, curly quotes become junk. The fix is to choose the correct File Origin / encoding in the import dialog; UTF-8 is the right answer the vast majority of the time today.

⚠️ Important Note: The safest habit with any downloaded CSV is to preview before you trust. The Get & Transform / Text Import path shows you the parsed data and lets you set each column's type and the file's encoding before it lands — which is exactly why it beats a blind double-click for anything that matters.

Exporting — CSV vs PDF

Getting data out is the mirror image, and the format you choose depends entirely on who or what will receive it. Use File > Save As (or Export) and pick the format.

The mental rule is simple. Exporting to CSV says "here's the raw data for a machine" — it's lossy on purpose, keeping only values so another system can ingest them. Watch out: saving to CSV keeps only the active sheet and drops all formulas, formatting, and charts, so keep your real .xlsx as the master and treat the CSV as a disposable export. Exporting to PDF says "here's a fixed snapshot for a human to read or print" — it looks exactly as designed and can't be accidentally edited, but it's frozen. When the recipient is another Excel user who needs to keep working, don't convert at all — just share the .xlsx (or, better, a OneDrive link as you learned last lesson).

A Taste of Power Query

Now the star of this lesson. Imagine you download a messy sales CSV every Monday: it has junk columns you don't need, names split awkwardly, dates as text, and a few blank rows. Cleaning it by hand takes fifteen minutes — and you do it every single week, forever. That's the problem Power Query was built to kill.

Power Query — labeled Get & Transform Data on the Data tab — lets you connect to a data source and apply cleanup steps that are recorded. Each thing you do (remove a column, split a column, filter out blanks, change a type) is saved as a step in a list. When next week's file arrives with the same shape, you don't redo anything — you click Refresh, and Power Query re-runs every recorded step on the new data automatically. Fifteen minutes becomes one click.

The kinds of transforms you'll use

Inside the Power Query Editor — a separate window that opens over a preview of your data — you shape the data with buttons, no coding required. The common moves:

• Remove columns you don't need, so only relevant data loads.
• Split a column — e.g. turn "Smith, Jane" into separate Last and First columns by the comma.
• Filter rows — drop blank rows, remove a category, keep only this year.
• Change type — mark a column as Date, Whole Number, or Text (this is where you fix the CSV type pitfalls, permanently).
• Unpivot — the powerful one: turn a wide, human-friendly table (a column per month) into a tall, analysis-friendly one (a Month column and a Value column), which is exactly the shape PivotTables love.

Every step appears in an Applied Steps list on the right. You can rename a step, delete one, or reorder them — it's an editable recipe. When you're happy, you Close & Load, and the cleaned result drops into your workbook as a proper Excel Table, ready for formulas and PivotTables.

Why it beats manual cleanup

The magic word is repeatable. Manual cleanup — deleting columns, running Text to Columns, retyping dates — works once, then vanishes; next week you start from scratch, and every manual pass risks a new mistake. Power Query's steps are saved with the workbook, so the cleanup is done once and refreshed forever. It's also self-documenting (the Applied Steps list is the documentation of exactly what you did), and it's non-destructive — your original source file is never changed, only read. For any data you'll process more than once, this is transformational.

Honest Scope & Best Practices

✅ Do's

• Preview CSVs before trusting them — use Get & Transform / Text Import to set types and encoding up front.
• Prefer ISO dates (YYYY-MM-DD) and UTF-8 whenever you control the source file.
• Keep your .xlsx as the master; treat exported CSVs as disposable copies for other systems.
• Reach for Power Query the moment cleanup repeats — if you'll do it twice, record it once.

❌ Don'ts

• Don't blindly double-click a CSV for important data — leading zeros and dates can silently break.
• Don't save your working file as CSV as its home format; you'll lose formulas, formatting, charts, and every sheet but one.
• Don't assume the web has full Power Query — flag desktop-only steps when you share a workflow with others.
• Don't hand-clean the same file every week — that's precisely the job Power Query erases.

🎯 Project: Import & Clean a CSV

You'll create a tiny CSV, import it into Excel, and perform one or two cleanup steps. If you're on Windows desktop, do it with Power Query so you feel the recorded-steps magic. If you're on the web or Mac, a manual import with type-setting is perfectly fine — the concepts are identical.

Date,Category,ZipCode,Amount
2026-01-03,Groceries,02139,54.20
2026-01-05,Transport,02139,30.00
2026-01-06,Groceries,90210,18.75
2026-01-09,Utilities,02139,88.40

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You can now move data through Excel's doors with confidence: importing CSVs and text without falling into the type/date/encoding traps, exporting to the right format for the audience, and — most importantly — you understand what Power Query is for. You've met the idea that cleanup can be recorded once and refreshed forever, which is one of the most time-saving habits in all of spreadsheeting.

❓ Common Questions

🔭 Looking Ahead

In the next lesson — Lesson 6.3: A Taste of Macros/VBA & Office Scripts (Honest Scope) — we take repeatability one step further: automation. You'll meet the macro recorder and VBA (Windows desktop), the modern cloud-based Office Scripts (web), and get an honest sense of when automating a task is genuinely worth it.

✅ Before the Next Lesson

• Successfully import your expenses.csv with at least one type fixed
• Try exporting one sheet to PDF and notice the layout options
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Import data & About Power Query (Microsoft Support)
• Import or export text (.txt or .csv) files — Microsoft Support
• Microsoft Excel — product overview