Why a clean import matters more than a fast one
A file that lands in Excel is only useful if the columns are typed correctly. I have watched a perfectly good 50,000-row export become useless because the Amount column arrived as text, so =SUM returned 0 and nobody noticed for a week. Spend the extra 30 seconds in the preview editor and your downstream formulas, PivotTables, and charts will behave. The Orders table from our regional sales export is a good example: the Region header must stay text, the Amount header must be decimal, and the OrderDate header must be a date. Get those three right at import time and you avoid the most common reporting errors.
Press Ctrl+E to use Flash Fill when the source crams first and last names into one column, because that is faster than a formula for a one-off cleanup. When the Orders file arrived with Amount formatted as text like $1,240.00, I selected the column, opened the Data tab, and clicked Text to Columns to strip the currency symbol before =SUM worked. Keep a backup copy of the raw CSV so you can re-import if a step goes wrong.
Import a CSV with Power Query
Go to the Data tab and click Get Data, then choose From File, then From Text/CSV. In the dialog, browse to your file. If the file is named Orders_2026.csv, select it and Power Query opens a preview window automatically.
In the preview, confirm the delimiter is Comma for a standard CSV. If you see one long column, change the delimiter to Tab or Semicolon. Set File Origin to UTF-8 so currency symbols like the $ sign and accented names render correctly.
Click Transform Data. In the Power Query editor, select the Amount column, go to Transform, then Detect Data Type, and choose Decimal Number. Set OrderDate to Date. Right-click the header and rename it to Orders so the table is easy to reference later.
Click Close & Load on the Home tab. Excel writes the result into a new worksheet as a proper table. You can now write =SUM(Orders[Amount]) and it will total every row, including rows added when you refresh.
I recommend loading the query into the Data Model only when the file is larger than 1 million rows, because the grid caps at 1,048,576 rows. For a 4,200-row Orders file, a normal table is faster and easier to filter. A common mistake is loading everything to a sheet and then building a PivotTable on top of a moving range, which breaks the next refresh.
Import a plain text or tab-delimited file
For a fixed-width file, go to Data, Get Data, From Text. On older Excel builds this opens the Text Import Wizard. Choose Delimited or Fixed Width, then set the column break lines by clicking in the preview.
In step 3 of the wizard, select each column and set its format: text for the ProductID header, date for the sale date, and a number for the Quantity column. This prevents the leading zero on codes like 0071 from being dropped.
Plain text files still appear in finance teams that export from mainframes. The Products table my team receives every quarter arrives as a pipe-delimited text file, and setting the ProductID column to text is the one step that saves us from duplicate keys later.
Get a PDF table into Excel
On the Data tab, click Get Data, then From File, then From PDF. Point Excel at the PDF and wait a few seconds while it scans the document for tables.
Power Query shows a list of detected objects: tables, pages, and raw text. Select the table named something like Page001_Table001. If the preview looks wrong, cancel and try a different object, because PDFs often embed multiple invisible tables.
In the editor, click Use First Row as Headers so the PDF labels become real Column names. Then trim whitespace with Transform, Format, Trim, because PDF extraction leaves stray spaces that break =VLOOKUP matches.
Close & Load, then check a known figure. When I imported a Q3 invoice PDF, the Subtotal column summed to $48,210.50, which matched the paper total, so I trusted the load and built a chart on top of it.
In my experience, a scanned PDF will not import as a table because it contains no text layer. You need OCR software first. This will break if you expect Power Query to read a picture of a table, it only reads embedded text.
Pull a live table from the web
On the Data tab, click Get Data, From Other Sources, From Web. Paste the page URL that holds the table you want, for example a public exchange rate page, and click OK.
The Navigator panel lists every table on the page. Hover each one to preview. Pick the table whose headers match what you need, such as Date, Rate, and Currency.
Click Transform, set the Rate column to Decimal, then Close & Load. Right-click the query in the Queries pane and choose Properties, then set Refresh every 60 minutes so the values stay current during the trading day.
Web imports are handy for reference data that changes often. I keep a small Currency table fed from a public source so my cost model always shows the right conversion without me editing cells by hand.
Clean columns inside Power Query before load
In the editor, click the table dropdown next to the header and choose Remove Empty Rows. This drops the trailing notes that many CSV exports append after the data.
When the source crams FirstName and LastName into one column, select it, go to Transform, Split Column, By Delimiter, and choose Space. You get two clean header columns you can sort on.
Cleaning inside Power Query means the cleanup runs every time you refresh. If you instead clean with helper columns on the sheet, you repeat the work on every new file. For the Sales table I receive weekly, this single change saved roughly 15 minutes per import.
Common mistakes that break the import
Power Query guesses types from the first 200 rows. If a later row holds a text note in the Amount column, the whole column becomes text and =SUM(Orders[Amount]) returns 0. This will break if you never set the type explicitly.
If you type notes in the rows below a loaded table, the next refresh pushes data into your notes and corrupts the sheet. Keep loaded tables on their own tab. A common mistake is building a dashboard on the same sheet as the raw import.
A refresh is the payoff of doing the import right. After the Orders table loads, click Refresh All on the Data tab and the new month's rows appear with no manual paste. In our Q1 file the East region grew to 34 percent of revenue after March was added, and the PivotTable updated the same second. Set the query to refresh on open under Connection Properties so anyone who opens the workbook sees current numbers without knowing Power Query exists.
Worked example: monthly Orders consolidation
Here is the real flow I run each month. I receive Orders_Jan.csv, Orders_Feb.csv, and Orders_Mar.csv, each with headers OrderID, Region, Amount, and OrderDate. I load all three with Get Data, From Folder, which stacks them into one Orders table of 12,640 rows. After setting Amount to decimal, I write =SUM(Orders[Amount]) and get $318,940.20 for the quarter. Then I build a PivotTable that groups Amount by Region, and the East region shows 34 percent of total revenue. Refreshing next quarter is two clicks because the query remembers every step.
Next steps
Pick the source you handle most often and rebuild it once through Power Query instead of copy-paste. Once your data lands as a real table, point your existing reports at it with =SUM, =FILTER, or a PivotTable so they update on refresh. If your files are messy, review our Excel Power Query guide and the data cleaning in Excel piece. Keep each imported table on its own tab, set column types explicitly, and refresh on a schedule so the workbook stays trustworthy without manual edits.
