Others

How to Autofill to the Last Used Row with Excel Office Scripts

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to use Excel Office Scripts to automatically fill a formula down a column dynamically, stopping at the last row containing data in an adjacent reference column.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating repetitive data formatting tasks by applying a formula to all populated rows without hardcoding row numbers.
Observed behavior: A static autofill range doesn't adapt to changing data sizes, requiring a dynamic script to calculate the final row based on the used range.

Before you start

Ensure you have an active Microsoft 365 subscription, as Office Scripts are primarily available in Excel on the web and Excel for Microsoft 365. Your workbook should be saved in OneDrive or SharePoint.

Solution 1Recommended

Use TypeScript to Dynamically Autofill Based on Used Range

Calculate the last used row in a reference column and use it to set the autofill range limit.

This approach uses the getUsedRange() method to find where data ends in a specific column (like column E), and then passes that dynamic row number to the autoFill method for your target column (like column F).

Open the Code Editor

Open your workbook in Excel on the web or Microsoft 365. Navigate to the Automate tab on the ribbon and click on New Script to open the Code Editor.

Define the Initial Formula

In your main function, get the active worksheet and set the initial formula in the starting cell. For example: sheet.getRange("F2").setFormulaLocal("=TEXT(E2, \"mm/dd/yyyy\")");

Calculate the Last Used Row

Determine the last row of the reference column by getting its used range. Add this logic: const usedRange = sheet.getRange("E:E").getUsedRange(); let lastRow = 2; if (usedRange) { lastRow = usedRange.getRowIndex() + usedRange.getRowCount(); }

Apply the Dynamic Autofill

Use the calculated lastRow variable to dynamically set the destination range for the autofill method: sheet.getRange("F2").autoFill(`F2:F${lastRow}`, ExcelScript.AutoFillType.fillDefault);

Save and Run

Save your script and click the Run button. The formula in F2 will automatically populate down to the final row of data present in column E.

Adapting for Multiple Columns: You can repeat this logic for other columns by updating the source column letters, target formula, and destination ranges accordingly within the same script.

Free Microsoft Office alternative

Try WPS Office for Powerful Spreadsheet Automation

While Microsoft Excel uses Office Scripts, WPS Office offers a built-in JS Macro Editor that lets you automate spreadsheet tasks for free. It provides a lightweight, highly compatible alternative for handling complex data operations without requiring a premium cloud subscription.

1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
2. Install WPS Office: Run the downloaded file and follow the on-screen instructions to complete the installation.
3. Open and Automate: Launch WPS Spreadsheets, open your .xlsx file, and explore the JS Macro features under the Tools tab.

Free to use with a familiar, user-friendly interface.Highly compatible with Microsoft Excel (.xlsx, .xls) formats.Supports powerful JS Macros for automating repetitive spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Office Script autofill applying to empty rows?

This happens if the reference column's getUsedRange() includes formatted but technically empty cells. Ensure you clear all formatting from empty cells below your data, or use a more precise script loop to find the last non-empty cell.

Can I use this script for multiple columns at once?

Yes. You can apply the same logic sequentially in your script for different columns (e.g., applying to column H and I). Just ensure you update the target ranges and recalculate the last row if the reference column changes.

Do Office Scripts work on the desktop version of Excel?

Office Scripts were originally designed for Excel on the web, but they are now fully supported in Excel for Microsoft 365 on Windows and Mac. They do not work in older, non-subscription desktop versions like Excel 2019 or 2016.

Office Quick Solutions