How to Import Data into Excel from Web, CSV, and PDF

Excel is a widely used tool, leveraged by users like analysts, small‑business owners, and content creators. And once we connect Excel to live or semi‑structured data coming from outside the workbook, it becomes multiple times more powerful. There are many scenarios where you may need to import data into Excel from Web, CSV and PDF. 

Doing it manually will require lot of copy paste. Let’s explore how to tackle these huge manual tasks in this article as we explore pulling data from these sources. We will use modern Excel’s Get & Transform (Power Query) tools for this. We will discuss in simple terms, using screenshots and examples so that it is easy for you to follow along.

1. How to Import Data into Excel using Power Query

You can find Power Query in Excel 2016 + (Microsoft 365, Excel 2021, 2019). Go to Data tab, then Get External Data. For earlier versions of Excel (2010/2013), you can install it as a free add‑in.

We will follow these 4 steps –

1. 1. Connect – First we will connect your web page, file, database, or folder with the Excel’s Power Query Editor.

   2. Transform – Then, we will perform tasks like clean, filter, split, pivot, unpivot, and more in the Power Query Editor.

   3. Load – Import the transformed data to a new / existing tab in the sheet.

   4. Refresh – Now whenever raw data changes, we can do a one click update.

Let’s do it together now.

2. Importing Data into Excel from the Web

Below are some examples when we may need to download data from a website to Excel sheet for analysis –

• Importing stock or crypto prices to the Excel sheet

• Tracking weather forecasts or COVID case counts

• Tables of open‑source data (IMF, World Bank)

Step‑by‑Step Example: Pulling Foreign Exchange Rates

We need to build a table of USD‑INR, USD‑EUR, and USD‑GBP rates from https://www.xe.com/currencytables. We should be able to refresh it daily with a few clicks. Follow along –

1. Data → Get Data → From Other Sources → From Web

2. Paste URL and click OK.

3. A pop up will open where you will see every table and list detected in the website. Wait for the currency table to load.

4. Select the currency table and choose Transform Data (not Load yet).

   

5. You will now be in the Power Query Editor: Here you can perform actions like deleting columns which are not required, apply filters or rename columns to something friendly: Currency, Rate, Date.

6. Then, click Close & Load and it will load the result to a new worksheet.

   

7. Now, whenever we want to refresh the data, we can just press Data→ Refresh All. Excel grabs the latest rates and applies the same transform steps. Imagine how much time that will save you.

ProTip: Some sites apply anti‑scraping blocks which will not allow the Excel to read the data. Here, you need to use an official open API (like JSON/XML) and let Power Query’s From Web connector hit the API instead.

3. Importing Data into Excel from CSV Files

We use CSV (comma‑separated values) file because it is easy to read and write. it is supported by a wide range of software applications and programming languages. They are good for storing raw data without formatting. Good use cases can be –

• Weekly e‑commerce orders from Shopify

• Sensor logs from IoT devices

• HR exports such as Employees_2025‑06‑HR.csv

Step‑by‑Step Example: Combining Monthly Sales Exports

Every month, you get a file named Sales_YYYY‑MM.csv from your point‑of‑sale system. We have 1 CSV file for Jan, 1 for Feb and 1 for March. Jan CSV has Jan sales figure date wise, Feb CSV has Feb sales figure date wise, and so on.

Now, you want to combine these files into one. First create a folder and move these 3 CSVs to it. Then, follow along –

1. Data → Get Data → From File → From Folder (yes, folder, not single file).

2. Go to the folder having all CSVs, click OK.

3. Combine & Transform Data – Click OK.

4. In Power Query Editor, we can see as below that the data is combined. We can modify it if required and in the first column can see which row came from which file.

   

5. Then, hit Close & Load to import the combined data to Excel sheet.

6. Now whenever we get a new CSV, just drop it into the same folder, hit Refresh All, and your dashboard will update instantly.

Pro Tip: Files must be in the same format and order. If your vendor adds an extra column in the new CSV file, Power Query will still import it but may place it at the end; review Applied Steps to ensure sanity.

4. Importing Data into Excel from PDF

You will face many situations when someone gives you a PDF with tables and data and you will have to import it to an Excel. You can not directly copy and paste, so use Power Query Editor. A few such cases can be –

• Banks or credit‑card statements are undoubtedly sent as a PDF in email.

• Government reports are published as PDF

Step‑by‑Step Example: Extracting a Bank Statement

Download this Sample PDF and convert it to Excel by using below steps –

1. Data → Get Data → From File → From PDF

2. Select the downloaded PDF.

3. You will see the tables and pages selected by the Navigator.

4. Transform Data to open it and make the required changes if any.

5. Close & Load into a worksheet; just like we did in the previous examples.

Performance Tip: You may not see the option to import from PDF if you’re using the older version of Excel. Please update or contact your office’s IT team.

5. Automating Refresh & Sharing

• Refresh on open – We can enable this by right‑clicking on any query in the Queries & Connections pane → Properties → tick Refresh data when opening the file.

• Power BI upgrade – If your dataset is growing which is causing the Excel to slow down, or if multiple people need browser dashboards, we can import the same queries into Power BI Desktop. Surprisingly, steps you created in Excel are same in Power BI too.

8. Practice Questions

1. Web import – Import latest top 5 headlines from https://news.ycombinator.com using Power Query. Now apply a filter to show only those stories which have more than 100 points.

2. CSV power‑combo – Download the CSVs having three months of search history of Google Takeout. Then, combine them with From Folder and build a pivot showing top 10 queries.

3. PDF finesse – Download the PDF of the 2024 Fortune 500 list. Now try to import the ranking table, then sort companies by revenue in Excel.

Drop your queries in comments if you get stuck anywhere.

Conclusion

With this toolkit, you can certainly create structured, refresh-able tables and import data into Excel from Web, CSV, or PDF source. Power Query is the key, and as you get more comfortable with it, you’ll find that VBA and conventional formulas are rarely required for data ingestion tasks. Hence, begin importing small datasets now, set up a refresh, and allow Excel to maintain your insights current and alive.

Also checkout our other articles on –

Excel to Google Sheets: How to Switch & Sync Data

How to Use Excel Templates for Invoicing, Budgeting, and Reporting

Dynamic Array Formulas: FILTER, SORT, UNIQUE Explained (with Examples)

Microsoft Community Discussion on Importing Data into Excel