VBA & Macro Problems

How to Automatically Add 'Not Started' Status to New Excel Table Rows

Khadija Khan Sep 30, 2026 870 views

Question details

The user wants to automatically insert the default value 'Not Started' into a Status column whenever a new row is added to an existing spreadsheet table.

How to Automatically Add 'Not Started' to New Excel Table Rows

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing task lists or project statuses within a structured spreadsheet table where new entries are frequently added.
Observed behavior: When adding a new row to the table, the Status column remains blank and requires manual data entry to show 'Not Started'.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application to access the VBA Editor, and remember to save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve your automation code.

Solution 1Recommended

Use a VBA Event Macro to Auto-Populate New Rows

Because Excel tables do not have a native 'default value' setting for columns, using a Worksheet_Change VBA event is the most reliable way to automatically fill in a specific status when a new row is detected.

This method uses a background script that monitors your worksheet for changes. When it detects that a new row has been added to your target table, it automatically enters 'Not Started' into the designated Status column.

Since exact VBA code depends on your specific table name and column structure, you may need to adjust the cell references within the code.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

Access the Worksheet Module

In the Project Explorer pane on the left, double-click the name of the worksheet that contains your table (e.g., Sheet1).

Insert the Worksheet_Change Code

Select 'Worksheet' from the left drop-down at the top of the code window, and 'Change' from the right drop-down. Write a script that checks if the Target.Row is outside the previously used range and inputs 'Not Started' into the required column offset.

Save as Macro-Enabled

Close the VBA Editor and save your file as an Excel Macro-Enabled Workbook (*.xlsm) so the script runs automatically in the future.

Use a VBA Event Macro to Auto-Populate New Rows

Community Assistance for Custom VBA: For customized VBA code tailored to your exact table name, column layout, and trigger events, posting your specific workbook details in a VBA-focused community such as Stack Overflow is highly recommended.

Automate Your Tasks with WPS Spreadsheet

Manage Table Statuses Efficiently in WPS Spreadsheet

WPS Spreadsheet offers powerful support for table formulas, data validation, and automated table expansions. You can easily set up dynamic task trackers using smart formulas or seamlessly run your existing macro-enabled workbooks.

1. Format Data as a Table: Highlight your data range in WPS Spreadsheet and press Ctrl + T to format it as a Table, enabling auto-expanding capabilities.
2. Apply an Auto-Filling Formula: Instead of VBA, click the first cell of your Status column and enter =IF(A2<>"","Not Started",""). Replace 'A2' with your primary entry column.
3. Add New Rows Seamlessly: Type data into a new row at the bottom of the table. WPS Spreadsheet will automatically copy the formula down, instantly displaying 'Not Started'.
4. Run Existing Macros: If you already have a VBA script, simply open your .xlsm file in WPS Office (Premium required for VBA creation) to run the automation flawlessly.

Fully compatible with Microsoft Excel .xlsx and .xlsm formats.Built-in Data Validation to easily create drop-down lists for 'Not Started', 'In Progress', and 'Complete'.Auto-expanding tables ensure that your status formulas apply to new rows instantly.Lightweight, fast, and completely free for standard spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I set a default value in an Excel table without using VBA?

While Excel doesn't have a direct 'default value' property for table columns, you can achieve this by using an IF formula (e.g., =IF([@Task]<>"","Not Started","")). Due to the table's auto-expansion feature, the formula will automatically copy down and display the status when you type in a new row.

Why isn't my VBA code running when I add a new row?

This usually happens if macros are disabled in your Trust Center settings, if the file was not saved as a Macro-Enabled Workbook (.xlsm), or if the code was placed in a standard module instead of the specific Worksheet object module.

Can I use Data Validation drop-downs alongside VBA automation?

Yes. You can configure a Data Validation drop-down list for your Status column to include 'Not Started', 'In Progress', and 'Complete'. The VBA code or formula will simply provide the initial default value, which users can later update using the drop-down menu.

What happens if I save a workbook with VBA as a standard .xlsx file?

If you save a workbook containing VBA macros as a standard Excel Workbook (.xlsx), all VBA code will be permanently deleted upon saving. Always ensure you select 'Excel Macro-Enabled Workbook (*.xlsm)' from the 'Save as type' drop-down.

Excel & Data Problems