How to connect Excel to Google Data Studio

by Dec 9, 2019

How to import data from Excel to Google Data Studio

Google Data Studio is a powerful visual reporting tool that allows you to transform a sea of raw data into engaging and interactive dashboards and charts. The user-friendly pre-built data connectors mean you don’t need any programming knowledge to pull data from multiple different sources. And combined with Google’s sharing functions, teams can easily combine, filter and present their data together in a really professional format, with great graphics.

As you’d expect from a piece of Google software, Google Data Studio is designed to integrate seamlessly with data sources such as Google Ads, Google Analytics, BigQuery. On top of that, there are over 150 third-party connectors to fetch data from sources like Facebook, Ebay, LinkedIn and Mailchimp. If you want to use spreadsheets as your data source, you need to use Google Sheets.

Got source data in Excel? No worries. Read on to find out how to create an automated system to import your data seamlessly from Excel to Google Sheets to Google Data Studio.

Step 1: Move your Excel files to Drive automatically.

The first step in this process is to get all your Excel files saved in your Google Drive. You could do that individually, one file at a time, but there are a few things to consider.

An effective dashboard or accurate report needs to be fed recent, up-to-date (or live!) data. Manually saving Excel files to Google Sheets is not efficient or reliable. Your data will quickly become out-of-date, plus it’s incredibly time-consuming to have to keep repeating the process. If you want to use source data from Excel files to feed your Google Data Studio reports, the best solution is to set up an automated system.

 

How to save Excel to Google Drive automatically?
  1. Start your own automated data flow by installing Google’s Backup and Sync software. Backup and Sync is designed as a way to safely back up all your files, but you can use it as way to sync Excel with Google Sheets.
  2. Select the Excel files you want to use as a your data source and save them in the same folder. Select this folder to be backed up to your Google Drive with Backup and Sync.

Step 2: Use Sheetgo to connect Excel to Google Sheets

Sheetgo is a no-code automation tool for spreadsheets and other office apps. When you create a Sheetgo connection, you watch your data move from one spreadsheet to another automatically. Here we’ll show you how to use Sheetgo to create a workflow, bringing your data from Excel files to Google Sheets and then on to Google Data Studio.

  1. Install the Sheetgo add-on from the G Suite Marketplace. Or, from inside a Google Sheet go to Add-ons > Get add-ons.
  2. From inside your spreadsheet, go to Add-ons > Sheetgo > Start.
  3. Sheetgo opens on the right hand side of your screen.
  4. Hover your mouse over the + Sign and select Import data.
  5. Click Select File and find the Excel file (or the folder) that you’ve synced to your Drive.
  6. Under Settings > connection name, enter a name.
  7. Under Settings > Automatic updates, tick the box to enable automatic updates and select how often your want the connection updated.
  8. Click Save Connection.

You have now created a Sheetgo connection.

In your spreadsheet you will see a new tab (sheet) has appeared. The data from your Excel file has been imported. Every time the connection is updated, the data in your destination spreadsheet is refreshed. If you have selected automatic updates this will happen automatically — there’s no need to open your spreadsheet or Sheetgo.

Step 3: Connect your Excel and Google Sheets files to Google Data Studio

Ok, so you’ve set up an automated system to pull data from your Excel file(s) to Google Sheets. Now you’re ready to connect to Google Data Studio.

  1. Open Google Data Studio.
  2. Click on Select Data Source, in the top right hand corner of the screen.
  3. Select Google Sheets.
  4. Select the spreadsheet, then the worksheet (tab).
  5. Click on the blue Connect button in the top right-hand corner
  6. You will now see an overview of all columns, fields and items. Click Add to report.
  7. A popup appears, to verify that you have selected the correct data. Check it and click Add to report.

Your connection is complete!

You have created a link from the Excel file on your hard drive to Google Data Studio, using Backup and Sync and Sheetgo.

Any changes to the data in the original Excel file(s) on your computer, or the Google Sheets file in your Drive, will be reflected in your Google Data Studio reports.

Need help turning your Excel data into a dashboard?

Looking for advice on how to convert all that data into an eye-catching and effective report or dashboard? Check out our post on how to create dashboards in Google Sheets and Google Data Studio.

Share This