Formula Errors

How to Keep ROW Formula Reference Fixed When Filling Down in Excel

Khadija Khan Oct 10, 2026 873 views

Question details

The user needs to prevent the ROW function's cell reference from incrementing when dragging the formula down multiple cells.

How to Keep the ROW Formula Reference Fixed When Filling Down in Excel

Product: Excel
Device & OS: not provided
Scenario: Filling down a formula containing the ROW function across a column.
Observed behavior: Dragging =ROW(1:1) down automatically changes the reference to =ROW(2:2), =ROW(3:3), etc., instead of remaining fixed at row 1.

Before you start

Identify the specific cell or range containing the formula you want to copy, and ensure you understand whether you need to lock only the row, only the column, or both dimensions.

Solution 1Recommended

Use an Absolute Reference with Dollar Signs

Apply the dollar sign ($) to lock the row number so it remains fixed when dragged down.

In Excel, cell references are relative by default. Adding a dollar sign ($) before the row number creates a mixed or absolute reference, forcing Excel to keep that specific row constant no matter where the formula is copied.

Select the formula cell

Click on the cell containing your original =ROW(1:1) formula to make it active.

Edit the formula

Click into the formula bar at the top of the screen, or double-click the cell to enter edit mode.

Add absolute reference markers

Place your cursor next to the row numbers and add a dollar sign ($) before each to change the formula to =ROW($1:$1). Alternatively, you can use =ROW(A$1).

Fill the formula down

Press Enter to save the formula. Click the cell again, grab the square fill handle at the bottom-right corner, and drag it down your column. The formula will now stay fixed on row 1.

Use an Absolute Reference with Dollar Signs

Keyboard Shortcut: You can highlight the cell reference in the formula bar and press F4 on your keyboard to automatically toggle between absolute, mixed, and relative references.

Solution 2

Enter and Copy a Static Number

If you only need the number 1 repeated in every cell, bypass formulas entirely by copying a static value.

Efficient Spreadsheet Management

Master Formulas Easily with WPS Spreadsheet

WPS Office offers a fully compatible spreadsheet tool that makes handling absolute and relative references intuitive. You can easily lock rows and columns using the same F4 shortcut and functions you already know.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to manage your formulas.
2. Enter the locked formula: Type =ROW($1:$1) into your desired cell and press Enter.
3. Drag to fill: Click the bottom-right corner of the cell and drag it down. WPS Spreadsheet will perfectly maintain your locked row reference.

Fully compatible with Microsoft Excel formulas, formats, and functions.Quick F4 shortcut support to effortlessly toggle absolute and relative cell references.Free, lightweight, and user-friendly interface for seamless data analysis.

Frequently Asked Questions

What is the difference between relative and absolute references?

A relative reference changes when a formula is copied to another cell based on its relative position. An absolute reference, created by adding a dollar sign ($) like $A$1, remains entirely fixed no matter where the formula is copied.

How do I lock only the row but not the column?

To lock only the row, place the dollar sign just before the row number, such as A$1. When dragged horizontally, the column letter will change, but when dragged vertically, the row number stays exactly the same.

Why doesn't pressing F4 add the dollar signs in my formula?

Ensure you are actively editing the formula by double-clicking the cell or clicking inside the formula bar. Your text cursor must be placed directly on or next to the cell reference you want to lock before pressing F4.

Excel & Data Problems