Skip to main content

How to prevent number increment when using AutoFill by dragging in Excel?

Author: Kelly Last Modified: 2020-04-29

For example, the cell A2 is filled with KTE00251, and you need to fill below cells with the same content of this cell. When we drag Cell A2’s AutoFill handle down, the number automatically increase (see below screenshot). So is there any way to prevent number from incrementing when using AutoFill in Excel. Below method will help you get it done.


Prevent number increment when using AutoFill by hotkey

The number won’t increase automatically if we hold down the Ctrl key while dragging the AutoFill handle in Excel.

In our case, please select the Cell A2; hold down the Ctrl key which will change the cursor to a double-cross; and then drag the AutoFill handle down to the cells as you need.

And now below cells are filled with the content of the first cell exactly without number incrementing automatically. See below screenshot:

Easily Fill Blank Cells with Values above/below/right/left in Excel

Kutools for Excel’s Fill Blank Cells utility can help Excel users easily fill all blank cells with values above/below/right/left in a specified range. This utility also supports filling blank cells with a fixed text string or number.


ad fill blank cells 4

Prevent number increment with Fill feature

You can also apply Excel’s Fill feature to fill below blank cells with the first cell’s content without number incrementing automatically in Excel. Please do as follows:

Select the cell with content and below blank cells, and click Home > Fill > Down. See screenshot:

And now below blank cells are filled with the first cell’s content exactly without number incrementing automatically. See screenshot:


Prevent number increment with Kutools for Excel

If you have Kutools for Excel installed, you can apply its Fill Blank Cells utility to easily prevent number from incrementing in Excel. Please do as follows:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

1. Select the cell with content and below blank cells, and click Kutools > Insert > Fill Blank Cells.

2. In the opening Fill Blank Cells dialog box, please check the Based on values option in the Fill with section, check the Down option in the Options section, and click the Ok button.

And now below blank cells are filled with the first cell’s content exactly without number incrementing automatically. See screenshot:

Note: This Fill Blank Cells utility also supports to fill the cell content to the left blank cells or above blank cells. See below screenshot:

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Demo: Prevent number increment when filling with up or left cell values


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you very much!
This comment was minimized by the moderator on the site
=IF(CHIF(CHEMISTRY!D1=0,"",CHEMISTRY!D1) WHEN I DRAG DOWN =IF(CHEMISTRY!D1=0,"",CHEMISTRY!D1) I WANT TO DO THIS BUT I AM GETTING C2
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations