Autofill Google Sheets Columns: A Step-by-Step Guide

Aug 8, 2025 by Felix Dubois 53 views

Automate Google Sheets: Autofill Columns Based on Denomination Sold

Hey guys! Ever found yourself doing the same repetitive tasks in Google Sheets? It can be a real time-suck, right? Well, what if I told you there's a way to automate some of those tasks, making your life a whole lot easier? In this article, we'll dive into how you can automate your Google Sheets using formulas and scripts to autofill columns based on the value you enter in another column. Specifically, we'll tackle the scenario of automatically filling the "Selling At" and "SC Used Per Transaction" columns when you input the "Denomination Sold" in your Google Sheet. This is super useful for tracking sales, managing inventory, or any situation where certain values are consistently associated with others. Let's get started and make your spreadsheets work smarter, not harder!

Imagine you're running a business that deals with different denominations of products, let's say phone cards. Every time you sell a card, you need to record the denomination sold, the price you sold it at, and the service charge (SC) used per transaction. Manually entering this information for each sale can be tedious and prone to errors. The goal here is to create a system where you simply enter the "Denomination Sold," and the "Selling At" price and "SC Used Per Transaction" automatically populate. This not only saves time but also ensures consistency in your data. Think about the possibilities! You could apply this same concept to various other scenarios, such as inventory management, order processing, or even tracking student grades. By automating these tasks, you free up your time to focus on more important things, like growing your business or spending time on the things you love. So, buckle up, and let's learn how to make Google Sheets your automation powerhouse!

Before we jump into the how-to, let's break down the scenario we're tackling. We have a Google Sheet with several columns, including "Denomination Sold," "Selling At," and "SC Used Per Transaction." The idea is that when you enter a specific denomination in the "Denomination Sold" column, the corresponding "Selling At" price and "SC Used Per Transaction" values should automatically fill in. For example, if you enter "$10" in the "Denomination Sold" column, the "Selling At" column might automatically populate with "$12," and the "SC Used Per Transaction" column might fill with "2." This automation relies on a predefined relationship between the denomination and the other two values. This relationship can be stored in a separate table within the same sheet or in a different sheet altogether. The key is to have a clear mapping between the "Denomination Sold" and its corresponding values. Once this mapping is in place, we can use Google Sheets formulas or scripts to look up the values and autofill the columns. This not only saves time but also reduces the risk of manual data entry errors. Imagine the possibilities – no more typos, no more accidentally entering the wrong price! Just consistent, accurate data every time.

The beauty of this approach is its flexibility. You can easily update the mapping table to reflect changes in pricing or service charges. This means your automation can adapt to your evolving business needs. For instance, if you decide to increase the selling price of a particular denomination, you simply update the corresponding value in the mapping table, and the changes will automatically be reflected in your sheet. This dynamic nature of the system makes it a powerful tool for managing your data efficiently. Furthermore, you can extend this concept to other scenarios as well. Maybe you want to automatically fill in customer details based on their ID, or perhaps you want to calculate discounts based on the quantity of items purchased. The possibilities are endless! By understanding the core principles of looking up values and autofilling columns, you can create a truly customized and efficient workflow for your Google Sheets.

There are primarily two methods to achieve this autofilling magic in Google Sheets: using formulas and using Google Apps Script. Each method has its strengths and is suitable for different levels of complexity and automation needs. Let's explore each of these methods in detail.

Method 1: Using Formulas (VLOOKUP or INDEX/MATCH)

Formulas are the bread and butter of Google Sheets, and they can be incredibly powerful for automating tasks. For this particular scenario, the VLOOKUP and INDEX/MATCH formulas are your best friends. These formulas allow you to search for a value in one column (in our case, the "Denomination Sold") and return a corresponding value from another column (the "Selling At" and "SC Used Per Transaction").

VLOOKUP

VLOOKUP is a classic formula for looking up values in a table. It searches for a value in the first column of a range and returns a corresponding value from a specified column in the same row. The syntax for VLOOKUP is as follows:

=VLOOKUP(search_key, range, index, [is_sorted])

search_key: The value you want to search for (e.g., the denomination sold).
range: The table where you want to search and retrieve data.
index: The column number in the range from which to return a value.
is_sorted: Optional. Indicates if the first column in the range is sorted. Use TRUE if sorted (for approximate match) or FALSE (for exact match).

Let's say you have a table in your sheet (or another sheet) with the denominations in the first column, the selling prices in the second column, and the SC per transaction in the third column. You could use VLOOKUP to autofill the "Selling At" column with the following formula (assuming the denomination is in cell A2, and the table is in the range G2:I10):

=VLOOKUP(A2, G2:I10, 2, FALSE)

This formula searches for the value in A2 within the first column of the range G2:I10, and if it finds a match, it returns the value from the second column (the selling price). Similarly, you can autofill the "SC Used Per Transaction" column using a similar formula, but with the index set to 3:

=VLOOKUP(A2, G2:I10, 3, FALSE)

INDEX/MATCH

While VLOOKUP is powerful, INDEX/MATCH is often considered a more flexible and robust alternative. The INDEX/MATCH combination allows you to look up values based on both row and column numbers, giving you greater control over your data retrieval.

INDEX returns the value of a cell in a range based on its row and column number.
MATCH returns the relative position of a value in a range.

The syntax for INDEX/MATCH is:

=INDEX(array, MATCH(search_key, lookup_range, [match_type]), column_number)

array: The range from which to return a value.
search_key: The value to search for.
lookup_range: The range to search within.
match_type: Optional. Specifies the type of match. Use 0 for an exact match.
column_number: The column number in the array from which to return a value.

Using the same example as before, you could use INDEX/MATCH to autofill the "Selling At" column with the following formula:

=INDEX(H2:H10, MATCH(A2, G2:G10, 0))

This formula first uses MATCH to find the row number where the denomination in A2 matches a value in the range G2:G10. Then, it uses INDEX to return the value from the corresponding row in the range H2:H10 (the selling prices). For the "SC Used Per Transaction" column, you would use a similar formula, but with the index range adjusted:

=INDEX(I2:I10, MATCH(A2, G2:G10, 0))

Choosing Between VLOOKUP and INDEX/MATCH

Both VLOOKUP and INDEX/MATCH can effectively autofill columns in Google Sheets. However, INDEX/MATCH offers some advantages. It's more flexible because you can insert or delete columns in your lookup table without breaking the formula. VLOOKUP, on the other hand, relies on the column number, so if you insert a column, you'll need to update the formula. Additionally, INDEX/MATCH can handle lookups from right to left, while VLOOKUP can only look up values in the first column of the range. So, while VLOOKUP is easier to learn initially, INDEX/MATCH is generally the preferred choice for more complex scenarios.

Method 2: Using Google Apps Script

For more advanced automation and customization, Google Apps Script is the way to go. Google Apps Script is a cloud-based scripting language based on JavaScript that lets you automate tasks in Google Workspace applications, including Google Sheets. With Apps Script, you can create custom functions, automate workflows, and even build web applications that interact with your Google Sheets data.

Setting up the Script

To use Google Apps Script, you'll need to open the Script editor in your Google Sheet. Go to "Tools" > "Script editor." This will open a new tab with the Apps Script editor. Here, you can write your script to autofill the columns based on the denomination sold.

Writing the Script

Here's an example of a Google Apps Script that autofills the "Selling At" and "SC Used Per Transaction" columns when a value is entered in the "Denomination Sold" column:

function onEdit(e) {
  // Get the active sheet
  var sheet = e.range.getSheet();
  // Check if the edited column is the "Denomination Sold" column (e.g., column 1)
  if (e.range.getColumn() == 1) {
    // Get the edited row
    var row = e.range.getRow();
    // Get the denomination sold
    var denomination = sheet.getRange(row, 1).getValue();
    // Define the lookup table (adjust range as needed)
    var lookupRange = sheet.getRange("G2:I10").getValues();
    // Loop through the lookup table
    for (var i = 0; i < lookupRange.length; i++) {
      // Check if the denomination matches
      if (lookupRange[i][0] == denomination) {
        // Autofill "Selling At" (column 2) and "SC Used Per Transaction" (column 3)
        sheet.getRange(row, 2).setValue(lookupRange[i][1]);
        sheet.getRange(row, 3).setValue(lookupRange[i][2]);
        // Exit the loop once a match is found
        break;
      }
    }
  }
}

Let's break down this script:

function onEdit(e): This is a special function that automatically runs whenever a cell in the sheet is edited. The e parameter contains information about the edit event.
var sheet = e.range.getSheet();: This line gets the active sheet where the edit occurred.
if (e.range.getColumn() == 1): This checks if the edited column is the "Denomination Sold" column (assuming it's the first column, column 1). Adjust the number if your "Denomination Sold" column is different.
var row = e.range.getRow();: This gets the row number of the edited cell.
var denomination = sheet.getRange(row, 1).getValue();: This gets the value from the edited cell (the denomination sold).
var lookupRange = sheet.getRange("G2:I10").getValues();: This gets the values from the lookup table (the table with denominations, selling prices, and SC per transaction). Adjust the range to match your table.
The for loop iterates through the lookup table.
if (lookupRange[i][0] == denomination): This checks if the denomination in the lookup table matches the denomination sold.
If a match is found, the script autofills the "Selling At" (column 2) and "SC Used Per Transaction" (column 3) columns using sheet.getRange(row, 2).setValue(lookupRange[i][1]); and sheet.getRange(row, 3).setValue(lookupRange[i][2]);.
break;: This exits the loop once a match is found, improving efficiency.

Saving and Running the Script

After writing the script, save it by clicking the save icon. You might be prompted to give your script a name. Once saved, the script will automatically run whenever a cell in the sheet is edited. However, the first time you run the script, you'll need to grant it permissions to access your Google Sheet. This is a security measure to ensure that scripts only access the data they're authorized to access.

Advantages of Using Google Apps Script

Google Apps Script offers several advantages over formulas for autofilling columns:

More flexibility and customization: You can write complex logic and perform actions that are not possible with formulas.
Integration with other Google services: You can integrate your sheet with other Google services like Gmail, Calendar, and Drive.
Automation of complex workflows: You can automate multi-step processes that involve data manipulation and interaction with external systems.

However, Apps Script also requires some programming knowledge, so it might not be the best option for beginners. If you're comfortable with JavaScript or willing to learn, Apps Script can be a powerful tool for automating your Google Sheets.

Now that we've covered the theory, let's dive into a practical step-by-step guide on how to implement these methods in your Google Sheet. We'll cover both using formulas (VLOOKUP or INDEX/MATCH) and using Google Apps Script.

Using Formulas (VLOOKUP or INDEX/MATCH)

Step 1: Set up your data table.

First, you'll need to create a table that maps the "Denomination Sold" to the corresponding "Selling At" and "SC Used Per Transaction" values. This table can be in the same sheet or in a separate sheet. For this example, let's assume you have a sheet named "Data" with the following structure:

Denomination Sold	Selling At	SC Used Per Transaction
$10	$12	2
$20	$24	3
$50	$55	5
$100	$110	8

This table will serve as our lookup table for the formulas.

Step 2: Write the formula in your main sheet.

Now, go to the sheet where you want to autofill the columns. Let's say you have the following columns:

Denomination Sold	Selling At	SC Used Per Transaction

In the first cell of the "Selling At" column (e.g., B2), enter the following formula using VLOOKUP:

=VLOOKUP(A2, Data!A:C, 2, FALSE)

A2: The cell containing the "Denomination Sold".
Data!A:C: The range of the lookup table in the "Data" sheet (columns A to C).
2: The column number in the lookup table from which to return the value (Selling At is in the second column).
FALSE: Specifies an exact match.

In the first cell of the "SC Used Per Transaction" column (e.g., C2), enter the following formula:

=VLOOKUP(A2, Data!A:C, 3, FALSE)

This is the same formula as before, but with the index changed to 3 to retrieve the value from the third column (SC Used Per Transaction).

Alternatively, you can use the INDEX/MATCH formula. In the first cell of the "Selling At" column (e.g., B2), enter:

=INDEX(Data!B:B, MATCH(A2, Data!A:A, 0))

Data!B:B: The range containing the selling prices in the "Data" sheet.
A2: The cell containing the "Denomination Sold".
Data!A:A: The range containing the denominations in the "Data" sheet.
0: Specifies an exact match.

In the first cell of the "SC Used Per Transaction" column (e.g., C2), enter:

=INDEX(Data!C:C, MATCH(A2, Data!A:A, 0))

Step 3: Drag the formula down to apply it to other rows.

Once you've entered the formulas in the first row, you can drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formulas to other rows. This will automatically adjust the cell references in the formulas, so they correctly look up the values for each denomination sold.

Step 4: Test your formulas.

Now, try entering a denomination in the "Denomination Sold" column. The corresponding "Selling At" and "SC Used Per Transaction" values should automatically fill in based on your lookup table. If the values don't appear, double-check your formulas and ensure that the lookup table is set up correctly.

Using Google Apps Script

Step 1: Open the Script editor.

Go to "Tools" > "Script editor" in your Google Sheet. This will open a new tab with the Apps Script editor.

Step 2: Write the script.

Copy and paste the following script into the Script editor:

function onEdit(e) {
  // Get the active sheet
  var sheet = e.range.getSheet();
  // Check if the edited column is the "Denomination Sold" column (e.g., column 1)
  if (e.range.getColumn() == 1) {
    // Get the edited row
    var row = e.range.getRow();
    // Get the denomination sold
    var denomination = sheet.getRange(row, 1).getValue();
    // Define the lookup table (adjust range as needed)
    var lookupRange = sheet.getRange("Data!A2:C10").getValues();
    // Loop through the lookup table
    for (var i = 0; i < lookupRange.length; i++) {
      // Check if the denomination matches
      if (lookupRange[i][0] == denomination) {
        // Autofill "Selling At" (column 2) and "SC Used Per Transaction" (column 3)
        sheet.getRange(row, 2).setValue(lookupRange[i][1]);
        sheet.getRange(row, 3).setValue(lookupRange[i][2]);
        // Exit the loop once a match is found
        break;
      }
    }
  }
}

Step 3: Customize the script.

Adjust the if (e.range.getColumn() == 1) condition if your "Denomination Sold" column is not the first column.
Adjust the var lookupRange = sheet.getRange("Data!A2:C10").getValues(); line to match the range of your lookup table in the "Data" sheet. Make sure to include the sheet name if the lookup table is in a different sheet.

Step 4: Save the script.

Click the save icon and give your script a name (e.g., "AutofillColumns").

Step 5: Run the script and grant permissions.

The script will run automatically whenever a cell is edited. However, the first time you run the script, you'll need to grant it permissions to access your Google Sheet. You'll see a dialog box asking for permissions. Click "Continue" and follow the prompts to grant the necessary permissions.

Step 6: Test your script.

Now, try entering a denomination in the "Denomination Sold" column. The corresponding "Selling At" and "SC Used Per Transaction" values should automatically fill in based on your lookup table. If the values don't appear, double-check your script for any errors and ensure that the lookup table is set up correctly.

Now that you've mastered the basics of autofilling columns in Google Sheets, let's explore some advanced tips and tricks to take your automation skills to the next level.

Handling Errors and Edge Cases

One important aspect of automation is handling errors and edge cases gracefully. What happens if the denomination sold is not found in the lookup table? You don't want your formulas or scripts to return errors or, worse, fill in incorrect values. Here are some techniques for handling these situations.

Using IFERROR with Formulas

The IFERROR function in Google Sheets allows you to specify a value to return if a formula results in an error. This is particularly useful when using VLOOKUP or INDEX/MATCH, as these formulas can return #N/A if a match is not found. To use IFERROR, simply wrap your formula within the IFERROR function and provide the value you want to return in case of an error. For example:

=IFERROR(VLOOKUP(A2, Data!A:C, 2, FALSE), "Not Found")

This formula will return "Not Found" if the denomination in A2 is not found in the lookup table. You can replace "Not Found" with any value you want, such as an empty string ("") to leave the cell blank, or a specific error message.

Checking for Matches in Apps Script

In Google Apps Script, you can add a check to see if a match was found in the lookup table before setting the values. This prevents the script from writing incorrect values if a denomination is not found. Here's how you can modify the script from the previous section:

function onEdit(e) {
  // Get the active sheet
  var sheet = e.range.getSheet();
  // Check if the edited column is the "Denomination Sold" column (e.g., column 1)
  if (e.range.getColumn() == 1) {
    // Get the edited row
    var row = e.range.getRow();
    // Get the denomination sold
    var denomination = sheet.getRange(row, 1).getValue();
    // Define the lookup table (adjust range as needed)
    var lookupRange = sheet.getRange("Data!A2:C10").getValues();
    // Flag to track if a match is found
    var matchFound = false;
    // Loop through the lookup table
    for (var i = 0; i < lookupRange.length; i++) {
      // Check if the denomination matches
      if (lookupRange[i][0] == denomination) {
        // Autofill "Selling At" (column 2) and "SC Used Per Transaction" (column 3)
        sheet.getRange(row, 2).setValue(lookupRange[i][1]);
        sheet.getRange(row, 3).setValue(lookupRange[i][2]);
        // Set matchFound to true
        matchFound = true;
        // Exit the loop once a match is found
        break;
      }
    }
    // If no match is found, clear the "Selling At" and "SC Used Per Transaction" cells
    if (!matchFound) {
      sheet.getRange(row, 2).clearContent();
      sheet.getRange(row, 3).clearContent();
    }
  }
}

In this modified script, we've added a matchFound variable to track whether a match was found in the lookup table. If a match is found, we set matchFound to true. After the loop, we check the value of matchFound. If it's false, it means no match was found, and we clear the contents of the "Selling At" and "SC Used Per Transaction" cells using sheet.getRange(row, 2).clearContent(); and sheet.getRange(row, 3).clearContent();.

Dynamic Lookup Tables

In some cases, your lookup table might change over time. You might add new denominations, update prices, or modify service charges. If your lookup table is static, you'll need to manually update your formulas or scripts whenever the table changes. However, you can make your lookup table dynamic so that your automation automatically adapts to changes in the table.

Using Named Ranges

One way to create a dynamic lookup table is to use named ranges. A named range is a descriptive name that you assign to a range of cells. Instead of using cell references like Data!A2:C10 in your formulas or scripts, you can use the named range. The advantage of using named ranges is that you can adjust the size of the range without having to update your formulas or scripts. To create a named range, select the range of cells you want to name, then go to "Data" > "Named ranges" and enter a name for the range.

Using Formulas to Define the Lookup Range

Another way to create a dynamic lookup table is to use formulas to define the range in your VLOOKUP or INDEX/MATCH formulas. For example, you can use the COUNTA function to dynamically determine the number of rows in your lookup table. COUNTA counts the number of non-empty cells in a range. Here's an example of how you can use COUNTA with INDEX/MATCH:

=INDEX(Data!B:B, MATCH(A2, Data!A1:INDEX(Data!A:A,COUNTA(Data!A:A)), 0))

In this formula, COUNTA(Data!A:A) calculates the number of rows with data in column A of the "Data" sheet. INDEX(Data!A:A,COUNTA(Data!A:A)) then returns the last cell with data in column A. This dynamically adjusts the lookup range based on the number of rows in your table.

Combining Multiple Criteria

Sometimes, you might need to autofill columns based on multiple criteria. For example, you might want to consider both the denomination sold and the customer type when determining the selling price. In such cases, you can use more complex formulas or scripts to handle the multiple criteria.

Using Array Formulas

Array formulas allow you to perform calculations on multiple values at once. You can use array formulas with VLOOKUP or INDEX/MATCH to handle multiple criteria. To use an array formula, you typically press Ctrl+Shift+Enter (or Cmd+Shift+Enter on a Mac) instead of just Enter when entering the formula. This tells Google Sheets to treat the formula as an array formula.

Using Apps Script with Multiple Conditions

In Google Apps Script, you can use multiple if statements or a switch statement to handle multiple criteria. This gives you more flexibility in defining the logic for autofilling your columns.

So there you have it, folks! We've covered the ins and outs of autofilling columns in Google Sheets, from the basics of using formulas like VLOOKUP and INDEX/MATCH to the advanced techniques of Google Apps Script. We've explored how to handle errors, create dynamic lookup tables, and even combine multiple criteria for complex automation. By now, you should have a solid understanding of how to make your Google Sheets work for you, saving you time and reducing the risk of errors.

Autofilling columns is a powerful way to streamline your workflow and improve the efficiency of your spreadsheets. Whether you're managing sales data, tracking inventory, or organizing customer information, the techniques we've discussed in this article can help you automate repetitive tasks and focus on the things that matter most. Remember, the key to successful automation is to understand your data, define clear relationships between values, and choose the right method for the job. Formulas are great for simple lookups, while Google Apps Script is ideal for more complex scenarios.

Don't be afraid to experiment and try different approaches. The more you practice, the more comfortable you'll become with these techniques. And who knows, you might even discover new ways to automate your Google Sheets that we haven't even covered in this article! So go ahead, take what you've learned, and start building your own automation powerhouses in Google Sheets. Your future, more efficient self will thank you for it!