How to Fix Excel Autofill When Using R1C1 Reference Style
Question details
The user needs to fix an issue where formulas do not update correctly when using autofill with the R1C1 reference style.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Copying or autofilling a formula across multiple cells while working in the R1C1 reference style.
- Observed behavior
- Cell references remain unchanged instead of adjusting dynamically to their new positions.
Verify that your spreadsheet is set to the R1C1 reference style via the application options, and ensure you understand the syntax difference between absolute and relative numbering.
Use Bracketed Offsets for Relative R1C1 References
Switch from absolute R1C1 notation to relative bracketed notation so formulas update dynamically when copied or autofilled.
In the R1C1 reference style, references without brackets (like R1C1) are absolute, meaning they will always point to row 1, column 1, no matter where you copy the formula.
To allow the formula to adjust when using autofill, you must use relative references with brackets (like R[1]C[1]), which indicate the number of rows and columns away from the current cell.
Select the cell containing the formula that won't autofill correctly and check the formula bar. Look for references like 'R1C1' or 'R5C2' that lack square brackets.
Edit the formula to include square brackets for row and column offsets. For example, change 'R1C1' to 'R[1]C[-1]' to reference the cell one row down and one column to the left.
Press Enter to save the modified formula. Click and drag the fill handle at the bottom-right corner of the cell to autofill the formula across adjacent cells. The bracketed references will now adjust properly.

Manage Formulas Easily with WPS Spreadsheet
WPS Office provides a highly capable Spreadsheet application that fully supports standard A1 and R1C1 reference styles, making complex formula creation and autofill completely seamless.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
- 2. Access Application Options: Click on 'Menu' in the top left corner and select 'Options' from the drop-down list.
- 3. Toggle Reference Style: Navigate to the 'Calculation' tab, check or uncheck the 'R1C1 reference style' box, and click 'OK'.
- 4. Use Smart Autofill: Enter your relative R1C1 formula with brackets, then drag the fill handle over adjacent cells to automatically update the ranges.

Frequently Asked Questions
How do I turn off R1C1 reference style and go back to A1?
To switch back to the standard A1 style, navigate to Menu > Options > Calculation in your spreadsheet software, uncheck the 'R1C1 reference style' option, and click OK.
What does R[-1]C mean in a formula?
In R1C1 relative reference notation, R[-1]C refers to the cell located exactly one row above the current formula cell, in the exact same column.
Why did my spreadsheet automatically switch to R1C1 style?
This typically occurs if you open a workbook that was previously saved with the R1C1 reference style enabled. The spreadsheet software temporarily assumes the reference style of the first workbook opened in that session.
Can I mix absolute and relative references in R1C1 style?
Yes, you can use mixed references such as R1C[1] (absolute row 1, relative column offset right by 1) or R[2]C2 (relative row offset down by 2, absolute column 2) to fit your specific calculation needs.




