Categories
Design

How to use autofill on a google docs spreadsheet

Lowell is the founder and CEO of How-To Geek. He’s been running the show since creating the site back in 2006. Over the last decade, Lowell has personally written more than 1000 articles which have been viewed by over 250 million people. Prior to starting How-To Geek, Lowell spent 15 years working in IT doing consulting, cybersecurity, database management, and programming work. Read more.

Have you ever wanted to fill an entire row or column with a series of values? If you’re an Excel user, you can do the same thing in Google Docs. If you haven’t used either, here’s the quick way to do it.

Just type in a couple of numbers in sequence… 1 2 3 works pretty well. You could also put them across a row instead of down a column.

How to use autofill on a google docs spreadsheet

Then move your mouse over the dot in the corner until the pointer changes, then just drag it downward (or if you are filling a row instead, you can drag it to the right).

How to use autofill on a google docs spreadsheet

Let go of the mouse, and your data will be automatically filled in.

How to use autofill on a google docs spreadsheet

You could also make it skip by 1 instead, like 2 4 6 8, etc…

How to use autofill on a google docs spreadsheet

If you want to get really fancy, you can use the tip from reader Andy in the comments below… fill in the cells with something else that would normally be in a set, like the days of the week, then drag the blue dot downward…

How to use autofill on a google docs spreadsheet

And you’ll get a full list of days.

How to use autofill on a google docs spreadsheet

The same thing works for other information, basically anything that can be powered with Google Sets, and you can force Google Spreadsheets to always use Google Sets for the information by holding down the Ctrl key.

For example, if you type in Ford, Honda, Toyota, and hold down the Ctrl key while dragging the dot down…

How to use autofill on a google docs spreadsheet

It all works the same way. Sadly there’s no really advanced options like Excel has, but for most uses, this is good enough. Also, we’re aware this is a very simple tip for most of you, but we’re trying to help the beginners out as well!

I am a fan of creating to-do lists and trackers. And Google Sheets is my weapon of choice.

Since I have created a lot of these lists and trackers, I decided to merge all my trackers into one single Google Sheets document and then use this master tracker instead.

And to do this, I had to copy sheets from multiple Google Sheets into one single Google Sheets document.

While it’s not complicated, it took me a few minutes to figure it out.

So I thought I will share it with you all (in case you want to get this done yourself).

Copy a Sheet from One Google Sheets to Another

Below are the steps to create a copy of a sheet in another Google Sheets document:

  1. Open the Google Sheets document from which you want to copy the sheet. So these would be different trackers (lists) that I want to combine.
  2. Right-click on the sheet that you want to move to another master tracker Google Sheets document.
  3. Click on ‘Copy to..’ option.How to use autofill on a google docs spreadsheet
  4. In the dialog box that opens, you need to select the Google Sheets in which you want the sheet to be copied. This Google Sheets document could be in your own Google Drive or the one that is shared with you (with edit rights).How to use autofill on a google docs spreadsheet
  5. Once the Google Sheets in which you want to copy the sheets is selected, click on Select.

You will see a prompt that will tell you that the sheet has been copied. You can also open the target Google Sheets document, which now has the copied sheet.

Note that when the sheet is copied, it still remains in the source worksheet. A copy of the sheet is created in the target document.

In case you have the link to the Google Sheets in which you want to copy the sheet, you can simply enter the link at the bottom of the dialog box (step 4 above), and then click Select.

When you are adding data to a spreadsheet in Google Sheets, it’s very likely that some of that data will fall into a pattern.

Whether you are entering months, days of the week, or simply numbering your rows, it’s a common type of information to put into a cell.

But manually typing all of that data can be tedious, and it’s pretty easy to make a mistake when you start doing repetitive typing tasks like that. Luckily Google Sheets has a feature called Autofill that can make it a little easier.

How to Autofill Cells in Google Sheets

The steps in this article were performed in the desktop version of the Google Chrome Web browser, but will also work in other desktop browsers like Firefox or Edge.

Step 1: Sign into your Google Drive at https://drive.google.com and open the Sheets file in which you wish to use autofill.

Step 2: Type the first few items in the series that you wish to autofill.

How to use autofill on a google docs spreadsheet

Step 3: Select the cells containing the information that you entered.

How to use autofill on a google docs spreadsheet

Step 4: Click and hold down the mouse button on the blue handle at the bottom-right corner of the bottom cell in the series.

How to use autofill on a google docs spreadsheet

Step 5: Drag the handle down to select the cells that you wish to autofill.

How to use autofill on a google docs spreadsheet

Step 6: Release the mouse button to fill the cells.

How to use autofill on a google docs spreadsheet

Find out how to create a dropdown menu in Google Sheets for another way to expedite the process of creating your spreadsheets or eliminating typing errors.

How to use autofill on a google docs spreadsheet

Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.

He specializes in writing content about iPhones, Android devices, Microsoft Office and many other popular applications and devices.

How to use autofill on a google docs spreadsheet

Save as little or as much information as you like to speed up form filling. Here’s how to use Autofill in Chrome and manage saved items.

If you fill out forms online using Google Chrome, the handy Autofill feature can be a real-time-saver. Maybe you’re shopping and need to enter your physical address and payment method. Or maybe you’re completing an application form where you have to put in your phone number or email address.

Autofill in Chrome gives you options to manually enter any of these details or save them from a current form, and then populate future forms with a click. So that you can take advantage of the convenient Autofill feature, here’s how to use and manage the details in it.

How to Automatically Save Form Details in Chrome

You can enable a setting within Autofill so that you can automatically save the details you use to complete a form.

  1. Click the Customize and Control Chrome button (three dots) on the top right.
  2. Select Settings.
  3. You should land on the You and Google area of the Settings, but if not, select it on the left.
  4. The second section here is for Autofill. Start with either Payment methods or Addresses and more per your preference.

How to use autofill on a google docs spreadsheet

For further help with the passwords section you see, check out our detailed how-to for viewing saved passwords in Chrome.

Payment Methods

At the top of the Payment methods screen, enable the toggle for Save and fill payment methods. Optionally, you can turn on the option for Allow sites to check if you have payment methods saved. If you use Google Pay, you can click the link for Google Account to manage those specific ways to pay.

How to use autofill on a google docs spreadsheet

Addresses and More

There is just one toggle to Save and fill addresses that you can turn on. This includes a physical address with country or region, phone number, email address, and organization.

How to Manually Add Autofill Details

Whether you enable the toggle for payment methods, addresses, or both, you’ll go back to the same section for Autofill in your Chrome settings to manage those details.

Plus, you can add information manually to save time later if you like.

  1. Choose either Payment methods or Addresses and more.
  2. Click the Add button.
  3. Enter the required details and any optional information. For payment methods, you must enter the Card number or Name on a card. For addresses, you must enter at least one piece of information.
  4. Click Save.

How to use autofill on a google docs spreadsheet

Because you are not required to complete all fields, you may only include what you feel comfortable with. For instance, if you prefer not to enter and save your credit card number, you can simply include the expiration date and/or your name. Or for addresses, you can just enter and save a phone number or email address.

Even by saving a small amount of information, you can speed up filling out forms.

If you’d like more information on how Chrome saves and protects payment methods using Autofill, view the Google Chrome Privacy Whitepaper.

How to Use Autofill in Chrome

Once you have information saved in Autofill, the rest is easy. The next time you start completing a form in Chrome, you’ll see a prompt appear. To populate the remaining fields with the details you have saved, just click. If you have more than one option saved, for instance, if you save two addresses, just pick the one you want to use.

How to use autofill on a google docs spreadsheet

How to Edit or Remove Autofill Details

When you have a change to a saved payment method or address detail, you can easily make edits or completely remove Autofill items.

  1. Head back to Settings >You and Google >Autofill.
  2. Choose either Payment methods or Addresses and more.
  3. Click the More actions button (three dots) to the right of the item.
  4. Select Edit to make changes and be sure to click Save when you finish. Or choose Remove to delete the item completely.

How to use autofill on a google docs spreadsheet

Speed Up Form Filling with Autofill in Chrome

As mentioned, you can include as many or few details as you like with Chrome’s Autofill feature. So enter or save only what you want to speed up filling out forms.

Autofill is a helpful feature for other types of applications as well. Check out how to use autofill on Apple TV and iOS for easy logins or even how to save time on data entry with autofill in Excel.

Learn how to prefill answers in Google Forms using data from a Google Sheet and send the pre-populated Google Forms as personalized emails.

Prefilled Google Forms, where some of the fields in the form are pre-populated with answers you already have, make the process of filling out your forms easier and faster.

  1. Your contacts are more likely to fill out the form as it takes less time for them to complete the remaining fields.
  2. The form respondents are less likely to type incorrect data in fields, like the employee ID, that are pre-populated.
  3. The forms feels more personal when people see their name and other personalized information pre-filled in the form.

Create Pre-filled Google Forms with Google Sheets

This step-by-step video tutorial explains how you can create pre-filled Google Forms with dynamic information from a Google Sheet. You can then use Mail Merge or Document Studio to automatically send the prefilled forms to your contacts in bulk with Gmail.

In our example, the organization maintains their employee database in a Google Spreadsheet and they want to give employees an option to self-update their details in the spreadsheet with the help of Google Forms.

How to use autofill on a google docs spreadsheet

If you look at employee records in the Google Sheet carefully, you’ll find that only some details of the employees are missing in the sheet. This is a perfect use case for using prefilled Google Forms as it be wasting employee productivity if we send them a blank Google Form and require them to fill out every single field.

For instance, in row #2, we know the location and gender of Angus but his date of birth is unavailable in our records. For row #4, the employee ID and email is known but Kiran’s other details are missing.

Create the Google Form

To build this workflow, we’ll create a Google Form with fields corresponding to the columns in the source Google Sheet. Here’s how the final form would look like:

How to use autofill on a google docs spreadsheet

Generate the Prefilled Form Link

Inside the Google Form editor, click the 3-dot menu choose the Get pre-filled link option. Here, fill in every field with dummy data that is easy to recognize and replace later. Once the fields have been filled, click the Get Link button to generate the prefilled link and copy it to your clipboard.

How to use autofill on a google docs spreadsheet

The link to the prefilled Google Form would look something like this.

It’s long and complex but if you take a closer look, this is simply a collection of name and value pairs appended to the Google Form URL. Google Forms will assign a unique id to each field in the form and these are appended to the Form URL with your pre-populated value.

For instance, the Name field in your Google Form is internally represented as entry.1663131167 in the form URL. If we replace the parameter value EMPLOYEENAME in the URL with another value, that would be pre-populated in the Google Form.

And this is exactly what we’ll do to create personalized prefilled links for all the rows in our Google Sheet.

How to use autofill on a google docs spreadsheet

Add Form Formulas in Google Sheet

Inside your Google Spreadsheet, create a new sheet and rename it Form Link. Paste the prefilled Google Form link in the first cell (A1) of this blank sheet.

Next return to the Google Sheet that has the employee database and create a new column, say Google Form Link.

Now we need to replace the dummy values in our prefilled link with the actual values from the rows in the sheet and this can be easily done with SUBSTITUTE function of Google Sheets.

For instance, we need replace EMPLOYEENAME in the prefilled link with real names that are in column B of the spreadsheet. Our formula would be something like this:

We’ll feed the result of this formula into another SUBSTITUTE function to replace another field, say EMPLOYEEID.

This has to be repeated for every prefilled field in the Google Form.

If your prefilled data contains space, you need to wrap the results into another SUBSTITUTE function that will replace all occurrences of spaces with the plus symbol.

Our final prefilled link would be:

You can test the workflow using this prefilled Google Form that will write your form submission in a new row of this Google Sheet.

Copy-down the Google Forms Formula

You may use ArrayFormula to copy down formulas or, if you have only a few rows, select the first cell and drag the crosshair to the last row in the formula column as shown below:

How to use autofill on a google docs spreadsheet

Handling Dates in Google Forms

If you plan to prefill dates in the Google Form, you need rewrite your dates in the Google Sheets in a format that Google Forms can recognize.

This is easy to implement. Just select the column in your Google sheet that contains the dates, then go to the Format menu, choose Number > More Formats > More date and time format and choose the YY-MM-DD format.

How to Email Prefilled Google Form Links

You can use Mail Merge with Gmail to send the prefilled forms to all the email addresses in one go from the Google Sheet itself.

When composing the email template for merge, select any text in the email body and convert it into a hyperlink. You can put the title of the column – <> as the hyperlink and this would be replaced with your Google Form link.

How to use autofill on a google docs spreadsheet

Please watch the Mail Merge tutorial to learn more.

How to use autofill on a google docs spreadsheet

Google Docs templates save you from reinventing the wheel every time you need to create a similar-but-not-quite-the-same doc. Now, with Zapier, you can automate the template editing process, sending information from the other apps you use most into your Google Docs templates. You’ll be able to automatically create new Google Docs from an existing template without lifting a finger.

When to Use Zapier to Create a Document from a Template

Even when you use a Google Docs template, you’re still taking time to fill in the blanks. And if you do it enough, the few minutes it takes each time can add up. You need to find the source information, copy it over to the template, and then double and triple check that it’s right.

Zapier automates the process, saving you time—and decreasing the opportunity for human error.

For example, you may need to create new sales and marketing documentation on the regular. Each time you need to create an invoice or an RFQ, you’re copy/pasting information from one database into the document. When there are a lot of fields to fill in, it adds up. Or maybe you have a meeting notes template that needs to be populated with the names of attendees. Now, Zapier can find the information in your first app—say, QuickBooks or Google Calendar—and automatically populate your template for you, without sacrificing formatting.

In this tutorial, we’ll walk through one specific example, but remember that you can use this process to fill in a Google Docs template based on information in any of the 1,000+ other apps supported by Zapier.

How to Automatically Create a Document from a Template

Here, we’ll show you how to autopopulate a Google Docs offer letter template, using information from your HR software.

Before you start, you’ll need to be sure that your document template exists in Google Docs. You can use a pre-made template supplied by Google Docs, or you can create your own. If you need help getting started with templates, here’s a quick tutorial.

IMPORTANT: In order to create a fillable template in Google Docs, any text that you want to replace with information from another app needs to be formatted like this:

Make sure you have the double braces on both sides and that there are no spaces between words. Here’s what our offer letter template looks like wth that formatting in mind.

Now you’re ready to create your Zap—our term for an automated workflow.

Step 1: Create a new Zap.

Once you have a Zapier account, click the Make a Zap button in the top right, or go directly to the Zap editor.

Step 2: Set up your trigger.

Select your trigger app from the list. In this case, we’ll use Breezy HR.

Step 3: Select your specific trigger.

What needs to happen in order to start this workflow? In this case, we want to populate the offer letter whenever a candidate is moved to the “Made Offer” stage in our HR software. So we’ll choose New Candidate in Stage.

Step 4: Connect your account.

Select the account you’d like to connect. If you’ve never connected this account before, you’ll need to enter your credentials.

Step 5: Select the specific options for the trigger.

Here, we’re telling Zapier to only kick off the workflow for a specific job position (“Chocolate Taster”) and candidate stage (“Made Offer”).

Step 6: Test the trigger.

Make sure you have an entry in your trigger app that has all the information you might want to send to your document template. For example, here, we’ll want to be sure Breezy HR includes a candidate recently moved to “Made Offer” that contains at least the first name, start date, position title, and yearly compensation—i.e., the parts of the template we want to fill in.

Then, you’ll use that sample to test the step. Follow the instructions, and click Continue.

Step 7: Add an action step.

Click the plus sign, and select Action/Search.

Step 8: Select Google Docs as your action app.

Now it’s time to send that information to Google Docs. Select Google Docs as your action app.

Step 9: Select the action.

Here, you’ll select Create Document from Template as the action.

Step 10: Connect your Google Docs account.

Just like you did with your trigger app, you’ll need to connect your Google Docs account to Zapier.

Step 11: Map the information into your Google Docs template.

First, select the Google Docs template you want to use from the dropdown menu.

Then, name the template. You can name it dynamically based on information in your trigger app by clicking the plus sign next to the field and selecting the information. Here, we’re naming the document ” [ Candidate Name] Offer Letter,” where [ Candidate Name] will change depending on the information in Breezy HR.

By default, your filled-in template will be saved in your root folder in Google Drive, i.e., not nested within a folder. If you want to put it in a specific folder, click the dropdown and select the folder. To put it in a subfolder, click the dropdown again and select the subfolder.

Then you’ll complete the mapping for the rest of the fields. For example, our offer letter template has a spot for the employee’s name and position title, among other things. So we’d map that information from Breezy HR into the document template.

Step 12: Test your Zap.

The next step will prompt you to send a test to Google Docs. Click Send Test to Google Docs, and after a few seconds, you should see a success message.

Now you can go to the Google Docs folder you’d selected in Step 11. There, you should find your completed template.

Now, every time you move a candidate to the “Made Offer” stage, this template will be automatically filled in, with zero manual work required from you.

Want to take the process one step further? You can add a step to your Zap that would automatically send that offer letter to the user. So with one simple drag into a new stage in your HR software, you’ve created the offer letter and sent it to the candidate.

Remember, this is just one example of how you can use Zapier to autopopulate your Google Docs templates. Why not send your LinkedIn updates to your resume template? Or send incident reports from your dashboard software to a Google Docs template to kick off documentation for attending to the issue? Your options are only limited to the tools in your tech stack.

Get productivity tips delivered straight to your inbox

We’ll email you 1/wk, and never share your information.

How to use autofill on a google docs spreadsheet

Deb Tennen is managing editor of the Zapier blog. When she’s not working, Deb is either watching television or showing someone a picture of her dog.

Google Form Tutorial – This is the first tutorial from me in which I am going to show you How to populate or fill Google form fields with Spreadsheet Column values. This post also focus on dynamic Google forms based on choice of option from user. Situation I have considered for the tutorial is selecting City based on the Country opted, like such as if a user selects “India” and clicks on Continue button then next page should show Cities from India(Values stored in Spreadsheet).

How to use autofill on a google docs spreadsheet

AutoFill Google Form fields with Spreadsheet Column values

First you need to create a Google Form from Google Drive account. Select Create Form and enter the title of the form. Next step is to create a Country field but make sure to check the “Go to page based on answer” option. See the image below for more information:

How to use autofill on a google docs spreadsheet

Next Create new pages by inserting “Page break” option.

How to use autofill on a google docs spreadsheet

Create two pages, one for each country – India and USA. On each page add “Select from the list” items but make sure to leave the options blank.

How to use autofill on a google docs spreadsheet

Now is the time to select page destination based on user response. To do navigate back to first page and Country field. For each option select the page accordingly, for example see image below where I have selected Pages for India and USA(Country option)

How to use autofill on a google docs spreadsheet

First step of Tutorial is complete which allows you to select the destination page depending on the user response. Next Part of tutorial covers information to auto fill City Fields from Spreadsheet column values.

Create a spreadsheet to accept the Form responses and leave the first sheet as it is. Create new sheet in the same spreadsheet and add two columns for each city values of India and USA. See image below:

How to use autofill on a google docs spreadsheet

To Auto fill Google Form “formRanger” script needs to be installed. To do so click on “Script Gallery” menu item from Spreadsheet

How to use autofill on a google docs spreadsheet

Search for “formranger” script and click on install next to the latest version of the script. See image below:

How to use autofill on a google docs spreadsheet

Next step is to install the script, to do click on formRanger Menu item from spreadsheet and select “Run initial configuration” menu.

How to use autofill on a google docs spreadsheet

Authorize the script to get access to your App and once the installation is done, your spreadsheet is ready to populate Google Form field values. Once again click on formRanger menu and now you will see more menu items, select “Assign form items to columns” option.

How to use autofill on a google docs spreadsheet

On the next screen you just need to select the sheet which in this tutorial is “City” and the Column. See image below:

How to use autofill on a google docs spreadsheet

That’s it and visit Live form once again to see the City fields values from Spreadsheet columns.

About The Author

admin

TKA is owner and passionate blogger of Technokarak from Delhi, INDIA. He is an Engineer and loves to discover and learn new things on Internet. At Technokarak, he writes articles on WordPress, SEO, Mobiles, Tablets and other technical stuff.

Related Posts

How to use autofill on a google docs spreadsheet

How to Access Your Hotmail Login?

How to use autofill on a google docs spreadsheet

Reveal a Cheating Spouse with Cell Phone Spy

How to use autofill on a google docs spreadsheet

UnoTelly removes all restrictions from Watching TV shows outside USA

How to use autofill on a google docs spreadsheet

How to Backup Data in Android without Any Root

2 Comments

Thanks for the tip! I believe this will work once–it reads the spreadsheet to insert the values into the form for you. If you change the spreadsheet later, I think it will not update the form.

Is there any way to make things dynamically linked, so that if you update the spreadsheet, you’ll update the form?

hi, tried following your instructions but I couldn’t find a break page for the form on the insert button (this option is not given)

Easily import data from other tabs and spreadsheets

How to use autofill on a google docs spreadsheet

  • Facebook
  • Twitter
  • LinkedIn
  • Tweet
  • Share
  • Email

Before you can use Google Sheets to reference data from another sheet, you need to determine where that source data is coming from. If the data is in another tab inside the same spreadsheet, you only need to use a simple sheet reference to reference data from other sheets. If the data is in an entirely different Google Sheets file, you need to use a special spreadsheet key to pull data from another sheet.

Use Google Sheets to Pull Cell Data From Another Sheet

The most common reason people want to pull data from another sheet in Google Sheets is because those other tables are usually lookup tables.

For example, you might have one sheet that holds all of the products you sell along with their UPC code and unit price, while another sheet may contain a log of your sales. To calculate the total sale, you’ll need to pull pricing data from the product sheet.

Here’s how to do this:

In the original sheet where you want to pull data into, place your cursor in the cell where you’d like the data to go.

How to use autofill on a google docs spreadsheet

Type = (the equal sign) into the cell. Select the second sheet and, then, the cell that contains the data you want to bring into the original sheet.

How to use autofill on a google docs spreadsheet

Press Enter finish. This will bring the cell data you selected into the first spreadsheet.

How to use autofill on a google docs spreadsheet

The final formula in this case is =Sheet2!C2. ‘Sheet2’ is the name of the sheet where the data comes from. This method is good for referencing individual cell data from a different spreadsheet into an original one.

Looking up Data From Another Google Sheets Spreadsheet

The method above works well to pull individual cell data from one sheet into another, but what if you want to look up data from the other sheet?

In the original sheet where you want to pull data into, place your cursor in the cell where you’d like the data to go, then type =VLOOKUP( into the cell. Formula syntax help will appear.

How to use autofill on a google docs spreadsheet

For the search_key, select the cell in the original sheet you want to use as the lookup.

Type a comma (,) and switch to the sheet you want to pull data from. For range, select the entire range of cells you want to use as the lookup table.

How to use autofill on a google docs spreadsheet

Type a comma (,) again, and for the index, type the number of the column that contains the data you want to retrieve from the second spreadsheet.

How to use autofill on a google docs spreadsheet

Finally, type ,FALSE). This finishes the formula with a parameter that says the data in the other sheet isn’t sorted. If the data is sorted, then you’d make this TRUE.

How to use autofill on a google docs spreadsheet

Press Enter and you’ll see that the resulting Unit Price comes from the appropriate data cell from the other spreadsheet. Fill in the rest of the cells using this same formula.

How to use autofill on a google docs spreadsheet

This should fill out all of the correct data values, pulled in from the other spreadsheet, into the original sheet.

Pull Cell Data From a Different Spreadsheet File

While all of the formulas above work well for pulling data from another spreadsheet tab in the same Google Sheets file, you can also reference data from a different spreadsheet file.

To pull in data from another Google Sheets file, you need to use the IMPORTRANGE formula.

Before you can use the IMPORTRANGE formula, you’ll need the URL link to the Google Sheets file where you want to reference data. Highlight and copy the URL link to the end of the long code before the last forward slash (/) in the URL.

How to use autofill on a google docs spreadsheet

In the original sheet where you want to pull data to, place the cursor in the destination cell and type:

=IMPORTRANGE(“URL”

Be sure to replace URL in this formula with the URL you want to reference.

How to use autofill on a google docs spreadsheet

Follow the quotes after the URL with a comma (,), then type the name of the sheet and the cell you want to get data from.

In this example you’d enter:

=IMPORTRANGE(“URL”,”Sheet1!C2″).

Again, URL would be a full URL. We’re just keeping it short for example purposes.

How to use autofill on a google docs spreadsheet

Press Enter. You’ll see that the data from the other Sheets spreadsheet file is pulled into this spreadsheet.

How to use autofill on a google docs spreadsheet

You can use this same IMPORTRANGE function inside a VLOOKUP function to perform the same lookup method described in the previous section. This allows you to perform a VLOOKUP using data from another Google Sheets file.

Learn how to use the ARRAYFORMULA function in Google Sheets to quickly apply a formula to an entire column in the spreadsheet. The formula is also added to new rows automatically.

You are working inside a Google Spreadsheet where a formula needs to copied down to the last row of the sheet. You also need the formula to be added automatically when a new row is added to the Google Sheet.

There are several ways to solve this problem.

Copy Formula Down in Google Sheets

The easiest approach to copy down formulas is to use the fill handle in Google Sheets. Write your formula in the first row of your spreadsheet, and then point your mouse to the lower right corner of the formula cell.

How to use autofill on a google docs spreadsheet

The pointer changes into a fill handle (black plus symbol) that you can drag to the last row of the sheet. The fill handle will not just copy down the formulas to all the adjacent cells but also copies the visual formatting.

If you need to copy the formulas across cells but sans any formatting, select the cell that contains the formatting and press Ctrl+C to copy it to the clipboard. Next, select the range where that formula needs to applied, right-click, choose Paste Special and Paste Formula only.

How to use autofill on a google docs spreadsheet

Apply Formula to the Entire Column in Google Sheets

If you have hundreds of rows in a Google Spreadsheet and you want to apply the same formula to all rows of a particular column, there’s a more efficient solution than copy-paste – Array Formulas.

Highlight the first cell in the column and type the formula as earlier. However, instead of specifying a single cell as a parameter, we’ll specify the entire column using the B2:B notation (start from cell B2 and go all the way down to the last row of column B).

Then press Ctrl+Shift+Enter, or Cmd+Shift+Enter on Mac, and Google Sheets will automatically surround your formula with ARRAYFORMULA function.

How to use autofill on a google docs spreadsheet

Thus, we could apply the formula to the entire column of the spreadsheet with only a single cell. Array Formulas are more efficient as they process a batch of rows in one go. They are also easier to maintain as you only need to modify a single cell to edit the formula.

One issue that you may have noticed with the above formulae is that it applies to every row in the column where you have only want to add formulas to rows that contain data and skip the blank rows.

This can be done by adding an IF contain to our ARRAYFORMULA so that it doesn’t apply the formula the any of the blank rows.

Google Spreadsheet offers two functions to help test whether a cell is empty or now.

  • ISBLANK(A1) – Returns TRUE if the referenced cell is empty.
  • LEN(A1) <> 0 – Returns TRUE if the referenced cell not empty, FALSE otherwise

Our modified Array Formulas would therefore read:

Using ISBLANK(Cell Reference):

How to use autofill on a google docs spreadsheet

There are several other ways to test if a cell is blank or not:

Use Array Formulas inside Column Headers

In our previous examples, the text of the column titles (like Tax, Total Amount) was pre-populated and the formulas were only added to the first row of the dataset.

We can further improve our formula so that they can be applied to the column header itself. If the index of the current row is 1, calculated using the ROW() function, the formula outputs the column title else it performs the calculation using the formula.

How to use autofill on a google docs spreadsheet

Auto Fill Formulas into Google Form Submissions

ARRAYFORMULA functions are particularly useful for Google Forms when the form responses are getting saved inside a Google Sheet. You cannot do live calculations inside Google Forms but they can be performed inside the spreadsheet that is collecting the responses.

You can create new columns inside the Google Spreadsheet and apply the ARRAYFORMULA to the first row of the added columns.

When a new form submission is received, a new row would be added to the Google Sheet and the formulas would be cloned and automatically applied to the new rows without you have to copy-paste stuff.

How to Use VLOOKUP inside ARRAYFORMULA

You can combine ARRAYFORMULA with VLOOKUP to quickly perform a lookup across an entire column.

Say you have a “Fruits” sheet that lists the fruit names in column A and the corresponding prices in column B. The second sheet “Orders” has fruit names in column A, the quantity in column B and you are supposed to calculate the order amount in column C.

How to use autofill on a google docs spreadsheet

In simple English, if the row of the current cell is 1, output the column title in plain text. If the row is greater than 1 and the column A of the current row is not empty, perform a VLOOKUP to fetch the price of the item from the Fruits sheet. Then multiply that price with the quantity in cell B and output the value in cell C.

If your VLOOKUP range is in another Google Spreadsheet, use the IMPORTRANGE() function with the ID of the other Google Sheet.

Please note that you may have to use semicolons in the spreadsheet formulas instead of commas for some locales.