Are you tired of tediously entering tons of data into Google Sheets? Do you find yourself wondering how to fill Google Spreadsheets automatically? Well, you’re in luck! In this article, we’ll explore three easy ways to save you from the tedious task of data entry using the autocompletion feature in Google Sheets. 🙌
Method 1: How to Fill Google Sheets Automatically with the Fill Handle
If you simply want to automatically fill Google Sheets with data you already have, then the fill handle is what you need. The fill handle is the small box in the bottom right corner of cells in Google Sheets:
Drag the fill handle vertically or horizontally to automatically populate your sheets based on the patterns you have already started filling on the spreadsheet.
So, how do you automatically fill sequential numbers in Google Sheets using the fill handle?
First, you need to create a pattern by manually filling in a few entries. This helps Google Sheets understand what you’re trying to do before the autocompletion feature works correctly.
Let’s explore some examples of autocompletion patterns in this section. But first, here’s how you should drag the fill handle to initiate autocompletion:
- Vertically
- Horizontally
Now, let’s look at a few examples of how the fill handle helps speed up data entry.
1. Using the Fill Handle on Google Sheets to Automatically Fill Dates
Let’s see how you can use the fill handle to add dates. Take the month of September, the ninth month, for example. The first two dates (MM/DD/YY) are 09/01/2022 and 09/02/2022:
These two entries are enough for Google Sheets to understand our pattern.
So, we can drag the fill handle:
It’s as simple as that. If you want to create a large spreadsheet with dates following an established pattern, then the fill handle can provide exactly what you need.
2. Using the Fill Handle for Days of the Week
Do you need to fill a Google Sheets spreadsheet with consecutive days of the week? The fill handle allows you to create a complete calendar using just a fill handle. Simply start by entering the first two days:
Continue by dragging the fill handle as far to the right as needed on the spreadsheet:
When you release, the final result should look like this:
As you can see, the fill handle restarts the week after Sunday, just as it should. This way, you can create a calendar. For example, let’s take the year 2022. The first Monday of January 2022 was the third day: 01/03/2022 (MM/DD/YY).
Let’s start our calendar with the entries:
And continue dragging:
Here, you can also see how to use the fill handle to automatically fill two lines at once. In this case, the days of the week and the dates. Pretty impressive!
Method 2: How to Fill Google Sheets Automatically with Smart Fill
If you have experience with Microsoft Excel, you may be familiar with Flash Fill. It’s a feature that recognizes patterns and fills in data accordingly. But is there a Flash Fill option in Google Sheets? Yes, Google has introduced Smart Fill as its own version of Flash Fill.
Smart Fill is more versatile than the fill handle as it detects patterns as you work on your sheets and helps speed up data entry.
Smart Fill is enabled by default on Google Sheets. To activate it, simply:
- Enter data in Google Sheets, then
- Press ⌘ + Shift + Y on Mac or CTRL + Shift + Y on Windows/Chromebook.
To enable or disable Smart Fill, go to Tools > Autocomplete and check or uncheck “Enable Autocomplete” in the menu:
Another useful feature of Smart Fill is that it’s not limited to pattern detection. Smart Fill can also use data from Google’s Knowledge Graph to fill in data. For example, if you provide a list of cities, Smart Fill will detect their countries using the Knowledge Graph.
Let’s look at a few examples to help you understand how Smart Fill works.
1. Splitting First Names and Last Names
If you have a list of full names, Smart Fill allows you to create separate columns for their first names and last names. This comes in handy when creating a spreadsheet for, let’s say, personalized emails.
Manually splitting names can be a nightmare. Thankfully, Smart Fill can recognize the pattern after just a few separated names. Let’s start with a list of full names:
Let’s now manually separate the first two names:
And voilà, Smart Fill automatically triggers. After accepting the suggestions, the “First Name” column gets filled:
Now, place the cursor in the “Last Name” column:
Smart Fill triggers again! It successfully fills the “Last Name” column:
This is a simple example of data splitting in Google Sheets using Smart Fill, and the same principles apply to more complex situations. For example, Smart Fill can be used to split zip codes from a complete address.
2. Combining First Names and Last Names
Smart Fill not only helps you split data, but it also allows you to combine it. Let’s look at a simple example where we need to combine two lists of first names and last names:
Once we combine two names under the “Full Name” section, the Smart Fill suggestion pops up:
And by accepting them, the column gets automatically filled:
Method 3: How to Fill Google Sheets Automatically with Magical
When you need to gather data from different platforms, the autocompletion features in Google Sheets may not be enough. You would still have to manually copy your external data and move it to Google Sheets, which can be time-consuming and… well, boring.
Here are some examples of situations where you might want to automatically fill Google Sheets with data from other sources:
- You’re a recruiter and need to transfer candidate information into a spreadsheet for data analysis.
- You’re a salesperson and need to transfer data about companies or individuals into a sheet.
- You’re searching for Airbnbs, a new house on Zillow, or a car on Autotrader, and you want to compile an easy-to-reference list in a Google Sheets spreadsheet.
This is where Magical comes in. Magical is a super simple yet powerful Chrome extension that helps you collect and move data between different websites without any API or integration. No coding required.
Here are a few examples of how you can use Magical to automatically fill information into Google Sheets:
1. Automatically Fill Google Sheets with Prospects’ Information
Let’s say you work in a sales team. You need to enter contact information for different prospects into Google Sheets from various platforms.
All you have to do is open the different pages:
And let Magical take care of the rest:
Thanks to Magical, sales teams have collectively saved over 42 years of time! 🤯
2. Automatically Fill Google Sheets with Listing Details
Magical allows you to transfer more than just contact information. You can also use it to automatically fill Google Sheets with information from online databases, such as Airbnb listings or Amazon product listings. For example, let’s see how to use Magical to extract an Airbnb listing.
Simply open the listing:
Activate Magical’s Airbnb listing transfer:
And use keyboard shortcuts to instantly fill your Google Sheets spreadsheet:
You can also open multiple tabs for different Airbnb listings and extract the data from all of them with just one click using the “Transfer All” button.
3. Automatically Fill Google Sheets with Customer Reviews
Keeping track of your reviews is important to understand what customers think about your product, service, and brand. But when those reviews are scattered across different platforms, it’s not always easy to keep up. This might be a reason to create a Google Sheets spreadsheet filled with reviews or to collect them for quarterly customer success reports.
With Magical, you can extract reviews and automatically fill Google Sheets with the data hassle-free. Magical works with all major review sites, so you can extract reviews from G2, TrustPilot, Amazon, eBay, Capterra, and others.
For example, let’s say a small business wants to collect G2 reviews. To extract them into a Google Sheets spreadsheet, you start by creating a transfer:
Then, you choose “G2” as the source and “Sheets” as the destination:
And Magical takes care of the rest:
What other Google Sheets autocompletion transfers can you achieve with Magical?
We’re constantly adding new transfer options, but here’s a preview of what you can automatically fill in Google Sheets using Magical right now:
- LinkedIn profiles, jobs, and companies to Google Sheets
- Indeed job listings to Google Sheets
- GitHub repositories to Google Sheets
- Real estate listings to Google Sheets
- Zillow listings to Google Sheets
- Monster Jobs listings to Google Sheets
- Crunchbase company details to Google Sheets
- Salesforce data to Google Sheets
- Lever candidates to Google Sheets
- Greenhouse people and jobs to Google Sheets
- Redfin listings to Google Sheets
- G2 reviews to Google Sheets
- Instagram and Facebook data to Google Sheets
- Glassdoor job listings and insights to Google Sheets
- YouTube videos and pages to Google Sheets
- Twitter profiles to Google Sheets
- Dice job listings to Google Sheets
- Etsy listings to Google Sheets
- Yelp business details to Google Sheets
- Alibaba listings to Google Sheets
Don’t see the one you need? Reach out to us directly on Twitter — it’s probably in development.
Frequently Asked Questions
Looking for quick answers? Here are the most common questions about Google Sheets autocompletion, along with their answers.
1. What’s the fastest way to automatically fill Google Sheets with external data?
By using Magical, our free downloadable app that allows you to instantly collect data and automatically transfer it to your Google Sheets. 🪄
2. What is the keyboard shortcut for Smart Fill on Windows/Chromebook?
It’s CTRL + Shift + Y.
3. What is the keyboard shortcut for Smart Fill on Mac?
It’s ⌘ + Shift + Y.
4. What is the keyboard shortcut for autocompletion (fill handle) in Google Sheets?
The keyboard shortcuts for autocompletion on Windows/Chromebook are:
- CTRL + R to fill horizontally (to the right).
- CTRL + D to fill vertically (down).
- CTRL + Enter to fill what you have selected.
If you’re a Mac user, replace CTRL with ⌘.
Now that you have all the tools and techniques to automatically fill Google Sheets, you’re ready to save time and effort in data entry. Say goodbye to tedious tasks and hello to productivity! And if you want more valuable insights and tips, be sure to check out Crawlan.com for all your Google Sheets needs.