Using AutoFill to Speed Up Excel Office Scripts

A couple of weeks ago, I published an article about writing an Office Script in Excel:

Write Your First Office Script in Excel | by Kyle Gibson | Jan, 2023 | Medium

It was a basic example, showing how to loop through each row, applying the SUM function to each row during the iteration. Because our example had such few rows, there were no performance issues.

However, what if we have a large number of rows? Looping through each row could take much longer that we would like. Consider example data similar to the first article, except with 2500 rows this time:

Running the same script, to iterate through each row and sum them together one at a time, took approximately 15 seconds. That will most likely vary for you depending on different factors, but it’s clear that looping through each row won’t be very efficient as our dataset grows larger.

Let’s look at a more efficient way to accomplish the same thing.

Note: This article assumes that you have the Automate tab available in Excel. See the article linked above for how to find it if you don’t see it.

Step 1: Create the sample data to add two columns of numbers

I used the RANDBETWEEN function in Excel to generate my two columns of numbers, i.e. RANDBETWEEN(0,10). I generated 2500 rows of numbers for this example but you can do however many you want.

Step 2: Create a new script

Navigate to Automate > New Script. It should pull up an empty script like this:

If you want to change the name of this script, click into the field labeled Script 1 in my example above, and change it to whatever you prefer. For this example, I named mine Example Autofill:

Make sure to select Save script. Next, we will add some code to it.

Step 3: Add code to the main function

I’ve added comments in each step of this code to explain how it works. Update your script to reflect like the following:

function main(workbook: ExcelScript.Workbook) {
    // Step 1:
    // Get your sheet object
    let sheet =
        workbook
            .getWorksheet('Example')

    // Step 2:
    // Get amount of used rows in the first column, 
    // and then offset that by two columns to the right, 
    // so that we get the full range of cells where we need a total 
    let total_column =
        sheet
            .getRange("A1")
            .getEntireColumn()
            .getUsedRange()
            .getOffsetRange(1, 0)
            .getUsedRange()
            .getOffsetRange(0, 2)

    // Step 3:
    // Set your SUM formula in the first cell only in the Total column
    sheet
        .getRange("C2")
        .setFormula("=SUM($A2, $B2)")

    // Step 4
    // Apply an autofill to the whole total column, 
    // based on the formula in the first cell in the Total column
    sheet
        .getRange("C2")
        .autoFill(total_column, ExcelScript.AutoFillType.fillDefault);
}

As you can see, we are not looping through each row but rather utilizing the AutoFill capability of Excel to populate the Total column.

In my opinion, the most crucial part to understand in the code is Step 2 where we are using Offsets and UsedRanges. These are crucial functions in selecting the range for the Total column that needs to be AutoFilled.

Let’s analyze how that step works.

Step 4: Explanation of Step 2 in the Script

Step 2 in our Office Script contains this code:

let total_column =
        sheet
            .getRange("A1")
            .getEntireColumn()
            .getUsedRange()
            .getOffsetRange(1, 0)
            .getUsedRange()
            .getOffsetRange(0, 2

Here’s how it works:

sheet: We are starting with the sheet object defined in the beginning of the script.

.getRange(“A1”): We select the range for a single cell (A1) in the first column:

.getEntireColumn(): We select the entire first column (A):

.getUsedRange(): We select only the rows that are used in the first column:

.getOffsetRange(1, 0): We move our selection down 1 row, so that we don’t include the header row. Note that it still keeps the size of the range, so we have an empty row at the bottom:

.getUsedRange(): We again select only the rows that are used in the first column, without the header row and without the blank row from the previous step:

.getOffsetRange(0, 2): We move our selection 2 columns to the right.

And that’s it for how Step 2 in the code works. We have now selected the Total column range that needs to be AutoFilled with the SUM formula from cell C2. These concepts will be very useful in your Office Script development.

Step 5: Save and run your script

Make sure to save your script, and then run it:

After running, I can see that my formula correctly AutoFilled to the last row:

Not only are the totals populated…

…but it also worked extremely fast. I didn’t even bother setting a timer on running this script, because my Total column populated almost instantly. It was easily under 1 second, and it was probably under half a second. This process using AutoFill was much more efficient than looping through each row.

That’s it! While this example was just summing two columns, this same concept could be used for any number of operations or formulas where you need to populate a column.

I hope this helps you in your development of Office Scripts, and that it helped you realize new ways to automate your work.

Thanks for reading!