If the starting number is in A2 and you want the next number in B2, enter =A2+1 in B2, then fill the formula down; for example, 7 in A2 produces 8 in B2, and the next row produces 9 if A3 contains 8. If you want Excel to create a simple counting sequence in one column instead, enter the starting value and add 1 to the cell above in each row.
First decide what should increase
“Increase a number by 1 in each row” can describe two different spreadsheet tasks. You might want a new column that adds one to each existing value, or you might want a column of consecutive numbers such as 1, 2, 3, 4. The right method depends on whether each result should be based on a value in another column or on the previous row.
- Add one to existing values: Keep the original numbers in one column and calculate the increased numbers in a neighboring column. This preserves the source data and makes the change easy to check.
- Make a counting sequence: Put a starting number in the first cell and continue upward by one in each following row.
- Replace existing values: If you intend to overwrite the original numbers, make a copy of the column first. A formula in the source column can otherwise replace or depend on the values you need.
Adding one is not the same as increasing a value by one percent. If a price is 50, adding 1 produces 51; increasing it by 1% produces 50.50. For a one-unit increase, use addition, not a percentage calculation.
Add 1 to values in another column
This is usually the clearest approach when you already have numbers to update. With source values in A2:A100 and the increased results needed in B2:B100, enter =A2+1 in B2. The formula returns the value in A2 plus one. For example, if A2 is 12, B2 displays 13.
When you copy the formula down one row, Excel adjusts the relative reference from A2 to A3. So B3 calculates the number in A3 plus one, B4 uses A4, and so on. The original column stays unchanged, which is useful when you need to compare old and new values or verify that the results are correct.
To enter it in Microsoft Excel for Microsoft 365 on Windows:
- Click B2, next to the first source number in A2.
- Type
=A2+1and press Enter. - Select B2 again and use the fill handle—the small square at the lower-right corner of the selection—to drag the formula down as far as needed. You can also double-click the fill handle when the neighboring source column has a continuous block of data; Excel fills down to match that adjacent data.
If your data is an Excel table, entering the formula in the first row of the new table column will commonly fill the calculated column automatically. Check the first and last results before relying on a large fill, particularly if the source column has blank rows or irregular data.
Build a sequence in the same column
For a simple list such as 5, 6, 7, 8, type 5 in A2 and 6 in A3, select both cells, then drag the fill handle down. Selecting the first two values tells Excel the pattern: continue the series with a step of one. Selecting only the value 5 may copy 5 repeatedly, depending on the fill action, so use two seed values when you want Excel to infer a numeric sequence.
You can also use a formula that refers to the previous row. With 5 entered in A2 and the next number to appear in A3, enter =A2+1 in A3. A3 returns 6. Fill A3 down, and each formula refers to the cell directly above it: A4 adds one to A3, A5 adds one to A4, and so on. This is convenient when the starting value may change, because the rest of the sequence recalculates from it.
A formula-based sequence is not a set of fixed numbers: each cell contains a formula. If you need fixed values for export or for a process that should not recalculate, copy the completed range and use Paste Special > Values. This replaces the formulas with their displayed results. Keep the original formulas or a backup if you may need to revise the sequence later.
Use Fill Series for a fixed counting list
Excel’s Series command can create consecutive values without entering a formula in every cell. On Windows, enter the first value in A2, select the range to fill, and choose Home > Fill > Series. In the Series dialog, select Columns if the values should run down rows, choose Linear, set Step value to 1, and enter the desired Stop value. For example, starting at 1 with a stop value of 20 fills the selected column with 1 through 20, provided the selected range is large enough.
The stop value is an endpoint, not the number of entries. A sequence starting at 5 and ending at 14 with a step of 1 contains ten values. If the selected range ends early, Excel fills only the selected cells; if it extends beyond the stop value, the stop setting limits the series. The Series dialog is useful when you know the start, step, and endpoint in advance and want static values rather than formulas.
For a quick series, you can also type two starting values, select both, and drag the fill handle. After dragging, Excel may show the Auto Fill Options button near the filled range. Its choices can include Fill Series and Copy Cells. Use Fill Series if Excel copied the same number instead of continuing the pattern.
Use a formula that calculates the number from the row
If every row should show a predictable sequence regardless of the value above it, a row-based formula is another option. With the sequence starting at 5 in A2, enter =ROW()-ROW($A$2)+5 in A2 and fill it down. A2 returns 5, A3 returns 6, and A4 returns 7. The anchored reference to A2 provides a fixed starting point while ROW() changes with each formula’s location.
This method can be useful in a template where the sequence should be recreated from the row position. It is less suitable if you plan to sort only part of the range, insert or remove rows in ways that affect the intended numbering, or number only visible records. The result reflects worksheet row positions, not an independent count of filtered or nonblank records. For most ordinary lists, adding one to the previous cell or using Fill Series is easier to understand.
Microsoft 365 also supports dynamic arrays. With the first output cell A2 selected and a ten-row sequence starting at 5, enter =SEQUENCE(10,1,5,1). It spills 5 through 14 into the cells below. The arguments specify ten rows, one column, a starting value of 5, and a step of 1. SEQUENCE is available in Microsoft 365 and Excel 2021 or later. The cells in the spill area need to be empty; an existing value in the way causes a spill error. This approach is handy when you want the whole sequence controlled by one formula, but it is unnecessary for a short list.
Choose the method that fits the job
For everyday use, choose based on whether the result must remain linked to source data and whether you want formulas or fixed values.
- Formula beside source data: Best when every result should be the corresponding source value plus one. It is transparent and updates when a source number changes.
- Formula referring to the row above: Best for a sequence that should recalculate from its first value.
- Fill Series: Best for a static list with a known start, step, and endpoint.
- SEQUENCE: Best when you want a spill formula to generate a specified number of consecutive values in a supported Excel version.
Also consider whether blank source cells should stay blank. The simple formula =A2+1 treats an empty referenced cell as zero in this arithmetic context, so it can show 1 instead of a blank. If that is not the desired result, use a conditional formula. With source values in A2:A100 and results in B2:B100, enter =IF(A2="","",A2+1) in B2 and fill down. The result stays blank when A2 is blank; if A2 is 12, the result is 13. This also treats a formula returning an empty string as blank for the test.
Fix common filling and formula problems
If every row shows the same number, check whether Excel copied a constant rather than extended a series. For a number sequence, select two seed values before dragging, use the Auto Fill Options button and choose Fill Series, or use Home > Fill > Series. For a formula, check that the formula bar shows a formula and that the references shift as expected.
If a formula displays as text instead of calculating, make sure the cell does not have a leading apostrophe and that the entry begins with an equals sign. A cell formatted as Text can also prevent a newly entered formula from calculating. Change the format to General, then re-enter the formula; changing the format alone may not convert an existing text entry into a calculated formula.
If the result is one row out of place, check where the formula was first entered. For source values in A2 downward, put the first result formula in B2, not B3, if the first result should correspond to A2. For a same-column sequence that begins with a typed value in A2, put the next-row formula in A3.
If the fill handle is not visible, confirm that cell selection is active rather than editing the cell, and check Excel’s settings for the fill handle. On Windows, go to File > Options > Advanced and look under Editing options for Enable fill handle and cell drag-and-drop. You can avoid dragging by selecting the target range and using Ctrl+D to fill down on Windows. The corresponding Excel for Mac shortcut is Control+D; menu commands are also available through Home > Fill > Down.
Excel for the web and other practical details
In Excel for the web, the basic formula method works the same way: enter the formula in the first result cell, then use the fill handle or copy and paste it down. The browser version’s ribbon and available fill options can differ from desktop Excel, so if you do not see the Series command, use the formula or the two-seed-value fill method. The formula result updates when its referenced value changes, as it does in desktop Excel.
On Windows, Ctrl+D fills the selected cells with the contents and formula of the top cell. On Mac, Control+D is the commonly used Fill Down shortcut in Excel; if your keyboard or Excel settings differ, use Home > Fill > Down instead. When copying a formula such as =A2+1 down, Excel normally adjusts relative references automatically. A dollar sign makes a reference absolute: for example, $A$2 remains fixed when copied. That distinction matters in more complex formulas, but the basic row-by-row formula does not need dollar signs.
Before filling thousands of rows, test a few and inspect the formula bar for the top and bottom cells. For a source-to-result calculation, verify that the last formula points to the last source row. If you sort or filter the data later, keep related columns together so each increased value remains beside its original value.
Frequently Asked Questions
How do I add 1 to every number in an Excel column?
Put the original values in one column, enter a formula such as =A2+1 in the neighboring column’s first data row, and fill it down. This calculates each result from the matching source row without changing the originals.
How do I make Excel count up by 1 down a column?
Enter two consecutive starting numbers, such as 1 and 2, select both cells, and drag the fill handle down. Alternatively, type a starting number and enter a formula in the next row that adds one to the cell above it.
Why does Excel repeat the same number instead of increasing it?
Excel may be copying the selected value rather than detecting a series. Select two consecutive values before dragging, or use the Auto Fill Options button and choose Fill Series. If you are filling a formula, confirm that the formula references the preceding or corresponding row as intended.
Can I increase numbers by 1 without changing the original column?
Yes. Put the results in a separate column with a formula that adds one to each source cell. This keeps the source intact and lets the results update if the source numbers change. To make the results fixed, copy them and paste as values.
How do I leave blank rows blank?
Use a conditional formula that checks whether the source cell is empty before adding one. For example, with a source value in A2, =IF(A2="","",A2+1) returns a blank for an empty source cell and adds one when the source contains a number.
Related
- Excel Data Validation List Without a Cell Reference?
- Excel German Number Format: Commas, Dates, and Currency
- Excel General Number Format: How It Works and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Calculate Age in Excel from Date of BirthCalculate age in Excel with DATEDIF, handle blank or invalid dates, and format results correctly. Follow the steps…
- Car Loan Amortization Schedule in Google SheetsYes. In Google Sheets, enter the amount financed, annual interest rate, and loan term, calculate the monthly payment…
- Excel Page Breaks: Set and Adjust Print PagesTo set a page break in Excel for Microsoft 365 on Windows, select the row below or the…
- Compare Two Excel Sheets With Power QueryTo compare two Excel sheets with Power Query, turn each data range into an Excel table, import both…
- How to Delete Blank Rows in ExcelTo delete completely empty rows without removing records that have a few blank cells, add a helper column…
- How to Delete an Extra Page in WordTo delete an extra page in Word, first show formatting marks with Ctrl+Shift+8 on Windows or Command+8 on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.