How to Get Data in Excel

Getting your data into Excel is the first, most crucial step before you can analyze anything. While it may seem simple, there are several ways to do it, each with its own advantages for different situations. This guide walks you through the most common methods for importing data into Excel, from basic copy-pasting to powerful, automated connections.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Basics: Manual Entry and Copy-Pasting

Sometimes, the simplest method is the best one, especially when you're working with small, manageable amounts of data. These foundational methods are your starting point.

Manual Data Entry

Exactly what it sounds like: you type data directly into the cells. This method is straightforward for logging expenses, creating a simple contact list, or jotting down project notes.

When to use it:

• When you have a very small, non-recurring dataset.
• For creating templates or mock-ups where placeholder data is needed.
• When the data source is a physical document like a piece of paper or a receipt.

While easy, manual entry is slow, prone to typos, and simply impossible for large datasets. For anything beyond a handful of records, you’ll want a more efficient method.

Copy and Paste (With a Twist)

Copying data (Ctrl+C) and pasting it (Ctrl+V) is a daily task for most of us. You can copy data from websites, Word documents, PDFs, or other spreadsheets. However, a simple paste often brings unwanted formatting, links, and styling with it.

This is where Paste Special comes in. After copying your data, right-click on a cell in Excel and look for the "Paste Special" options. Here are the most useful ones:

• Values (V): Pastes only the raw text or numbers, stripping away all original formatting. This is the cleanest and most common option.
• Transpose (T): Swaps rows and columns. If you copy a horizontal list of headers, you can paste them vertically in a column.
• Formulas (F): Pastes the formulas from the copied cells, not the resulting values.

Pasting as values is a fantastic way to grab clean data from a web page without inheriting its crazy table layouts and colors. Still, like manual entry, this method isn’t scalable for large or frequently updated datasets.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Importing Data from Local Files: CSV and Text Files

The most common way to get structured data into Excel is by importing it from a file, most often a CSV (Comma Separated Values) or a standard text (.txt) file.

A CSV file is just a plain text file where values are separated by a delimiter, usually a comma. Nearly every business application on the planet - from Google Analytics to your bank - can export data as a CSV.

Here’s how to properly import a CSV file into Excel:

1. Navigate to the Data tab on the Ribbon.
2. In the "Get & Transform Data" group, click From Text/CSV.
3. A file explorer window will open. Locate and select the CSV or text file you want to import and click Import.
4. An import preview window will appear. Here, Excel automatically tries to make sense of your file. Key settings to check are:
5. Once you're happy with the preview, you have two main choices at the bottom:

Importing data this way is far more reliable and efficient than copy-pasting, especially for files with thousands of rows.

Using Power Query: The Modern, Automated Way to Get Data

Built directly into modern versions of Excel (2016 and later), Power Query is a game-changing data connection and transformation tool. Think of it as "Import Data" on steroids. Instead of performing a one-time import, Power Query allows you to create a refreshable link to your data source. When the source data changes, you just click "Refresh All" on the Data tab, and your Excel report updates automatically.

You can find Power Query tools under the Data > Get & Transform Data section.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Getting Data from the Web

Ever wanted to pull a table from a Wikipedia page or a financial website? Power Query makes it easy.

1. Go to the Data tab and click From Web.
2. Paste the URL of the webpage containing the data you want to import. Click OK.
3. Excel will analyze the page and present a "Navigator" window showing all the HTML tables it found.
4. Click on each table name in the list on the left to preview it on the right. Once you find the correct one, select it.
5. Click Load to import it directly into Excel, or click Transform Data to open the Power Query Editor for cleaning.

In the Power Query Editor, you can perform steps like removing unnecessary columns, filtering out specific rows, changing data types, or splitting columns – all without touching your original source. Every step you take is recorded and will be reapplied automatically the next time you refresh.

Getting Data from an Entire Folder of Files

This is where Power Query truly shines for saving time. Imagine you receive a daily sales report as a CSV file and store them all in one folder. Instead of importing and combining them manually each week, you can point Power Query to the folder.

1. On the Data tab, go to Get Data > From File > From Folder.
2. Browse to and select the folder containing your files (e.g., all your daily sales CSVs).
3. A preview of the files will appear. Click Combine & Transform Data.
4. Power Query will ask for an example file to understand the structure. Just click OK.
5. The Power Query Editor will open with all the data from all the files stacked into one single table. You can then Load it into Excel.

From now on, just drop a new file into that folder, hit "Refresh All" in Excel, and your master table will be instantly updated. No more tedious copy-pasting.

The Challenge: Getting Data from Your Business Apps

While Power Query is powerful, it doesn't solve the most common, day-to-day data headache for marketers, sales teams, and business owners: getting data out of countless SaaS platforms.

Think about your typical weekly marketing report. The process looks something like this:

1. Log into Google Analytics. Find the traffic report, set the date range, and export to CSV.
2. Log into Facebook Ads Manager. Do the same for your campaign performance data.
3. Log into Shopify. Export your sales data for the same period.
4. Log into Salesforce or HubSpot. Pull a report on new leads or deals.
5. You now have four separate CSV files on your desktop.
6. You open them all in Excel, then manually copy and paste the relevant columns into one "master" spreadsheet to finally build your charts and pivot tables.

This manual routine is incredibly time-consuming, repetitive, and ripe with opportunities for copy-paste errors. Worst of all, the minute you’ve finished the report, your data is already stale.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Best Practices for Newly Imported Data

Once your data is in Excel, take a few moments to set it up for success. This will make your analysis much smoother.

Format as a Table (Ctrl+T)

The single best thing you can do after importing data is to format it as an official Excel Table. Select any cell within your data range and press Ctrl+T. Here’s why this is so great:

• Automatic Formatting: Tables have clear header rows and alternating row colors, making them easy to read.
• Easy Sorting and Filtering: Filter buttons are added to the header row automatically.
• Auto-Expansion: Formulas and charts based on the table automatically expand to include new rows you add.
• Structured References: Your formulas can use clear names like Table1[SalesAmount] instead of confusing cell references like D2:D112, making them much easier to understand.

Use Data Validation

If you or your team will be adding data manually, use Data Validation (found on the Data tab) to prevent errors. You can create rules to:

• Create a dropdown list of acceptable entries (e.g., "Open," "Closed," "Pending").
• Restrict a column to only accept whole numbers or dates.
• Set minimum and maximum value limits.

This keeps your data consistent and clean from the start.

Final Thoughts

Getting your data into Excel is a universal first step, and mastering methods like importing CSVs and using Power Query will save you an incredible amount of time. These skills allow you to stop focusing on manual data collection and start focusing on uncovering valuable insights from your data.

For marketing and sales teams in particular, the routine of exporting CSVs from dozens of platforms is a huge bottleneck. Instead of repeating that manual Excel workflow over and over, we built a tool to automate it entirely. With Graphed , we connect directly to your data sources – like Google Analytics, Shopify, Facebook Ads, and Salesforce – and keep them in one place. You can instantly create real-time, professional dashboards just by describing what you want to see, freeing you from the drudgery of data importing so you can actually grow your business.