How to Create a Google Sheet from Data

Transforming a raw data file into a clean, organized Google Sheet is the starting point for nearly all analysis, from tracking marketing spend to building sales reports. This guide will walk you through a few different ways to get your data into a spreadsheet, starting with the basics and moving to more automated methods for handling different types of data.

First, Understand Your Data Format

Before you import anything, take a second to look at your data file. Most raw data you export from other applications comes in a few common formats. Understanding them will make the import process much smoother.

• CSV (Comma-Separated Values): This is the most common format. It's a plain text file where values in each row are separated by commas. If you open it in a text editor, it might look like this: Date,Campaign,Spend,Clicks.
• TSV (Tab-Separated Values): Similar to a CSV, but the values are separated by tabs instead of commas. This is often used to avoid issues when the data itself contains commas (like in a description field).
• XLSX or XLS (Excel Files): These are Microsoft Excel files. Google Sheets can open and convert them directly, preserving most of the formatting, formulas, and even multiple sheets within the workbook.

The key here is recognizing the delimiter - the character that separates your columns of data. Most of the time it’s a comma or a tab, but sometimes you’ll see semicolons or pipes (|). Knowing your delimiter will help you fix any import issues quickly.

Method 1: Plain and Simple Copy-Paste

The fastest way to get data into a Google Sheet is often to just copy and paste it. This method works best for smaller datasets that you can easily highlight and copy from another spreadsheet, a web page, or a document.

Here’s how to do it right:

1. Open your source data (another spreadsheet, a table on a website, etc.) and highlight the cells you want to move. Press Ctrl+C (or Cmd+C on Mac) to copy it.
2. Open a new or existing Google Sheet.
3. Click on the cell where you want the top-left corner of your data to appear (usually A1).
4. Press Ctrl+V (or Cmd+V on Mac) to paste.

Google Sheets is pretty smart and will usually separate the data into columns and rows correctly, especially if it's coming from a well-formatted source like another spreadsheet.

A Pro-Tip for Pasting

Sometimes you just want the data values without bringing over any funky formatting, colors, or fonts. In that case, use Paste special.

• After copying your data, right-click the destination cell in Google Sheets.
• Go to Paste special > Values only.

This will paste just the raw text and numbers, which is often cleaner and easier to work with. Pasting values only can prevent messy formatting that can break your formulas later on.

Method 2: Importing Files Directly

For larger data files like CSV exports from Shopify or your CRM, the direct import feature is your best friend. It gives you more control over how the data is handled and is much more reliable than copy-pasting for big datasets.

Step-by-Step Guide to Importing Files:

1. Open a new Google Sheet. You don’t want to accidentally overwrite an existing file.
2. Go to the menu and click File > Import.
3. The import window will appear with several options. Click on the Upload tab.
4. Drag your CSV, TSV, or Excel file into the window, or click “Select a file from your device” to browse your computer.
5. Once the file is uploaded, an "Import file" settings box will appear. This is the important part!

Here's what the options mean:

• Import location:
• Separator type:
• Convert text to numbers, dates, and formulas: Keeping this checked (it’s on by default) is usually a good idea. It helps Google Sheets recognize and properly format your dates and numbers so you can perform calculations on them.

Once you’ve selected your settings, click Import data, and your file's contents will populate the sheet.

Method 3: Using the =IMPORTDATA Function for Live Web Data

What if your data lives on the web and gets updated regularly? You can use the IMPORTDATA function to pull a publicly available CSV or TSV file directly into your sheet, and it will refresh automatically.

This is perfect for data from public government portals, specific financial data feeds, or any data source that provides a direct URL to a CSV file.

How It Works:

The syntax is incredibly simple. Just find an empty cell (again, usually A1 on a new sheet) and type:

=IMPORTDATA("URL_of_your_data_file.csv")

Replace the URL inside the quotes with the direct link to your CSV or TSV file. When you hit Enter, Google Sheets will visit that URL, fetch the data, and populate it into your spreadsheet starting from the cell where you entered the formula.

For example: To pull in a sample dataset of California property data, you could use a formula like this:

=IMPORTDATA("https://download.bls.gov/pub/time.series/la/la.area_type")

The best part? This data is dynamic. Google Sheets will automatically re-fetch the data from the source URL every hour or so, meaning your spreadsheet stays current without you having to manually re-import anything.

Cleaning Up Your Data After Import

Getting your data into the sheet is only half the battle. Now you need to make sure it’s clean and ready for analysis. Here are a few essential cleanup steps you should almost always perform.

Remove Duplicates

Exported data often contains duplicate rows. A few clicks can get rid of them.

1. Select the data range you want to check for duplicates (or click the top-left square between A and 1 to select the whole sheet).
2. Go to Data > Data cleanup > Remove duplicates.
3. A dialog box will pop up. If your data has a header row, make sure "Data has header row" is checked.
4. You can then choose to check for duplicates across all columns or just specific ones. Checking all is a good place to start.
5. Click "Remove duplicates," and Sheets will tell you how many duplicate rows it found and removed.

Trim Whitespace

Sometimes, data cells will have extra, invisible spaces at the beginning or end ("janedoe@email.com" instead of "janedoe@email.com"). These spaces, called whitespace, can break VLOOKUPs, filters, and pivot tables. Here’s how to get rid of them all at once.

1. Select the data you want to clean.
2. Go to Data > Data cleanup > Trim whitespace.
3. That’s it! Google Sheets will automatically remove any leading or trailing spaces from every selected cell.

Format Data Correctly

Make sure your numbers are numbers and your dates are dates.

• For numbers and currency: Select the column, go to Format > Number, and choose an appropriate format like Number, Currency, or Percent.
• For dates: Select the column, go to Format > Number > Date to ensure Sheets interprets the values correctly for sorting and filtering.

Final Thoughts

Turning raw files into structured Google Sheets is a fundamental data skill. Whether you use a simple copy-paste for small tasks, the powerful Import tool for files, or the IMPORTDATA function for live web-based data, there's always a straightforward way to get started. The real work begins after the import, where cleaning and structuring your data sets you up for successful analysis.

While importing CSVs and files is a massive step up from manual data entry, the ultimate goal is to eliminate that step entirely. At Graphed we created our platform to connect directly to the apps you use, like Google Analytics, Shopify, Salesforce, and Facebook Ads. Instead of downloading and importing files every week, your dashboards and reports update in real-time, completely automatically. This means you can skip the CSV shuffle and go straight from data to insights without opening a single spreadsheet.